December 19, 2014
Hot Topics:

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

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

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 xmlns="http://www.w3.org/1999/xhtml" >
<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.

About the Author

Thiru Thangarathinam has many years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. His certifications include MCAD for .NET, MCSD, and MCP. Thiru is an expert in ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET remoting. Thiru also has authored numerous books and articles. Contact him at thiruthangarathinam@yahoo.com.





Page 3 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.

Sitemap | Contact Us

Rocket Fuel