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.