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

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 to create provider-independent data access code.


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, 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, take a 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 then can 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, look at examples.

Enumerating Providers

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:


<system.data>
<DbProviderFactories>
<add name=”SqlClient Data Provider” invariant=”System.Data.SqlClient”
description=”.Net Framework Data Provider for SqlServer”
type=”System.Data.SqlClient.SqlClientFactory, System.Data,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ />
</DbProviderFactories>
</system.data>

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;
providerView.DataBind();
}
</script>
<html >
<head id=”Head1″ runat=”server”>
<title>Enumerating Providers</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:GridView HeaderStyle-BackColor=Control
HeaderStyle-ForeColor=Blue RowStyle-BackColor=Silver
runat=”server” ID=”providerView”></asp:GridView>
</div>
</form>
</body>
</html>

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.


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

Creating a Generic Query Execution Framework Using the Provider-independent Approach

In the previous example, you saw how to enumerate the providers listed in the machine.config file. This section explains the steps involved in creating a generic query execution framework using the DbProviderFactories class in conjunction with the DbProviderFactory class. In this example, you specify information such as the provider to use, server name, database name, and the table name through the input controls. You then leverage that information to execute the select query against the specified database. Key to this example is the ability to get a reference to a provider-specific factory implementation (represented by the DbProviderFactory class) using the DbProviderFactories class. This is where the GetFactory() method of the DbProviderFactories class comes into play. It has two overloads:


  • GetFactory(String) accepts a provider invariant name, such as “System.Data.SqlClient”.

  • GetFactory(DataRow) accepts a reference to a row in the DataTable returned by the GetFactoryClasses method.

For the purposes of this example, leverage the GetFactory() method using the following ASP.NET page:


<%@ 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)
{
if (!Page.IsPostBack)
{
DataTable table = DbProviderFactories.GetFactoryClasses();
ddlProvider.DataSource = table;
ddlProvider.DataTextField = “Name”;
ddlProvider.DataValueField = “InvariantName”;
ddlProvider.DataBind();
}
}

protected void btnExecute_Click(object sender, EventArgs e)
{
string SQL = “Select * from ” + txtTableName.Text;
ExecuteQuery(ddlProvider.SelectedItem.Value, sql);
}

private void ExecuteQuery(string providerName,
string connectionString, string sql)
{
DbProviderFactory factory = DbProviderFactories.GetFactory
(providerName);
string connectionString = CreateConnectionString
factory.CreateConnectionStringBuilder());
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = conn.CreateCommand();
adapter.SelectCommand.CommandText = sql;
DataTable table = new DataTable(“Table”);
adapter.Fill(table);
resultsView.DataSource = table;
resultsView.DataBind();
}
}
}

private string CreateConnectionString
(DbConnectionStringBuilder builder)
{
builder.Add(“Integrated Security”, true);
builder.Add(“Initial Catalog”, txtDatabaseName.Text);
builder.Add(“Data Source”, txtServerName.Text);
return builder.ConnectionString;
}
</script>
<html >
<head id=”Head1″ runat=”server”>
<title>Executing Dynamic Queries using Provider Independant
Approach</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
Select Provider: 
<asp:DropDownList ID=”ddlProvider” runat=”server” Width=”190px”>
</asp:DropDownList><br/><br/>
Server Name:     
<asp:TextBox ID=”txtServerName” runat=”server”
Width=”183px”></asp:TextBox><br/><br/>
Database Name: <asp:TextBox ID=”txtDatabaseName”
runat=”server” Width=”180px”></asp:TextBox><br/><br/>
Table Name:      
<asp:TextBox ID=”txtTableName” runat=”server”
Width=”176px”></asp:TextBox> <br/><br/>   
<asp:Button ID=”btnExecute” runat=”server”
OnClick=”btnExecute_Click” Text=”Execute Query” /><br/><br/>
<asp:GridView HeaderStyle-BackColor=”Control”
HeaderStyle-ForeColor=Blue RowStyle-BackColor=Silver
runat=”server” ID=”resultsView”></asp:GridView>
</div>
</form>
</body>
</html>

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read