. NET data provider

The. NET data provider is used to connect to the database, execute commands, and retrieve results. You can directly process the retrieved results or put them into ADO. net.DatasetObjects, which can be combined with data from multiple sources or remotely processed between layers and made public to users in special ways .. NET data provider is lightweight in design. It creates a minimal layer between the data source and code to improve performance without the cost of functionality.

The following table summarizes the four core objects that constitute the. NET data provider.

Object
Description

Connection
Establish a connection with a specific data source.

Command
Execute commands on the data source. PublicParametersAnd can beConnectionInTransaction.

Datareader
Read Only and read-only data streams from the data source.

Dataadapter
Fill with data sourcesDatasetAnd parse updates.

The. NET Framework includes the SQL server. NET data provider (for Microsoft SQL Server version 7.0 or later) and OLE DB. NET data provider.

Open Database Connection (ODBC). Net data provider can be downloaded separately from the http://msdn.microsoft.com/downloads. This download includes a document about the classes that constitute the ODBC. NET data provider. However, the structure of this implementation is the same as that of the SQL server. NET data provider and OLE DB. NET data provider. Therefore, the information provided in this section can also be applied to ODBC. NET data providers.

SQL Server. NET data provider

The SQL Server. NET data provider uses its own protocol to communicate with SQL Server. Because it has been optimized, it can directly access SQL server without adding OLE DB or open database connection (ODBC) layer, so it is lightweight and has good performance. Compare the SQL server. NET data provider with the OLE DB. NET data provider. The ole db. NET data provider communicates with the ole db data source through the ole db service component (which provides the connection pool and transaction service) and the ole db provider of the data source.

Comparison between SQL Server. NET data provider and OLE DB. NET data provider

To use the SQL server. NET data provider, you must have access to Microsoft SQL Server 7.0 or later. The SQL Server. NET data provider class is located inSystem. Data. sqlclientNamespace. For earlier versions of Microsoft SQL Server, use the ole db. NET data provider to the SQL Server OLE DB Provider (sqloledb ).

The following code example shows how to includeSystem. Data. sqlclientNamespace.

[Visual Basic]Imports System.Data.SqlClient

[C#]using System.Data.SqlClient;

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

Ole db. NET data provider

The ole db. NET data provider enables data access using the local ole db through COM InterOP. Ole db. Net Data Provider supports manual and automatic transactions. For automatic transactions, the ole db. NET data provider automatically registers in the transaction and obtains transaction details from the Windows 2000 component service.

To use the ole db. NET data provider, the ole db provider must support the ole db interface listed in the ole db interface used by the ole db. NET data provider.

The following table shows the providers that have been tested using ADO. net.

Driver
Providers

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

The ole db. NET data provider does not support the ole db version 2.5 interface. The ole db provider that supports the ole db 2.5 interface cannot run correctly together with the ole db. NET data provider. Such ole db providers include Microsoft ole db providers for exchange and Microsoft ole db providers for Internet publishing.

The ole db. NET data provider cannot run with the ole db Provider (msdasql) for ODBC. To access an ODBC data source using ADO. net, use the ODBC. NET data provider that can be downloaded from the http://msdn.microsoft.com/downloads.

The ole db. NET data provider class is located inSystem. Data. oledbNamespace. The following code example shows how to includeSystem. Data. oledbNamespace.

[Visual Basic]Imports System.Data.OleDb

[C#]using System.Data.OleDb;

The ole db. NET data provider requires MDAC 2.6 or later.

Select the. NET data provider

Based on the application design and data sources, the. NET data provider you select can improve the performance, functionality, and integrity of the application. The following table describes the advantages and limitations of each. NET data provider.

Providers
Note

SQL Server
. NET data provider
We recommend that you use Microsoft SQL Server 7.0 or later intermediate tier applications.

We recommend that you use Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 or later for a single-tier application.

It is recommended that you use the ole db Provider (sqloledb) for SQL Server together with the OLE DB. NET data provider ).

For Microsoft SQL Server versions 6.5 and earlier, the ole db provider for SQL Server must be used together with the OLE DB. NET data provider.

Ole db. net
Data Provider
We recommend that you use Microsoft SQL Server 6.5 or an earlier mid-tier application, or any supporting OLE DB..

For Microsoft SQL Server 7.0 or later, we recommend that you use the SQL server. NET data provider.

We recommend that you use a single-tier Application for the Microsoft Access database. We do not recommend that you use the Microsoft Access database for intermediate layer applications.

Disable support for the ole db Provider (msdasql) for ODBC. To access the Open Database Connection (ODBC) data source, you can download an ODBC. NET data provider separately from the http://msdn.microsoft.com/downloads.

Ole db.. NET data provider uses the ole db interface: The following table lists some ole db interfaces. The ole db provider must support these interfaces to work with ole db.. NET data provider. It also includes optional interfaces.

Ole db object
Interface (* indicates an optional Interface)

Ole db Service
Idatainitialize

Data Source
Idbinitialize
Idbcreatesession
Idbproperties
Ipersist
Idbinfo *

Session
Isessionproperties
Iopenrowset
Idbschemarowset *
Itransactionlocal *
Idbcreatecommand *

Command
Icommandtext
Icommandproperties
Icommandwithparameters *
Iaccessor (required only when icommandwithparameters is supported)
Icommandprepare *

Multiple results
Imultipleresults

Row set
Irowset
Iaccessor
Icolumnsinfo
Icolumnsrowset *
Irowsetinfo (required only when dbtype_hchapter is supported)

Line
Irow *

Error
Ierrorinfo
Ierrorrecords
Isqlerrorinfo *