Database Independent Programming Using ADO.NET

ADO.NET is a great new data access technology, but if you're not careful, you can tie your code to classes associated with a particular database. This article by Robert Oberg shows you how to write database-independent code using ADO.NET interfaces.

From the author of

From the author of

ADO.NET is a great new data access technology, but if you're not
careful, you can tie your code to classes associated with a particular database.
This article shows you how to write database-independent code using ADO.NET
interfaces.

You can download a full-length version of this article (Chapter 20 from the
book) and sample code
here.

ADO.NET Architecture

ADO.NET is a set of classes that provides consistent access to multiple data
sources, which may be either relational data from a database or hierarchical
data expressed in XML. A driving factor in ADO.NET is a provision for
disconnected access to data, which is much more scalable and flexible than the
connection-oriented database access that is traditional in client/server
systems.

The DataSet class is the central component of the disconnected
architecture. A data set can be populated from either a database or from an XML
stream. From the perspective of the user of the data set, the original source of
the data is immaterial. A consistent programming model is used for all
application interaction with the DataSet.

The second key component of ADO.NET architecture is the .NET Data Provider,
which provides access to a database, and can be used to populate a data set. A
data provider can also be used directly by an application to support a connected
mode of database access.

.NET Data Providers

A .NET data provider is used for connecting to a database. It provides
classes that can be used to execute commands and to retrieve results. The
results are either used directly by the application, or else they are placed in
a data set. A .NET data provider implements four key interfaces:

IDbConnection is used to establish a connection to a specific
data source.

IDbCommand is used to execute a command at a data
source.

IDataReader provides an efficient way to read a stream of data
from a data source. The data access provided by a data reader is forward-only
and read-only.

IDbDataAdapter is used to populate a data set from a data
source.

The ADO.NET architecture specifies these interfaces, and different
implementations can be created to facilitate working with different data
sources. A .NET data provider is analogous to an OLE DB provider, but the two
should not be confused. An OLE DB provider implements COM interfaces, and a .NET
data provider implements .NET interfaces.

When OLE DB first came out, it immediately supplied a provider for ODBC. This
single provider offered access to a wide array of data sources to any data
source with an ODBC driver. A native OLE DB provider was offered for SQL Server.
As time passed, more OLE DB providers became available.

The situation today is similar for .NET data providers. Currently, there are
two .NET data providers. The OleDb data provider goes through the COM interop
layer to talk to OLE DB. Thus, any data source with an OLE DB provider can be
accessed through ADO.NET. The SqlServer data provider uses the native SQL Server
wire protocol. As time passes, we can anticipate that additional native .NET
data providers will be offered by different database vendors.

In order to make your programs more portable, you should endeavor to program
with the interfaces rather than using specific classes directly. In our example
programs, we illustrate using interfaces to talk to an Access database (using
the OleDb data provider) and a SQL Server database (using the SqlServer data
provider).

Classes of the OleDb provider have a prefix of OleDb, and classes of
the SqlServer provider have a prefix of Sql. Table 1 shows a number of
parallel classes between the two data providers and the corresponding
interfaces.

Table 1Comparison of Parallel Classes in the OleDb and SqlServer
Data Providers

Interface

OleDb

SQL Server

IDbConnection

OleDbConnection

SqlConnection

IDbCommand

OleDbCommand

SqlCommand

IDataReader

OleDbDataReader

SqlDataReader

IDbDataAdatpter

OleDbDataAdapter

SqlDataAdapter

IDbTransaction

OleDbTransaction

SqlTransaction

IDataParameter

OleDbDataParameter

SqlDataParameter

Classes such as DataSet, which are independent of any
data provider, do not have any prefix.

.NET Namespaces

ADO.NET classes are found in the following namespaces:

System.Data consists of classes that constitute most of the
ADO.NET architecture.