ADO.NET tutorial - contributed by Nihal Singh

If you are working with Microsoft SQL server then you must include the following namespace:

• System.Data
• System.Data.SqlClient

Connection Objects

The main use of connection object is to provide connection to a data source. A connection object does not fetch or update data, it does not execute queries, and it does not contain the results of queries.It is a place where you can provide the connection string.

Creating Connection:

SqlConnection conObject = new SqlConnection

("Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=True");

Important Properties of Connection Object.

Command Objects

Command object is used to execute SQL statements and stored procedures against a database. Command objects contain the necessary information to execute SQL statements, stored procedures, functions, and so on.

Important Properties of Command Object.

Important Methods of Command Object.

DataReader Object

The DataReader Object provides a connection oriented data access to the Data Sources. DataReader Object is fast, forward-only, read-only retrieval of query results from the Data Sources It is not used to update the data.When we started to read from a DataReader it should always be open and positioned prior to the first record. The Read() method in the DataReader is used to read the rows from DataReader and it always moves forward to a new valid row, if any row exist .

Important Properties of DataReader Object.

Important Methods of DataReader Object.

The DataReader cannot be created directly from code, they can created only by calling the ExecuteReader method of a Command Object.

Example:

string connString = "some valid conn string";
SqlConnection connection = new SqlConnection(connString);
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT empID, empName FROM Employees";
SqlDataReader reader= cmd.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind ();
connection.Close();

Working with ADO.NET disconnected classes

The DataTable object represents tabular data as rows, columns, and constraints. You generally get a DataTable object by connecting to the database and returning table data but we can also create it by using DataTable class.

Example:

DataTable dt = new DataTable();

DataColumn dc = new DataColumn();
dc.ColumnName = "ProductID";
dc.DataType = typeof(int);
dt.Columns.Add(dc);

DataColumn dc2 = new DataColumn();
dc2.ColumnName = "ProductName";
dc2.DataType = typeof(string);
dt.Columns.Add(dc2);
dt.Rows.Add(new object[] { "1","CareerRide" });
GridView1.DataSource = dt;
GridView1.DataBind ();

Working with DataSet Object

Dataset is a disconnected, in-memory representation of data. It can contain multiple data table from different database.

Example:

string connString = "some valid conn string";
SqlConnection connection = new SqlConnection(connString);

using (SqlCommand cmd = new SqlCommand("Select * from Products", connection))
{
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
}