ADO.NET is a data access technology from
Microsoft .Net Framework, which provides communication between relational and
non-relational systems through a common set of
components. ADO.NET consists of a set of Objects that expose data
access services to the .NET environment. ADO.NET is designed to be easy to use,
and Visual Studio provides several
wizards and other features that you can use to generate ADO.NET data
The two key components of ADO.NET are Data
Providers and DataSet . The .Net
Framework includes mainly three Data Providers
for ADO.NET. They are the Microsoft SQL Server Data
Provider , OLEDB Data Provider and ODBC Data
Provider . SQL Server uses the SqlConnection object , OLEDB uses the
OleDbConnection Object and ODBC uses OdbcConnection Object respectively.
The four Objects from the .Net Framework
provides the functionality of Data Providers in the ADO.NET. Theyare Connection Object, Command Object, DataReader Object
and DataAdapter Object. The Connection Object provides physical
connection to the Data Source. The Command Object uses to perform SQL statement
or stored procedure to be executed at the Data Source. The DataReader Object is
a stream-based, forward-only, read-only retrieval of query results from the
Data Source, which do not update the data. Finally, the DataAdapter Object,
which populate a Dataset Object with results of Data Source.
DataSet provides a disconnected
representation of result sets from the Data Source, and it is completely
independent from the Data Source. DataSet provides much greater flexibility
when dealing with related Result Sets.
DataSet consists of a collection
of DataTable objects that can relate to each other with DataRelation
objects. The DataTable contains a collection of DataRow and DataCoulumn Object
which contains Data. The DataAdapter Object provides a bridge between the
DataSet and the Data Source.
Connected and Disconnected Data Access Architecture
The ADO.NET Framework supports two models of
Data Access Architecture, Connection Oriented Data Access Architecture and
Disconnected Data Access Architecture.
In Connection Oriented Data Access Architecture
the application makes a connection to the Data Source and then interacts with
it through SQL requests using the same connection. In these cases the
application stays connected to the database system even when it is not using
any Database Operations.
ADO.Net solves this problem by introducing a new
component called Dataset. The DataSet is the central component in the ADO.NET
Disconnected Data Access Architecture. A DataSet is an in-memory data store
that can hold multiple tables at the same time. DataSets only hold data and do
not interact with a Data Source. One of the key characteristics of the DataSet
is that it has no knowledge of the underlying Data Source that might have been
used to populate it.
DataSet ds = new DataSet();
In Connection Oriented Data Access, when we read
data from a database by using a DataReader object, an open connection must be
maintained between our application and the Data Source. Unlike the DataReader,
the DataSet is not connected directly to a Data Source through a Connection
object when we populate it. It is the DataAdapter that manages connections
between Data Source and Dataset by fill the data from Data Source to the
Dataset and giving a disconnected behavior to the Dataset. The DataAdapter acts
as a bridge between the Connected and Disconnected Objects.
SqlDataAdapter adapter = new SqlDataAdapter("sql","connection");
DataSet ds = new DataSet();
By keeping connections open for only a minimum
period of time, ADO .NET conserves system resources and provides maximum
security for databases and also has less impact on system performance.