.NET Framework Data Provider - ADO.NET (2023)

  • Article
  • 8 minutes to read

A .NET Framework data provider is used to connect to a database, run commands, and retrieve results. These results are either processed directly, placed in arecordto make them accessible to the user as needed, combined with data from multiple sources or removed between tiers. .NET Framework Data Providers are lightweight and create a minimal layer between the data source and the code, increasing performance without sacrificing functionality.

The following table lists the data providers included with the .NET Framework.

.NET Framework Data Providerdescription
.NET Framework Data Provider for SQL ServerProvides data access for Microsoft SQL Server. Uses theSystem.Data.SqlClientnamespace.
.NET Framework Data Provider for OLE DBFor data sources exposed using OLE DB. Uses theSystem.Data.OleDbnamespace.
.NET Framework Data Provider for ODBCFor data sources exposed using ODBC. Uses theSystem.Data.ODBCnamespace.
.NET Framework Data Provider for OracleFor Oracle data sources. The .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later and uses theSystem.Data.OracleClientnamespace.
EntityClient providerProvides data access for Entity Data Model (EDM) applications. Uses theSystem.Data.EntityClientnamespace.
.NET Framework Data Provider for SQL Server Compact 4.0.Provides data access for Microsoft SQL Server Compact 4.0. Uses theSystem.Data.SqlServerCenamespace.

Core objects from .NET Framework data providers

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

Objectdescription
connectionConnects to a specific data source. The base class for everyoneconnectionobjects is thedbconnectionClass.
commandRuns a command against a data source. ExposesParameterand can within the scope of atransactionof aconnection. The base class for everyonecommandobjects is theDbBefehlClass.
DataReaderReads a forward, read-only stream of data from a data source. The base class for everyoneDataReaderobjects is theDbDataReaderClass.
DataAdapterBevkert arecordand resolves updates with the data source. The base class for everyoneDataAdapterobjects is theDbDataAdapterClass.

In addition to the core classes listed in the table earlier in this document, a .NET Framework data provider also contains the classes listed in the following table.

Objectdescription
transactionEnlists commands in transactions at the data source. The base class for everyonetransactionobjects is theDBTransactionClass. ADO.NET also provides support for transactions with classes in theSystem.Transactionsnamespace.
CommandBuilderA helper object that automatically generates command properties from aDataAdapteror derives parameter information from a stored procedure and fills theParameterCollection of AcommandObject. The base class for everyoneCommandBuilderobjects is theDbCommandBuilderClass.
ConnectionStringBuilderA helper object that provides an easy way to create and manage the contents of connection strings used by theconnectionobjects. The base class for everyoneConnectionStringBuilderobjects is theDbConnectionStringBuilderClass.
ParameterDefines input, output, and return value parameters for commands and stored procedures. The base class for everyoneParameterobjects is theDbParameterClass.
exceptionReturned when the data source encounters an error. If an error occurs on the client, .NET Framework Data Providers throw a .NET Framework exception. The base class for everyoneexceptionobjects is theDbExceptionClass.
mistakeExposes the information from a warning or error returned from a data source.
ClientPermissionProvided for .NET Framework data provider code access security attributes. The base class for everyoneClientPermissionobjects is theDBDataPermissionClass.

.NET Framework Data Provider for SQL Server (SqlClient)

The .NET Framework Data Provider for SQL Server (SqlClient) uses its own protocol to communicate with SQL Server. It is lightweight and powerful as it is optimized for directly accessing a SQL Server without adding an OLE DB or Open Database Connectivity (ODBC) layer. The following figure 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 with an OLE DB data source through both the OLE DB Service component, which provides connection pooling and transaction services, and through the OLE DB Data Source Provider.

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 an ODBC service component.

.NET Framework Data Provider - ADO.NET (1)
Comparison of the .NET Framework Data Provider for SQL Server and the .NET Framework Data Provider for OLE DB

The .NET Framework Data Provider for SQL Server Classes resides in theSystem.Data.SqlClientnamespace.

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

The following code example shows how theSystem.Data.SqlClientnamespace in your applications.

Imports System.Data.SqlClient
mit System.Data.SqlClient;

.NET Framework Data Provider for OLE DB

The .NET Framework Data Provider for OLE DB (OleDb) uses native OLE DB via COM interop to provide data access. The .NET Framework Data Provider for OLE DB supports both local and distributed transactions. By default, for distributed transactions, the .NET Framework Data Provider for OLE DB automatically enlists in a transaction and retrieves transaction details from Windows Component Services. For more information, seeTransactions and Concurrency.

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

driverOfferer
SQLOLEDBMicrosoft OLE DB Provider for SQL Server
MSDAORAMicrosoft OLE DB Provider for Oracle
Microsoft.Jet.OLEDB.4.0OLE DB Provider for Microsoft Jet

note

Using an Access database (Jet) as a data source for multithreaded applications such as ASP.NET applications is not recommended. If you must use Jet as a data source for an ASP.NET application, be aware that ASP.NET applications that connect to an Access database can experience connectivity issues.

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 do not work correctly with the .NET Framework Data Provider for OLE DB. These include 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 with ADO.NET, use the .NET Framework Data Provider for ODBC.

.NET Framework Data Providers for OLE DB classes reside in theSystem.Data.OleDbnamespace. The following code example shows how theSystem.Data.OleDbnamespace in your applications.

Imports System.Data.OleDb
with System.Data.OleDb;

.NET Framework Data Provider for ODBC

The .NET Framework Data Provider for ODBC (Odbc) uses the native ODBC Driver Manager (DM) to provide data access. The ODBC Data Provider supports both local and distributed transactions. By default, with distributed transactions, the ODBC Data Provider automatically enlists in a transaction and retrieves transaction details from Windows Component Services. For more information, seeTransactions and Concurrency.

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

driver
SQL Server
Microsoft ODBC for Oracle
Microsoft Access-Treiber (*.mdb)

.NET Framework Data Providers for ODBC classes reside in theSystem.Data.ODBCnamespace.

The following code example shows how theSystem.Data.ODBCnamespace in your applications.

Imports System.Data.Odbc
with System.Data.Odbc;

note

The .NET Framework Data Provider for ODBC requires MDAC 2.6 or later, and MDAC 2.8 SP1 is recommended.

.NET Framework Data Provider for Oracle

The .NET Framework Data Provider for Oracle (OracleClient) enables data access to Oracle data sources through Oracle client connectivity software. The Data Provider supports Oracle client software version 8.1.7 or later. The data provider supports both local and distributed transactions. For more information, seeTransactions and Concurrency.

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

.NET Framework Data Providers for Oracle classes reside in theSystem.Data.OracleClientNamespace and are included in theSystem.Data.OracleClient.dllAssembly. You must refer to both of thoseSystem.Data.dlland theSystem.Data.OracleClient.dllwhen compiling an application that uses the data provider.

The following code example shows how theSystem.Data.OracleClientnamespace in your applications.

Imports System.Data Imports System.Data.OracleClient
with System.Data; with System.Data.OracleClient;

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 explains the benefits and limitations of each .NET Framework data provider.

OffererRemarks
.NET Framework Data Provider for SQL ServerRecommended for mid-tier applications using Microsoft SQL Server.

Recommended for single-tier applications using Microsoft Database Engine (MSDE) or SQL Server.

We recommend using the OLE DB Provider for SQL Server (SQLOLEDB) with the .NET Framework Data Provider for OLE DB.

.NET Framework Data Provider for OLE DBFor SQL Server, the .NET Framework Data Provider for SQL Server is recommended instead of this provider.

Recommended for single-tier applications using Microsoft Access databases. Using an Access database for a middle-tier application is not recommended.

.NET Framework Data Provider for ODBCRecommended for medium and single-tier applications using ODBC data sources.
.NET Framework Data Provider for OracleRecommended for middle and single-tier applications using Oracle data sources.

EntityClient provider

The EntityClient provider is used to access data based on an Entity Data Model (EDM). Unlike the other .NET Framework data providers, it doesn't directly interact with a data source. Instead, it uses Entity SQL to communicate with the underlying data provider. For more information, seeEntityClient provider for the Entity Framework.

See also

  • ADO.NET overview
  • Retrieving and modifying data in ADO.NET
Top Articles
Latest Posts
Article information

Author: Dong Thiel

Last Updated: 04/20/2023

Views: 5532

Rating: 4.9 / 5 (79 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Dong Thiel

Birthday: 2001-07-14

Address: 2865 Kasha Unions, West Corrinne, AK 05708-1071

Phone: +3512198379449

Job: Design Planner

Hobby: Graffiti, Foreign language learning, Gambling, Metalworking, Rowing, Sculling, Sewing

Introduction: My name is Dong Thiel, I am a brainy, happy, tasty, lively, splendid, talented, cooperative person who loves writing and wants to share my knowledge and understanding with you.