Transparent Database Access with ADO.NET

by Nick Harrison
12/16/2002

The Dream

The dream of transparent data access is to not have to care about where the data is coming from. You have more important things to worry about, like providing a nice user interface and getting the business logic right on time. Ideally, it should not matter if the data is coming from SQL Server, Oracle, Outlook, or anywhere else useful data may be stored. It should not matter if you choose to develop against Access at home, SQL Server in development, and use Oracle in production. In a perfect world, we shouldn't have to rewrite our code in order to use a more efficient data access provider, but we do have to work for this dream.

The Problem

Unfortunately, data sources are different and need to be treated differently. What works well for one data source may not necessarily work well for another, and coding to the lowest common denominator in database functionality does not allow us to take advantage of the strengths of any one platform. This causes our code to be less efficient than it needs to be.

In DotNet, we have SqlClient, OracleClient, and ODP.NET all providing database-specific, optimized classes that try to leverage the strengths of the respective databases and OleDb providing access to everything else through a database-neutral, non-optimized set of classes.

This allows us to have efficient optimized data access if we know that we will be using SQLServer or Oracle all the time, but switching data providers requires changing the data type for every Connection object, every Command object, every Data Reader object, every DataAdapter object, etc. If we are not using one of these two platforms, or not committed to using the same one in all settings, we would seem to be doomed to inefficient, non-optimized code. Fortunately, DotNet provides a solution to this problem.

The Solution

If you are not familiar with the object-oriented concept of polymorphism, this solution will endear the concept to you. Polymorphism simply refers to an object behaving differently in different circumstances. If we write our code in terms of a generic "class," and have this class behave like an SQLClient when connecting to an SQLServer Database, an OracleClient when connecting to an Oracle Database, and an OleDb class otherwise, and we won't have to get bogged down in the specifics of which provider we are using.

This generic class that we will be using is not really a class in the traditional sense of the word. We will be using the interfaces that all of the data providers implement. We will not delve much into the details of interfaces. For our purposes, think of an interface as a contract between the class and users of that class. The interface defines a set of methods and properties that any class implementing the interface will include. The interface does not implement any of these, so we cannot instantiate an instance of an interface. Instead we typecast an instance of a class that implements the interface to the interface. When we call one of the methods defined in the interface, the original class' implementation is being called without us having to keep track of the type of the original class.

The interfaces we will be concerned with here include IDbConnection, IDbCommand, IDataReader, and IDbDataAdapter. For a complete listing of the mehods and properties for these interfaces, refer to the MSDN documentation: IDbConnection, IDbCommand, IDataReader, and IDbDataAdapter.

Because all of the data providers implement these interfaces, we know that they will all include database-specific versions of the methods we will need to access our data.

The Code

This code is not intended as a guide for best coding practices. Many of the details needed in an enterprise solution are left out so that we can focus on the details of using interfaces to interact with ADO.NET.

We will start by defining a function that will read various settings from the web.config file to specify such things as the ConnectionString, the Data Type for the Connection object, and the Assembly containing this data type. To pull settings from the config file, we must include the System.Configuration namespace. We will also be using some of the features of Reflection to dynamically build the base type that is implementing the interface. To use Reflection, we must include the System.Reflection namespace.

public static IDbConnection GetConnection (string dbType)
{

The dbType parameter will be concatenated with the Configuration setting name to get the settings specific to the database type that we are using. In this example, we will be looking for SQLAssembly or OracleAssembly, etc.:

  Type connectionType = null;
  IDbConnection newConnection = null;

  // Get the assembly that owns the process  
  Assembly runningAssembly = Assembly.GetExecutingAssembly();
  
  // Get all the assemblies that are referenced  
  AssemblyName [] referencedAssemblies = 
    runningAssembly.GetReferencedAssemblies();
    
  // Find the current type of database you need  
  string strTargetAssembly =     
         ConfigurationSettings.AppSettings[dbType + "Assembly"];

We will use the currently-executing assembly to get a list of all of the assemblies referenced by the running app. We will then loop through this array of AssemblyNames to find the assembly where the config file promised the classes for our database type could be found.

// Search through all referenced assemblies
foreach (System.Reflection.AssemblyName
                   currentAssemblyName in referencedAssemblies)
{

When we find the referenced Assembly, we will load it giving us access to the non-static methods of the Assembly class. We will use this Assembly instance to get an instance of the Type object corresponding to the data type for the Connection object.

    // Open the assembly is the one we're looking for
    if (currentAssemblyName.Name == strTargetAssembly)
    {
      Assembly referencedAssembly = Assembly.
                            Load(currentAssemblyName.FullName);
      connectionType = referencedAssembly.
                        GetType(ConfigurationSettings.
                           AppSettings[dbType + "Connection"]);
      
      // Stop searching through the assemblies
      break;
    }
  }

If we were able to find the referenced assembly and get an instance of the Type for the data type referenced for the connection object, we will create an instance of this data type and pass that back as the IDbConnection object. The Activator object allows to instantiate instances of an class.

  if (connectionType != null) 
  {
    // Create a  connection of the type
    newConnection = (IDbConnection)Activator.
                         CreateInstance (connectionType);
     
    // Retrieve the Connection String    
    newConnection.ConnectionString = ConfigurationSettings.
                    AppSettings[dbType + "ConnectionString"];
  }
  
  // Return the new connection  
  return newConnection;
}

For our purposes, the relevant sections from the config file include:

 
<appSettings>

  <add key="SQLAssembly" 
       value= "System.Data"
  />
  <add key= "SQLConnection"
       value= "System.Data.SqlClient.SqlConnection"
  />
  <add key= "SQLConnectionString"
       value="User ID=sa;
              Password=;
              Database=Northwind;
              Server=localhost;" 
  />
  
  <add key="OracleAssembly" 
       value="System.Data.OracleClient" 
  />
  <add key="OracleConnection"
       value="System.Data.OracleClient.OracleConnection" 
  />
  <add key="OracleConnectionString"          
       value="User ID=scott;Password=tiger;Data Source=rddvl" 
  />
</appSettings>

We will also define a method that will use our GetConnection method to return an IDbConnection for the database type specified.

 
static IDataReader DataReader(string dbType, string query)
{
  // Get a connection Object   
  IDbConnection conn = GetConnection(dbType);
  
  // Create a Command Object  
  IDbCommand cmd = conn.CreateCommand();            
  
  // Set the Query  
  cmd.CommandText = Queryquery;
  
  // Open the connection  
  cmd.Open();
  
  // Execute the Command to create a DataReader  
  IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  
  // Return the DataReader  
  return rdr;
}

Once the interfaces are connected to instances of the appropriate objects, we can use the methods in the interface without regard for which data type they point to, and rest assured that the appropriate optimized function calls will be made.

To demonstrate using this function, we will define a Web form that includes two data grids side by side. We will display data from Oracle in the first and data from SQLServer in the second:

<body>
  <form id="TransparentADOAccess" method="post" runat="server">
    <table border="1">
      <tr>
        <td><asp:DataGrid ID="dgOracle"
            Runat="server"></asp:DataGrid></td>
        <td><asp:DataGrid ID="dgSQL"
            Runat="server"></asp:DataGrid></td>
      </tr>
    </table>
  </form>
</body>

In the Page_Load for this page, we will call the method we defined earlier twice, to populate the two datagrids with the names of the tables from both database platforms' demonstation databases.

private void Page_Load(object sender, System.EventArgs e)
{
  // Specify the Query
  string query = @"SELECT table_name 
                     FROM all_Tables 
                     WHERE owner = 'SCOTT' and rownum < 11";

  // Create the DataReader
  IDataReader oraDR = DataReader ("Oracle", query);

  // Setup DataBinding
  dgOracle.DataSource = oraDR; 
  dgOracle.DataBind(); 
  oraDR.Close(); 

  // Get another
  query = @"SELECT top 10 table_name 
              FROM INFORMATION_SCHEMA.TABLES";
  
  IDataReader sqlDR = DataReader ("SQL", query);

  // Bind again
  dgSQL.DataSource = sqlDR;
  dgSQL.DataBind();
  sqlDR.Close();

}

The output produced should look similar to this:

Another Example

Here we will use the same .aspx page defined earlier, and the same GetConnection method, but we will populate the datagrids using DataAdapters instead of DataReaders.

To use DataAdapters, we will need to define a new function, GetDataAdapter, similiar to the GetConnection function. We will also extend the web.config file to include the details about the data type for the DataAdapters.

public static IDbDataAdapter GetDataAdapter(string dbType)
{
  Type adapterType = null;
  IDbDataAdapter newAdapter = null;

  // Like before, get the main assembly
  Assembly runningAssembly = Assembly.GetExecutingAssembly();
  
  // Find the referenced assemblies
  AssemblyName [] referencedAssemblies = 
    runningAssembly.GetReferencedAssemblies();
  
  // Get the Configuration setting for the specified 
  // Assembly
  string strTargetAssembly = ConfigurationSettings.
    AppSettings[dbType + "assembly"];
  
  // Find the assembly
  foreach (System.Reflection.AssemblyName 
                currentAssemblyName in referencedAssemblies)
  {
    // If we found the right assembly...
    if (currentAssemblyName.Name == strTargetAssembly)
    {
      // Load the Assembly and the configuration
      // information
      Assembly newAssembly = Assembly.
        Load(currentAssemblyName.FullName);
      adapterType = newAssembly.
        GetType(ConfigurationSettings.
            AppSettings[dbType + "DataAdapter"]);
      
      // Stop searching if we get this far
      break;
    }
  }
  
  // If we found an adapter type, 
  if (adapterType != null)
  {
    // Create a new instance of the adapter
    newAdapter = (IDbDataAdapter)Activator.
        CreateInstance (adapterType);
  }
  
  // return it
  return newAdapter;
}

The following sections need to be added to the config file:

    <add key="SQLDataAdapter" 
         value = "System.Data.SqlClient.SqlDataAdapter"/>
    <add key="OracleDataAdapter" 
         value = "System.Data.OracleClient.OracleDataAdapter"/>

We will also define a method that will use the GetConnection and GetDataAdapter methods to return an IDbDataAdapter ready for use.

static IDbDataAdapter DataAdapter(string dbType, string query)
{
  // Get a connection for the type we want
  IDbConnection conn = GetConnection(dbType);

  // Get a new DataAdapter for the type we want
  IDbDataAdapter dataAdapter = GetDataAdapter(dbType);

  // Create a command from the connection
  dataAdapter.SelectCommand = conn.CreateCommand();

  // Specify the query
  dataAdapter.SelectCommand = query;

  // Return the new adapter
  return dataAdapter;
}

In the Page_Load, we can now use our new functions and bind the DataSets filled from the DataAdapters to the DataGrids.

private void Page_Load(object sender, System.EventArgs e)
{
  // Create the new DataAdapter
  IDbDataAdapter oraDA = DataAdapter ("Oracle", 
      "select table_name from all_Tables where rownum < 11");
  
  // Create a new DataSet
  DataSet dsOra = new DataSet();
  
  // Tell the adapter to fill it
  oraDA.Fill (dsOra);
  
  // Do Data Binding
  dgOracle.DataSource = dsOra.Tables[0];
  dgOracle.DataBind();
  
  // Get another adapter
  IDbDataAdapter sqlDA = DataAdapter ("SQL", 
      "SELECT top 10 table_name FROM INFORMATION_SCHEMA.TABLES");
  
  // Create a new DataSet
  DataSet dsSQL = new DataSet();

  // Have the DataAdapter fill it
  sqlDA.Fill (dsSQL);
  
  // Do more binding
  dgSQL.DataSource = dsSQL.Tables[0];
  dgSQL.DataBind();
}

Conclusion

Nothing is ever constant. The database used today may not be the database used in the future. The best data provider available for your database of choice today may not be the best data provider to use in the future. This article provides some guidance on how to protect your code from such changes. This simple method can also be expanded to serve as a common data access layer, helping to ensure consistent connection strings, which will help in connection pooling and management.

Nick Harrison UNIX-programmer-turned-.NET-advocate currently working in Charlotte, North Carolina using .NET to solve interesting problems in the mortgage industry.