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.