Overview of .NET Data Provider Objects

Despite the emphasis on the disconnected model of programming, you still need to connect to the physical database to actually retrieve, update, insert, and/or delete data from the database. The software that connects and communicates with the physical database in ADO.NET is called a .NET Data Provider. A data provider is the .NET managed code equivalent of an OLEDB provider or ODBC driver. A data provider consists of several objects that implement the required functionality, as defined by the classes and interfaces from which they are derived.

Currently, three different ADO.NET Data Providers are available, each defined within its own namespace. The prefixes used for objects in these namespaces are OleDb, Sql, and Odbc, respectively. When referring to these objects in a generic sense, we use the object name, without any prefix.

SqlClient

The SqlClient data provider is optimized to work with SQL Server 7.0 or higher. It achieves greater performance because (1) it communicates with the database directly through its native Tabular Data Stream (TDS) protocol, rather than through OLEDB, which needs to map the OLEDB interface to the TDS protocol; (2) the overhead of COM interoperability services are eliminated; and (3) there is no excess bloat of functionality that isn't supported by SQL Server. The objects for this provider are contained in the System.Data.SqlClient namespace.

Oledb

The Oledb data provider utilizes an existing native (COM) OLEDB provider and the .NET COM interoperability ser vices to access the database. This data provider is the one to use if you aren't accessing an SQL Server 7.0 or higher database. It allows you to access any database for which you have an OLEDB provider. The objects for this provider are contained in the System.Data.OleDb namespace.

Odbc

The Odbc data provider is the one to use when you're accessing databases that don't have their own .NET Data Provider or a (COM) OLEDB provider. Also for a given database, the ODBC driver may provide better performance than the OLEDB driver, so you may want to perform some tests to determine whether that's the case for your application. The objects for this provider are contained in the Microsoft.Data.Odbc namespace.

Note

Development of the Data Provider for ODBC lagged a bit behind the rest of the .NET Framework and Visual Studio.NET. Thus it wasn't included in the original Visual Studio.NET release, but you can download it from the Microsoft Web site. Also, be on the lookout for additional .NET data providers that will become available in the future.

Currently, an Oracle .NET Data Provider is also available from the Microsoft Web site. The downloadable ODBC and Oracle Data Providers will be incorporated into version 1.1 of the .NET Framework, which will ship together with Visual Studio.NET 2003. As a result, the namespace for the ODBC provider will change from

 Microsoft.Data.Odbc  

to

 System.Data.Odbc  

The example in this chapter features the version 1.0 ODBC provider. If you're already using version 1.1, be sure to make the change to the namespace, as just described.

Core Objects

Each data provider comprises the four core objects listed in Table 4.1.

Table 4.1. Core Data Provider Objects

Object	Brief Description
Connection	Establishes a connection to a specific data source.
Command	Executes a command at a data source. Exposes a collection of Parameter objects and methods for executing different types of commands.
DataReader	Reads and returns a forward-only, read-only stream of data from a data source.
DataAdapter	Bridges a DataSet and a data source to retrieve and save data.

Each object is derived from a generic base class and implements generic interfaces but provides its own specific implementation. For example, SqlDataAdapter, OleDbDataAdapter, and OdbcDataAdapter are all derived from the DbDataAdapter class and implement the same interfaces. Each one, however, will implement them specifically for its respective data source.

The System.Data.OleDb namespace includes the following objects:

• OleDbConnection

• OleDbCommand

• OleDbDataReader

• OleDbDataAdapter

Similarly, the System.Data.SqlClient namespace includes the following objects:

• SqlConnection

• SqlCommand

• SqlDataReader

• SqlDataAdapter

And the Microsoft.Data.Odbc namespace includes the following objects:

• OdbcConnection

• OdbcCommand

• OdbcDataReader

• OdbcDataAdapter

In the same way, all future or additional data providers will have their own namespaces and prefixes and implement the required objects appropriately.