ADO.NET 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 consist 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 access code.
Data Providers and DataSet

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.
C# SQL Server Connection
C# OLEDB Connection
C# ODBC Connection
The four Objects from the .Net Framework provides the functionality of Data Providers in the ADO.NET. They are 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 from a Data Source .
C# Connection
C# Command
C# DataReader
C# DataAdapter

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 you 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. From the following section you can see each of the ADO.NET components in details with C# Source Code .

ADO.Net is the data access model for .Net ?based 
applications. It can be used to access relational database 
systems such as SQL SERVER 2000, Oracle, and many other 
data sources for which there is an OLD DB or ODBC provider.

To a certain extent, ADO.NET represents the latest 
evolution of ADO technology. However, ADO.NET introduces 
some major changes and innovations that are aimed at the 
loosely coupled and inherently disconnected ? nature of web 
applications.


A .Net Framework data provider is used to connecting to a 
database, executing commands, and retrieving results. Those 
results are either processed directly, or placed in an 
ADO.NET DataSet in order to be exposed to the user in an ad-
hoc manner, combined with data from multiple sources, or 
remoted between tiers. The .NET Framework data provider is 
designed to be lightweight, creating a minimal layer 
between the data source and your code, increasing 
performance without sacrificing functionality.


Following are the 4 core objects of .Net Framework Data 
provider:

Connection: Establishes a connection to a specific data 
source .

Command:  Executes a command against a data source. Exposes 
Parameters and can execute within the scope of a 
Transaction from a Connection. 

DataReader: Reads a forward-only, read-only stream of data 
from a data source. 

DataAdapter: Populates a DataSet and resolves updates with 
the data source.

The .NET Framework includes the .NET Framework Data 
Provider for SQL Server (for Microsoft SQL Server version 
7.0 or later), the .NET Framework Data Provider for OLE DB, 
and the .NET Framework Data Provider for ODBC.

The .NET Framework Data Provider for SQL Server:  The .NET 
Framework Data Provider for SQL Server uses its own 
protocol to communicate with SQL Server. It is lightweight 
and performs well because it is optimized to access a SQL 
Server directly without adding an OLE DB or Open Database 
Connectivity (ODBC) layer. 

The following illustration contrasts the .NET Framework 
Data Provider for SQL Server with the .NET Framework Data 
Provider for OLE DB. The .NET Framework Data Provider for 
OLE DB communicates to an OLE DB data source through both 
the OLE DB Service component, which provides connection 
pooling and transaction services, and the OLE DB Provider 
for the data source .


The .NET Framework Data Provider for OLE DB: The .NET 
Framework Data Provider for OLE DB uses native OLE DB 
through COM interoperability to enable data access. 
The .NET Framework Data Provider for OLE DB supports both 
local and distributed transactions. 

For distributed transactions, the .NET Framework Data 
Provider for OLE DB, by default, automatically enlists in a 
transaction and obtains transaction details from Windows 
2000 Component Services.


The .NET Framework Data Provider for ODBC: The .NET 
Framework Data Provider for ODBC uses native ODBC Driver 
Manager (DM) through COM interoperability to enable data 
access. The ODBC data provider supports both local and 
distributed transactions. For distributed transactions, the 
ODBC data provider, by default, automatically enlists in a 
transaction and obtains transaction details from Windows 
2000 Component Services.

The .NET Framework Data Provider for Oracle: The .NET 
Framework Data Provider for Oracle enables data access to 
Oracle data sources through Oracle client connectivity 
software. The data provider supports Oracle client software 
version 8.1.7 and later. The data provider supports both 
local and distributed transactions (the data provider 
automatically enlists in existing distributed transactions, 
but does not currently support the 
EnlistDistributedTransaction method).


The .NET Framework Data Provider for Oracle requires that 
Oracle client software (version 8.1.7 or later) be 
installed on the system before you can use it to connect to 
an Oracle data source.


.NET Framework Data Provider for Oracle classes are located 
in the System.Data.OracleClient namespace and are contained 
in the System.Data.OracleClient.dll assembly. You will need 
to reference both the System.Data.dll and the 
System.Data.OracleClient.dll when compiling an application 
that uses the data provider.

Choosing a .NET Framework Data Provider
.NET Framework Data Provider for SQL Server: Recommended 
for middle-tier applications using Microsoft SQL Server 7.0 
or later. Recommended for single-tier applications using 
Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 or 
later.


Recommended over use of the OLE DB Provider for SQL Server 
(SQLOLEDB) with the .NET Framework Data Provider for OLE 
DB. For Microsoft SQL Server version 6.5 and earlier, you 
must use the OLE DB Provider for SQL Server with the .NET 
Framework Data Provider for OLE DB.


.NET Framework Data Provider for OLE DB: Recommended for 
middle-tier applications using Microsoft SQL Server 6.5 or 
earlier, or any OLE DB provider. For Microsoft SQL Server 
7.0 or later, the .NET Framework Data Provider for SQL 
Server is recommended. Recommended for single-tier 
applications using Microsoft Access databases. Use of a 
Microsoft Access database for a middle-tier application is 
not recommended.


.NET Framework Data Provider for ODBC: Recommended for 
middle-tier applications using ODBC data sources. 
Recommended for single-tier applications using ODBC data 
sources.


.NET Framework Data Provider for Oracle: Recommended for 
middle-tier applications using Oracle data sources. 
Recommended for single-tier applications using Oracle data 
sources. Supports Oracle client software version 8.1.7 and 
later. The .NET Framework Data Provider for Oracle classes 
are located in the System.Data.OracleClient namespace and 
are contained in the System.Data.OracleClient.dll assembly. 
You need to reference both the System.Data.dll and the 
System.Data.OracleClient.dll when compiling an application 
that uses the data provider.

.....................................................................................

ADO.NET provides 2 types of architecture. 
1. Connection Oriented 
2. Connectionless

Connection Oriented architecture is achieved by the use of 
Connection, Command and DataReader. Connection management 
should done by the user. This architecture is used for 
viewing data for continuous monitoring of data.

Connectionless architecture is achieved by the use of 
Connection, Command and DataAdapter. The data is retrieved 
from the database and stored in the dataset(local 
representation of a part or full of actual database). The 
connection management is handled by the DataAdapter. The 
connection is created when the query or DML operation is 
run against the database and cut immediately after the 
operation is over. This architecture is mainly used for 
doing Modifications in the data.