August 1, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

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

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

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



Click here for a larger image.

Figure 3. Browser Output from Page Request





Page 2 of 3



Comment and Contribute

 


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

 

 


Sitemap | Contact Us

Rocket Fuel