Database Independent Data Access

Mark Strawmyer Presents: .NET Nuts & Bolts


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 who answered "no" to the above questions, 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 because they are typically valuable resources and we don't want to wait for the garbage collector.

Because the goal of our data provider is to simplify accessing data from alternate data sources, we don't 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(font color="blue">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 e-mail 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.



About the Author

Mark Strawmyer

Mark Strawmyer is a Senior Architect of .NET applications for large and mid-size organizations. He specializes in architecture, design and development of Microsoft-based solutions. Mark was honored to be named a Microsoft MVP for application development with C# for the fifth year in a row. You can reach Mark at mark.strawmyer@crowehorwath.com.

Comments

  • at two different doses. Samples from the control plots were

    Posted by marcelpgv on 05/20/2013 09:45pm

    ayal of Clark http://www.baidu.com Sam finds the splinter destructive and gives it for safekeeping

    Reply
  • Book Universe: Ervin Dirda review articles 鈥楶hantom Lovely women,鈥?an old time noir thriller

    Posted by Victoria Iovva on 03/15/2013 04:05am

    #file_links[D:\keywords2.txt,1,S] {As this|Because this|Since this|Simply because this|Because|Much more} {suggests|indicates|implies|recommends|advises|proposes|shows|signifies|hints|has revealed|reveals}, Woolrich鈥檚 {favorite|preferred|favored|favourite|b

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: July 30, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT You may already know about some of the benefits of Bluemix, IBM's open platform for developing and deploying mobile and web applications. Check out this upcoming eSeminar that focuses on building an Android application using the MobileData service, with a walk-through of the real process and workflow used to build and link the MobileData service within your application. Join IBM's subject matter experts as they show you the way to build a base …

  • Live Event Date: August 20, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT When you look at natural user interfaces as a developer, it isn't just fun and games. There are some very serious, real-world usage models of how things can help make the world a better place – things like Intel® RealSense™ technology. Check out this upcoming eSeminar and join the panel of experts, both from inside and outside of Intel, as they discuss how natural user interfaces will likely be getting adopted in a wide variety …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds