2.1 Data Providers

The most commonly used .NET data providers are described in the following sections.

2.1.1 Microsoft SQL Server

The SQL Server .NET data provider ships with the .NET Framework. It uses the Tabular Data Stream (TDS) protocol to send requests to and receive responses from the SQL Server. This provider delivers very high performance because TDS is a fast protocol that can access Microsoft SQL Server directly without an OLE DB or ODBC layer and without COM interop. The SQL Server .NET data provider can be used with Microsoft SQL Server 7.0 or later. To access earlier versions of Microsoft SQL Server, the OLE DB .NET data provider with the SQL Server OLE DB provider (SQLOLEDB) should be used. The SQL Server .NET data provider classes are located in the System.Data.SqlClient namespace.

2.1.2 OLE DB

The OLE DB .NET data provider ships with the .NET Framework. It communicates with a data source using a data source-specific OLE DB provider through COM interop. The OLE DB provider, in turn, communicates directly with the data source using native OLE DB calls.

The OLE DB .NET data provider supports OLE DB interfaces later than Version 2.5. As a result, some OLE DB providers, including those for Microsoft Exchange Server and Internet Publishing, aren't supported. Also, the OLE DB .NET data provider can't be used with the OLE DB provider for ODBC (MSDASQL). To access ODBC data, use the ODBC .NET data provider discussed later in this chapter.

The OLE DB.NET data provider classes are located in the System.Data.OleDb namespace.

2.1.3 ODBC

The ODBC .NET data provider is installed as an add-in component to the .NET Framework Version 1.0 and ships with the .NET Framework Version 1.1. The provider communicates with the data source using native ODBC drivers through COM interop.

The following ODBC drivers are guaranteed compatible with the ODBC .NET data provider:

  • Microsoft SQL Server ODBC Driver

  • Microsoft ODBC Driver for Oracle

  • Microsoft Access (Jet) ODBC Driver

The ODBC .NET data provider classes are located in the Microsoft.Data.Odbc namespace in Version 1.0 of the .NET Framework. In Version 1.1, the namespace changes to System.Data.Odbc.

The Microsoft ODBC .NET data provider can be downloaded from the .NET Framework section of http://msdn.microsoft.com/downloads.

2.1.4 Oracle

The Oracle .NET data provider is installed as an add-in component to the .NET Framework Version 1.0 and ships with the .NET Framework Version 1.1. This provider accesses an Oracle database using the Oracle Call Interface (OCI). The Oracle .NET data provider can be used with Oracle 8i Release 3 (8.1.7) or later. Use the OLE DB .NET data provider with the Oracle OLE DB provider (MSDAORA) for earlier versions of Oracle. Oracle 9i is required to access UTF16 databases because UTF16 is a new feature in Oracle 9i.

The Microsoft Oracle .NET data provider classes are located in the System.Data.OracleClient namespace in both Versions 1.0 and 1.1 of the .NET Framework.

The Microsoft Oracle .NET data provider can be downloaded from the .NET Framework section of http://msdn.microsoft.com/downloads.

Additionally, Oracle has a .NET data provider available at http://otn.oracle.com/software/tech/windows/odpnet/content.html.

2.1.5 SQLXML Managed Classes

The SQLXML Managed Classes can access the functionality of SQLXML 3.0 from within .NET applications. Using SQLXML Managed Classes, XML data can be retrieved from a SQL Server, processed, and sent back to the SQL Server to apply updates. The SQLXML Managed Classes can't be considered a full .NET data provider because only partial implementations of the Command, Parameter, and DataAdapter classes are included.

The SQLXML Managed Classes are located in the Microsoft.Data.SqlXml namespace.

The SQLXML Managed Classes can be downloaded from the SQL Server Development section of http://msdn.microsoft.com/downloads.

2.1.6 Other Providers

In addition to the data providers mentioned earlier, there are many other native managed providers available or under development. Table 2-1 lists sources for more information about other native .NET data providers.

Table 2-1. Other native .NET data providers

Organization

Data source

URL

Core Lab

Oracle, MySQL

http://crlab.com

DataDirect Technologies

Oracle and Sybase

http://www.datadirect-technologies.com

dataWeb

TurboDB

http://www.turbodb.com

Enterprise Information Designs

MySQL

http://www.einfodesigns.com

MySQL

MySQL

http://www.mysql.com

PostgreSQL

PostgreSQL

http://gborg.postgresql.org

SourceForge

MySQL, Firebird

http://sourceforge.net

As mentioned, many databases and data sources that currently don't have native managed providers, including DB2, Informix, and Interbase, can be accessed using the .NET OLE DB data provider and an OLE DB provider.



    Part I: ADO.NET Tutorial
    Part II: ADO.NET Core Classes
    Part III: API Quick Reference