.NET Framework Data Providers

Article
10/12/2006

A .NET Framework data provider is used for 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.

The following table outlines the four core objects that make up a .NET Framework data provider.

Object	Description
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.

Note The .NET Framework Data Provider for ODBC is not included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for ODBC and are using the .NET Framework version 1.0, you can download the .NET Framework Data Provider for ODBC at https://msdn.microsoft.com/downloads. The namespace for the downloaded .NET Framework Data Provider for ODBC is Microsoft.Data.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

Note The .NET Framework Data Provider for ODBC has a similar architecture to the .NET Framework Data Provider for OLE DB; for example, it calls into an ODBC Service Component.

Comparison of the .NET Framework Data Provider for SQL Server and the .NET Framework Data Provider for OLE DB

To use the .NET Framework Data Provider for SQL Server, you must have access to Microsoft SQL Server 7.0 or later. .NET Framework Data Provider for SQL Server classes are located in the System.Data.SqlClient namespace. For earlier versions of Microsoft SQL Server, use the .NET Framework Data Provider for OLE DB with the SQL Server OLE DB Provider (SQLOLEDB).

The .NET Framework Data Provider for SQL Server supports both local and distributed transactions. For distributed transactions, the .NET Framework Data Provider for SQL Server, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. For more information, see Performing Transactions.

The following code example shows how to include the System.Data.SqlClient namespace in your applications.

Imports System.Data.SqlClient
[C#]using System.Data.SqlClient;

The .NET Framework Data Provider for SQL Server requires the installation of Microsoft Data Access Components (MDAC) version 2.6 or later.

The .NET Framework Data Provider for OLE DB

The .NET Framework Data Provider for OLE DB uses native OLE DB through COM interop 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. For more information, see Performing Transactions.

To use the .NET Framework Data Provider for OLE DB, you must use an OLE DB provider that supports the OLE DB interfaces listed in OLE DB Interfaces Used by the .NET Framework Data Provider for OLE DB.

The following table shows the providers have been tested with ADO.NET.

Driver	Provider
SQLOLEDB	Microsoft OLE DB Provider for SQL Server
MSDAORA	Microsoft OLE DB Provider for Oracle
Microsoft.Jet.OLEDB.4.0	OLE DB Provider for Microsoft Jet

**Note **Using an Access (Jet) database as a data source for multithreaded applications, such as ASP.NET applications, is not recommended. If you must use Access as a data source for an ASP.NET application, and are unable to use an alternative such as SQL Server or MSDE, be aware that ASP.NET applications connecting to an Access database can encounter connection problems most commonly related to security permissions. For help troubleshooting connection problems using ASP.NET and an Access database, see article Q316675 "PRB: Cannot Connect to Access Database from ASP.NET" in the Microsoft Knowledge Base located at https://support.microsoft.com.

The .NET Framework Data Provider for OLE DB does not support OLE DB version 2.5 interfaces. OLE DB Providers that require support for OLE DB 2.5 interfaces will not function properly with the .NET Framework Data Provider for OLE DB. This includes the Microsoft OLE DB Provider for Exchange and the Microsoft OLE DB Provider for Internet Publishing.

The .NET Framework Data Provider for OLE DB does not work with the OLE DB Provider for ODBC (MSDASQL). To access an ODBC data source using ADO.NET, use the .NET Framework Data Provider for ODBC.

.NET Framework Data Provider for OLE DB classes are located in the System.Data.OleDb namespace. The following code example shows how to include the System.Data.OleDb namespace in your applications.

Imports System.Data.OleDb
[C#]using System.Data.OleDb;

The .NET Framework Data Provider for OLE DB requires the installation of MDAC 2.6 or later.

The .NET Framework Data Provider for ODBC

The .NET Framework Data Provider for ODBC uses native ODBC Driver Manager (DM) through COM interop 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. For more information, see Performing Transactions.

The following table shows the ODBC drivers tested with ADO.NET.

Driver
SQL Server
Microsoft ODBC for Oracle
Microsoft Access Driver (*.mdb)

.NET Framework Data Provider for ODBC classes are located in the System.Data.Odbc namespace.

Note The .NET Framework Data Provider for ODBC is not included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for ODBC and are using the .NET Framework version 1.0, you can download the .NET Framework Data Provider for ODBC at https://msdn.microsoft.com/downloads. The namespace for the downloaded .NET Framework Data Provider for ODBC is Microsoft.Data.Odbc.

The following code example shows how to include the System.Data.Odbc namespace in your applications.

Imports System.Data.Odbc
[C#]using System.Data.Odbc;

The .NET Framework Data Provider for ODBC requires the installation of MDAC 2.6 or later. MDAC 2.7 is recommended.

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). For more information, see Performing Transactions.

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.

Note The .NET Framework Data Provider for Oracle is not included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for Oracle and are using the .NET Framework version 1.0, you can download the .NET Framework Data Provider for Oracle at https://msdn.microsoft.com/downloads.

The following code example shows how to include the System.Data.OracleClient namespace in your applications.

Imports System.Data.OracleClient
[C#]using System.Data.OracleClient;

The .NET Framework Data Provider for Oracle requires the installation of MDAC 2.6 or later.

Choosing a .NET Framework Data Provider

Depending on the design and data source for your application, your choice of .NET Framework data provider can improve the performance, capability, and integrity of your application. The following table discusses the advantages and limitations of each .NET Framework data provider.

Provider	Notes
.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 that supports the OLE DB interfaces listed in OLE DB Interfaces Used by the .NET Framework Data Provider for OLE DB (OLE DB 2.5 interfaces are not required). 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. Note The .NET Framework Data Provider for ODBC is not included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for ODBC and are using the .NET Framework version 1.0, you can download the .NET Framework Data Provider for ODBC at https://msdn.microsoft.com/downloads. The namespace for the downloaded .NET Framework Data Provider for ODBC is Microsoft.Data.Odbc.
.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. Note The .NET Framework Data Provider for Oracle is not included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for Oracle and are using the .NET Framework version 1.0, you can download the .NET Framework Data Provider for Oracle at https://msdn.microsoft.com/downloads.

Share via