Microsoft & .NET.NETBuilding Database Independent Data Access

Building Database Independent Data Access

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

From Codeguru’s .NET Nuts & Bolts column.

The last Nuts and Bolts article focused on using custom attributes and reflection to help eliminate tedious database access programming.  This month will continue to focus on database related topics and show you how to build a database access layer that is database independent.

Why Would You Need Database Independence?

Whether or not you need to worry about database independence depends upon the organization that you work for and the project work in which you are involved.  Have you ever worked on a custom application development project that where just two days before the application is put into production someone higher up in the food chain decides that your Oracle database must now be a SQL Server database?  Does the database vary from SQL Server, to Oracle, to DB2, etc from project to project?  If you are one of the lucky souls that answered “no” to the above questions then you can feel superior to us less fortunate.  For the rest of you please read on and we’ll discuss how to create a database independent data access layer that you can use across most, if not all of your projects.

What Data Providers Can We Support?

We need to look at the different databases that we need to access with our data provider.  Ideally we would like to have our object be able to support any and all of the databases that we need to access and use.  By default the .NET framework offers an optimized data provider specifically for accessing Microsoft SQL Server and another for accessing OLE DB data sources such as Oracle.  There are other data providers available for download from Microsoft such as ODBC and a Microsoft data provider for Oracle.  In addition it is possible to write custom data providers.  I will limit this article to just the SQL Server and OLE DB providers, but the concepts used apply across provider types.

What Functionality Do We Want To Expose?

The data providers in the .NET framework all provide similar functionality by design.  They allow you to connect to a data source and retrieve or modify the data.  Each data provider exposes four specific objects that are the core of the provider and specific to each provider.  The objects are listed below:

  • Connection — Establishes a connection to the data source
  • Command — Executes a command
  • DataReader — A forward-only, read-only stream of data, also known as fire hose
  • DataAdapter — Populates a DataSet

When using a .NET provider the following basic steps are used when accessing or updating data through the provider:

  • Establish a connection using the Connection object.
  • Create a Command object to execute a specific command and assign it to use the established connection.
  • Execute the desired Command or DataAdapter action, which will result in the return of a DataReader or DataSet or some alternate result.
  • Clean up the objects since they are typically valuable resources and we don’t want to wait for the garbage collector.

Since the goal of our data provider is to simplify accessing data from alternate data sources we dont want to have to deal with the Connection, Command, and DataAdapter objects directly.  We simply want to call a method with some indicator of the command to execute and have it return data in the form of a DataReader or DataSet.  Thus, the Command and DataAdapter objects expose the functionality that our data provider should make available.  The functionality is listed below:

  • ExecuteNonQuery — Executes commands such as SQL INSERT, DELETE, AND UPDATE statements and returns the number of rows affected.
  • ExecuteScalar — Retrieves a single value (for example, an aggregate value) from a database.  It retrieves the first value in the first row from the result.
  • ExecuteReader — Executes a command that returns rows.
  • ExecuteXmlReader — Executes SQL commands that return rows containing XML data (SQL Server data provider only)
  • Fill — Executes SQL commands that return rows and fill a DataSet with the results.

Creating the Database Independent Data Access Provider

Here is a class called DataProvider that performs to the design outlined above.

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace CodeGuru.DataAccess
{
  /// <remarks>
  /// Manage calls to the appropriate .NET data provider
  /// based upon the configuration.
 
/// </remarks>
 
public class DataProvider
 
{
 
  /// <summary>
 
  /// Types of databases that can be accessed through the
   
/// data provider
  
 /// </summary>

 public enum DBType
 {
   /// <value>OleDb accessible database</value>
   OleDb,
   /// <value>SQL Server database</value>
   SqlServer
 }

 // Internal DatabaseType value
 private DBType _DatabaseType = DBType.SqlServer;
 /// <value>Get or set the database type</value>
 public DBType DatabaseType
 {
   get { return this._DatabaseType; }
   set { this._DatabaseType = value; }
 }

 // Internal ConnectionString value
 private string _ConnectionString = “”;
 /// <value>Get or set the database connection string</value>

 public string ConnectionString
 {
   get { return this._ConnectionString; }
   set { this._ConnectionString = value; }
 }

 /// <summary>
 /// Constructor
 /// </summary>
 /// <param name=”ConnectString”>DB connection string</param>
 /// <param name=”Database”>Database type to access</param>
 public DataProvider(string ConnectString, DBType Database)
 {
   this.ConnectionString = ConnectString;
   this.DatabaseType = Database;
 }

 /// <summary>
 /// Executes commands such as SQL INSERT, DELETE, AND UPDATE 
 /// statements and returns the number of rows affected.
 /// </summary>
 /// <param name=”v_CmdText”>Command text</param>
 /// <returns>Number of rows affected</returns>
 public int ExecuteNonQuery(string CmdText)
 {
   IDbCommand command = null;    // Database command
   IDbConnection connection = null;  // Database connection

   try
   {
    connection = this.GetConnection();
    command = this.GetCommand(CmdText, connection);
    connection.Open();
    return command.ExecuteNonQuery();
   }
   catch( Exception exception )
   {
    System.Diagnostics.Debug.WriteLine(exception.Message);
    return 0;
   }
   finally
   {
   // Clean up the objects
   if( command != null ) command.Dispose();
   if( connection != null ) connection.Dispose();
   }
 }

 /// <summary>
 /// Executes SQL commands that return rows.
 /// </summary>
 /// <param name=”CmdText”>Command text</param>
 /// <returns>DataReader containing data</returns>
 public IDataReader ExecuteReader(string CmdText)
 {
   IDbCommand command = null;    // Database command
   IDbConnection connection = null;  // Database connection

   try
   {
     connection = this.GetConnection();
     command = this.GetCommand(CmdText, connection);
     connection.Open();

     // Use the command behavior to automatically close 
     // the connection when the reader is closed.  We need 
     // to leave the connection open until the data is
     // retrieved from the data source.
     return command.ExecuteReader( 
            System.Data.CommandBehavior.CloseConnection );
   }

   catch( Exception exception )
   {
    System.Diagnostics.Debug.WriteLine(exception.Message);
    return null;
   }
 }

 /// <summary>
 /// Retrieves a single value (for example, an aggregate
 /// value) from a database.  It retrieves the first 
 /// value in the first row from the resultset.
 /// </summary>
 /// <param name=”CmdText”>Command text</param>
 /// <returns>Single value as object</returns>
 public Object ExecuteScalar(string CmdText)
 {
   IDbCommand command = null;    // Database command
   IDbConnection connection = null;  // Database connection

   try
   {
    connection = this.GetConnection();
    command = this.GetCommand(CmdText, connection);
    connection.Open();
    return command.ExecuteScalar();
   }
   catch( Exception exception )
   {
    System.Diagnostics.Debug.WriteLine(exception.Message);
    return null;
   }
   finally
   {
    if( command != null ) command.Dispose();
    if( connection != null ) connection.Dispose();
   }
 }

 /// <summary>
 /// Executes SQL commands that return rows and fill a DataSet
 /// with the results.
 /// </summary>
 /// <param name=”CmdText”>Command text</param>
 /// <returns>DataSet containing results</returns>
 public DataSet FillDataSet(string CmdText)
 {
   DataSet dataSet = null;    // DataSet to return
   IDataAdapter adapter = null;  // Data adapter
   IDbCommand command = null;    // Database command
   IDbConnection connection = null;  // Database connection

   try
   {
     connection = this.GetConnection();
     command = this.GetCommand(CmdText, connection);
     adapter = this.GetDataAdapter(command); 

     // The data adapter will open and close the connection
     dataSet = new DataSet();
     
adapter.Fill(dataSet);
    
return dataSet;
    }
    catch( Exception exception )
    {
    System.Diagnostics.Debug.WriteLine(exception.Message);
    return null;
    }
    finally
    {
    if( command != null ) command.Dispose();
    if( connection != null ) connection.Dispose();
    }
  }

  /*
   * Get a data provider specific database connection.
   */
  private IDbConnection GetConnection()
  {
    IDbConnection connection = null;  // Database connection 

    switch( this.DatabaseType )
    {
    case DBType.SqlServer:
      connection = new SqlConnection(this.ConnectionString);
      break;
    case DBType.OleDb:
      connection = new OleDbConnection( this.ConnectionString );
      break;
    default:
      connection = new SqlConnection( this.ConnectionString );
      break;
    }
    return connection;
  }

  /*
   * Get a data provider specific database command object.
   */
  private IDbCommand GetCommand(string CmdText, IDbConnection Connection)
  {
    IDbCommand command = null;
    switch( this.DatabaseType )
    {
    case DBType.SqlServer:
      command = new SqlCommand(CmdText,
                 (SqlConnection) Connection);
      break;
    case DBType.OleDb:
      command = new OleDbCommand(CmdText,
                  (OleDbConnection) Connection);
      break;
    default:
      command = new SqlCommand(CmdText,
                       (SqlConnection) Connection);
      break;
    }
    return command;
  }

   /*
   * Get a data provider specific data adapter.
   */
  private IDataAdapter GetDataAdapter(IDbCommand command)
  {
    IDataAdapter adapter = null;
    switch( this.DatabaseType )
    {
    case DBType.SqlServer:
      adapter = new SqlDataAdapter((SqlCommand) command);
      break;
    case DBType.OleDb:
      adapter = new OleDbDataAdapter((OleDbCommand) command);
      break;
    default:
      adapter = new SqlDataAdapter((SqlCommand) command);
      break;
    }
    return adapter;
  }
}

}

Using the DataProvider

So now that we have created our DataProvider that allows us to access data independently of the database type, let’s put it to use in a couple of samples.

 // SQL Server Connection
string sqlConnect = “Server=localhost;Database=Northwind;Integrated Security=false;User Id=sa;Password=;”; 

DataProvider provider = new DataProvider(sqlConnect, DataProvider.DBType.SqlServer);
IDataReader reader = provider.ExecuteReader(“SELECT * FROM Products”);
while( reader.Read() )
{
  // Do some processing here….
}
reader.Close();

// Now use an OleDB Connection using the same provider
string oledbConnect = “Provider=SQLOLEDB;Server=localhost;Database=Northwind;User Id=sa;Password=;”;

provider.DatabaseType = DataProvider.DBType.OleDb;
provider.ConnectionString = oledbConnect;
reader = provider.ExecuteReader(“SELECT * FROM Products”);
while( reader.Read() )
{
  // Do some processing here….
}
reader.Close();

Possible Enhancements

Now we have built a data provider that will allow us to access data from multiple data sources through the same object.  There are all sorts of enhancements that could make this even more valuable. Here are some ideas to consider for yourself.

  • Expand the database provider to include support for the Odbc and Oracle providers available from Microsoft or a custom data provider of your own.  The type needs to be added to the enumerated DBType and the GetConnection, GetCommand, and GetDataAdapter methods need to be modified to include the new data provider.
  • Remove the Exception handling to require the calling class to handle the exception or expand it to include recoverability.
  • Combine the DataProvider provider object with the DataReflector object to create a database independent object that can populate data objects automatically.
  • Build connection strings dynamically from a location such as the registry or a configuration file based on the type of database being accessed.
  • Create additional methods that allow the use of transactions.
  • Create additional fields and properties to allow for configurations such as CommandTimeout to be used.
  • Have the DataProvider methods monitor the execution time and log SQL statements that exceed a certain amount of time.

Future Columns

The next column will be on the use of remoting. If you have something in particular that you would like to see explained please email me at mstrawmyer@crowechizek.com

About the Author

Mark Strawmyer, MCSD, MCSE (NT4/W2K), MCDBA is a Senior Architect of .NET applications for large and mid-size organizations. Mark is a technology leader with Crowe Chizek in Indianapolis, Indiana. He specializes in architecture, design and development of Microsoft-based solutions. You can reach Mark at mstrawmyer@crowechizek.com.

# # #

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories