ADO.NET allows you to interact with a database directly using objects of the managed provider classes. These objects allow you to connect to the database and execute SQL statements while directly connected to the database.
The Connection Classes:
1. SqlConnection.
we use an object of the SqlConnection class to connect to a SQL Server database.Firstly we connect to database by click tools on file menu of visual studio then connect to database then choose database type as sqlclient after selecting it your server name is required .if you donot know your server name then enter . (dot) it selects your local server.Choose database which you want.In this tutorial northwind database is selected.
e.g:-
connnection string = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
To find connection string right click on database in server explorer then select properties then choose connection string
sqlconnection cn as new sqlconnection(connection string);
2. OleDbConnection.
we use an object of the OleDbConnection class to connect to any database that supports OLE DB, such as Access or Oracle
3. OdbcConnection.
we use an object of the OdbcConnection class to connect to any database that supports ODBC.(opensource).
The DataSet Class:
we use an object of the DataSet class to represent a local copy of the information stored in the database. You can make changes to that local copy in your DataSet and then later synchronize those changes with the database through a managed provider DataAdapter object. A DataSet object can represent database structures such as tables, rows, and columns. You can even add constraints to your locally stored tables to enforce unique and foreign key constraints.
e.g:-
dataset ds as new dataset();
The DataAdapter Classes:
SqlDataAdapter :
we use a DataAdapter object to move rows between a DataSet object and a database.In one of
the overloaded method it takes select query and connection as parameters.
Query = "select*from products";
sqldataadapter da as new
sql dataadapt( SqlDataAdapter da = new SqlDataAdapte(query, cn);
The Command Classes:
we use a Command object to run a SQL statement, such as a SELECT, INSERT, UPDATE, or DELETE statement. we can also use a Command object to call a stored procedure or retrieve rows from a specific table. we run the command stored in a Command object using a Connection object.
e.g:-
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "select *from products";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
NOTE:
in this tutorial i use system.data.sqlclient(namespace) classes.and ms sql server 2000 or later as dbms.Also i have connected to northwind database which is installed in a sqlserver by default.
