February 18, 2019
Hot Topics:

Create Provider-Independent Data Access Code with ADO.NET 2.0

  • August 26, 2005
  • By Thiru Thangarathinam
  • Send Email »
  • More Articles »

One of the new features of ADO.NET 2.0 is the ability to create provider-independent data access code that enables you to code against a set of base abstract classes without referencing provider-specific classes. The key advantage of this approach is that it gives you the option of seamlessly working with multiple providers like SqlClient, OleDb, and ODBC without having to lock into a specific implementation. To this end, ADO.NET 2.0 provides a new namespace named System.Data.Common that exposes a number of base classes and factory classes.

This article focuses on these base classes and factory classes of this namespace and shows examples of how to leverage them to create provider-independent data access code in an ASP.NET application.

New Abstract Classes and Factory in ADO.NET 2.0

ADO.NET 2.0 introduces a myriad of new base classes in the new System.Data.Common namespace. These classes are specifically focused on enabling the creation of provider-independent data access code. They are abstract, so they can't be instantiated directly. Figure 1 shows the factory class hierarchy that is used for creating provider-independent data access code.

Click here for a larger image.

Figure 1. The Factory Class Hierarchy

As you can see, at the top of the hierarchy is a set of interfaces such as IDbConnection, IDbCommand, and IDbDataAdapter. Then there is a set of classes such as DbConnection, DbCommand, and DbDataAdapter that inherit from the base interfaces. Next is the set of implementation classes that inherit from the base abstract classes, such as SqlConnection, SqlCommand, and SqlDataAdapter. The introduction of the abstract base classes with ADO.NET 2.0 provide you with the ability to write your data access code against this layer.

If you are fairly certain that the database is not going to change, then you can directly code against the implementation classes. But this approach does not give you any benefits in terms of performance improvements. However, with the introduction of abstract classes and the flexibility they offer, there is no reason to lock yourself into the implementation of a specific provider.

Now that you have had a look at the class hierarchy, let’s look at the factory classes. In addition to base classes, the System.Data.Common namespace also contains two important factory classes: DbProviderFactories and DbProviderFactory. The DbProviderFactories class provides a method to enumerate the providers registered on your machine. In addition, the DbProviderFactories class exposes a method named GetFactory() that returns a reference to a DbProviderFactory implementation, which can then be used to create provider-specific implementations of a connection and command objects. Table 1 shows the methods of the DbProviderFactory class that return provider-specific instances of objects.

Table 1. CreateXXX() Methods of the DbProviderFactory Class

Method Name Description
CreateCommand Returns a provider-specific instance of the DbCommand class that you can use to execute SQL statements and stored procedures
CreateCommandBuilder Returns a provider-specific instance of the DbCommandBuilder class that you can use to execute SQL statements for CRUD operations
CreateConnection Returns a provider-specific instance of the DbConnection class that you can use to connect to a specific data store
CreateConnectionStringBuilder Returns an instance of the DbConnectionStringBuilder class that you can use to construct the connection string
CreateDataAdapter Returns a provider-specific instance of the DbDataAdapter object that you can use to fill or update a DataSet or a DataTable
CreateDataSourceEnumerator Returns an instance of a DbDataSourceEnumerator that you can use to examine the data sources available through the DbProviderFactory instance
CreateParameter Returns a provider-specific instance of the DbParameter object that you can use to pass parameters in and out of SQL statements and stored procedures

Now that you have a general understanding of the important classes in the System.Data.Common namespace, let’s look at examples. (Click here to download the accompanying source code for the examples.)

Enumerating Providers

Let’s look at the use of the DbProviderFactories class in enumerating the registered providers from the machine.config file. Each data provider that exposes a DbProviderFactory-based class registers configuration information in machine.config. For example, the System.Data.SqlClient provider contains the following registration information in the machine.config file:
    <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" 
      description=".Net Framework Data Provider for SqlServer" 
      type="System.Data.SqlClient.SqlClientFactory, System.Data, 
      Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

To enumerate the providers contained in the <DbProviderFactories> section, you use the GetFactoryClasses() method of the DbProviderFactories class. The following code shows an example of how to accomplish this:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Common" %>
<script runat="server">
void Page_Load(object source, EventArgs e)
    DataTable table = DbProviderFactories.GetFactoryClasses();
    providerView.DataSource = table;
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
  <title>Enumerating Providers</title>
<form id="form1" runat="server">
      <asp:GridView HeaderStyle-BackColor=Control 
        HeaderStyle-ForeColor=Blue RowStyle-BackColor=Silver 
        runat="server" ID="providerView"></asp:GridView>

The above code simply data binds the DataTable returned by the GetFactoryClasses() method of the DbProviderFactories with a GridView. The output is shown in Figure 2.

Click here for a larger image.

Figure 2. The GetFactoryClasses() Method of the DbProviderFactories Class

Page 1 of 3

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date