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:
The Connection object is responsible for establishing and maintaining the
connection to the data source.
The Command object stores the query that is to be sent to the data source, and
any applicable parameters.
The DataReader object provides fast, forward-only,readonly object. It is
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
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
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.
Gets or sets the string used to open the connection.
Read only. Gets the name of the current database after a connection is opened .
Read only. Gets the name of the database server to which it is connected.
Read only. Gets the state of the connection.
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.
Set this to any valid SQL statement or the name of any valid stored procedure.
The CommandType value determines the manner of execution.
Set to either SQL statement or StoredProcedure
Set this to the connection object.
The command’s parameters collection. When running parameterized queries or
stored procedures, you must add parameterobjects to this collection.
The SqlTransaction within which the SqlCommand executes.
Important Methods of Command Object.
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.
This method is used,If you are using Select SQL statement.Its return type is
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.
Returns XML formatted data. Returns a System.Xml.XmlReader 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.
Gets the Connection associated with the DataReader.
Gets the number of columns in the current row.
Gets a value that indicates whether the DataReader contains one or more rows.
Retrieves a Boolean value that indicates whether the specified DataReader
instance has been closed or not.
Gets the number of rows affected, inserted, or deleted by execution of the
Important Methods of DataReader Object.
Closes the DataReader object.
Advances the data reader to the next result, when reading the results of batch
Advances the DataReader to the next record.
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.
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);
using (SqlCommand cmd = new SqlCommand("Select * from Products", connection))
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds =
GridView1.DataSource = ds;