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.
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:<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>
Let’s walk through the code. It starts by populating the dropdownlist ddlProvider with the list of provider names from the machine.config file. Then you also declare three text box controls to allow the users to enter server name, database name, and table name respectively.
In the Page_Load event, you data bind the output DataTable with the dropdownlist. The DataTable is returned by the GetFactoryClasses() method of the DbProviderFactories object:
DataTable table = DbProviderFactories.GetFactoryClasses();
ddlProvider.DataSource = table;
Here, you set the DataTextField and DataValueField properties of the dropdownlist to appropriate columns in the DataTable object:
ddlProvider.DataTextField = “Name”;
ddlProvider.DataValueField = “InvariantName”;
ddlProvider.DataBind();
In addition to the text box controls, you also declare a button control named btnExecute. In the Click event of the button, you invoke a helper method named ExecuteQuery(), passing in the provider name and dynamic SQL statement as arguments. Note that when you use dynamic SQL, you need to check the SQL statement for malicious SQL injection attacks. This means you need to validate the incoming data that is used to construct the dynamic query:
string SQL = “Select * from ” + txtTableName.Text;
ExecuteQuery(ddlProvider.SelectedItem.Value, sql);
The ExecuteQuery() method starts by invoking the GetFactory() method of the DbProviderFactories class to retrieve the appropriate DbProviderFactory class based on the provider name:
DbProviderFactory factory = DbProviderFactories.GetFactory
(providerName);
Once you have the DbProviderFactory object, you can then use that to create provider-specific instances of the DbConnectionStringBuilder, DbConnection, and DbDataAdapter objects, using the CreateConnectionStringBuilder(), CreateConnection() and CreateDataAdapter() methods respectively.
To start with, you invoke the CreateConnectionStringBuilder() method of the DbProviderFactory class. You then pass in the DbConnectionStringBuilder object to the CreateConnectionString() helper function:
string connectionString = CreateConnectionString
(factory.CreateConnectionStringBuilder());
Inside the CreateConnectionString() method, you add the connection string attributes to the DbConnectionStringBuilder object using the Add() method. Finally, you return the complete connection string back to the caller:
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;
}
Once you have the connection string from the CreateConnectionString() method, you then create the DbConnection object using the CreateConnection() method of DbProviderFactory class:
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
Once you have the DbConnection and DbDataAdapter objects, executing the actual query is very simple. You simply set the CommandText property of the DbCommand object (returned by the SelectCommand property of the DbDataAdapter object) to the dynamically create SQL statement:
adapter.SelectCommand = conn.CreateCommand();
adapter.SelectCommand.CommandText = sql;
DataTable table = new DataTable(“Table”);
Here, you invoke the Fill() method of the DbDataAdapter object, passing in the DataTable as an argument:
adapter.Fill(table);
Finally, you data bind the DataTable object with the GridView named resultsView:
resultsView.DataSource = table;
resultsView.DataBind();
}
}
If you request the page from a browser and enter all the details, you will get an output that is somewhat similar to Figure 3.
Figure 3. Browser Output from Page Request
Utilizing web.config Settings to Write Provider-Independent Code
In the previous example, you created the connection string dynamically using the DbConnectionStringBuilder class. In this example, you will utilize the web.config file to store the connection string using the new <connectionStrings> element. In addition to the connection string, you can also store the provider invariant name as part of the connection string setting using the providerName attribute. You can then retrieve this providerName value from the code and use that as a foundation for creating database-independent code. For example, in the following connection string, you store the connectionString and the providerName entries in the web.config file:<connectionStrings>
<add name=”adventureWorks” connectionString=”server=localhost;integrated
security=true;database=AdventureWorks;”
providerName=”System.Data.SqlClient”/>
</connectionStrings>
The following code illustrates the use of the above providerName and connectionString attribute values to create provider-independent code:
<%@ 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)
{
string providerName = WebConfigurationManager.ConnectionStrings
[“adventureWorks”].ProviderName;
DbProviderFactory factory=DbProviderFactories.GetFactory(providerName);
using (DbConnection conn = factory.CreateConnection())
{
string connString = WebConfigurationManager.ConnectionStrings
[“adventureWorks”].ConnectionString;
conn.ConnectionString = connString;
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = conn.CreateCommand();
adapter.SelectCommand.CommandText =
“SELECT ProductSubcategoryID as SubCategoryID, ” +
“ProductCategoryID as CategoryID, Name, ModifiedDate “+
“FROM Production.ProductSubCategory”;
DataTable table = new DataTable(“ProductSubCategory”);
adapter.Fill(table);
categoryView.DataSource = table;
categoryView.DataBind();
}
}
}
</script>
<html >
<head runat=”server”>
<title>Using providerName setting to create provider
independent data access code
</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:GridView runat=”server” ID=”categoryView”></asp:GridView>
</div>
</form>
</body>
</html>
In the above code, you retrieve the value of the providerName attribute by using the following line of code:
string providerName = WebConfigurationManager. ConnectionStrings[“adventureWorks”].ProviderName;
Then you use the provider name as an argument to the GetFactory() method of the DbProviderFactories class:
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
The rest of the code is very similar to the previous example in that it also executes a query against the database using the appropriate provider name and the connection string.
Important Considerations
As you can see, the new factory classes make it easier to write data access code independent of the database. Although you can use this approach to write almost completely generic code, it is important to understand that databases aren’t completely generic. You can never write a data access logic and have it work seamlessly across different databases. Because of the inherent differences in the databases, each data provider may support some properties, methods, and events that are not supported by other data providers. For example, the way you pass in parameters to parameterized SQL statements or stored procedures completely varies depending on the database to which you are connecting. The SqlClient and the Microsoft implementation of OracleClient provider insist on named parameters; OleDb and Odbc use positional parameters.Another difference is how the parameter markers (symbols like ? or @, 🙂 are interpreted by different providers. Each provider has its own interpretation of the parameter markers. You can overcome some of these limitations by resorting to dynamic SQL statements. Although use of dynamic SQL statements opens up room for SQL injection attacks, the dynamic SQL approach works in most of the cases as long as you thoroughly validate the SQL statement.
A Very Handy Feature
Provider-independent data access code is a very useful feature in ADO.NET 2.0. As shown in this article, the new base classes and factory classes enable you to write data access code that works with almost all of the data stores with only slight adjustments. This feature can be very handy if you want your application to be portable and need to be able to run against different databases without a lot of code changes.
Download the Code
To download the accompanying source code for the examples, click here.
