ADO.NET tutorial

ADO.NET tutorial - contributed by Nihal Singh

.NET Tutorial > Working with ADO.NET

Working with ADO.NET

With the release of the .NET Framework, Microsoft introduced a new data access model, called ADO.NET.The five major objects in ADO.NET are:

Obects Description
Connection The Connection object is responsible for establishing and maintaining the connection to the data source.
Command The Command object stores the query that is to be sent to the data source, and any applicable parameters.
DataReader The DataReader object provides fast, forward-only,readonly object. It is connection oriented.
DataSet            The DataSet object is the collection of objects.It contains DataTable,DataRow,DataRelation,etc.It provides a storage mechanism for disconnected data. It is as an in-memory repository to store data that has been retrieved.
DataAdapter The DataAdapter object works as a bridge between the DataSet and the data source. The DataAdapter is responsible for retrieving the data from the Command object and populating the DataSet with the data returned.It uses the Fill method to populate the DataSet.


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.

NAME Description
ConnectionString Gets or sets the string used to open the connection.
Database Read only. Gets the name of the current database after a connection is opened .
DataSource Read only. Gets the name of the database server to which it is connected.
State Read only. Gets the state of the connection.

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.

CommandText Set this to any valid SQL statement or the name of any valid stored procedure. The CommandType value determines the manner of execution.
CommandType Set to either SQL statement or StoredProcedure
Connection Set this to the connection object.
Parameters The command’s parameters collection. When running parameterized queries or stored procedures, you must add parameterobjects to this collection.
Transaction The SqlTransaction within which the SqlCommand executes.

Important Methods of Command Object.

ExecuteNonQuery This method is used,If you are using insert,update,delete SQL statement.Its return type is Integer.This indicates the no of effected records. 
ExecuteReader This method is used,If you are using Select SQL statement.Its return type is DataReader.
ExecuteScalar If you need to return a single value from a database query, you can use the ExecuteScalar method. This method always returns the value of the first column from the first row of a resultset.Its return type is Object.
ExecuteXMLReader Returns XML formatted data. Returns a System.Xml.XmlReader 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.

Connection Gets the Connection associated with the DataReader.
FieldCount Gets the number of columns in the current row.
HasRows Gets a value that indicates whether the DataReader contains one or more rows.
IsClosed Retrieves a Boolean value that indicates whether the specified DataReader instance has been closed or not.
RecordsAffected Gets the number of rows affected, inserted, or deleted by execution of the Transact-SQL statement.

Important Methods of DataReader Object.

Close Closes the DataReader object.
NextResult Advances the data reader to the next result, when reading the results of batch Transact-SQL statements.
Read Advances the DataReader to the next record.
GetValue Gets the value of the specified column

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


string connString = "some valid conn string";
SqlConnection connection = new SqlConnection(connString);
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 ();

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.


DataTable dt = new DataTable();

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

DataColumn dc2 = new DataColumn();
dc2.ColumnName = "ProductName";
dc2.DataType = typeof(string);
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.


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();
            GridView1.DataSource = ds;

Write your comment - Share Knowledge and Experience

Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring