Building pluggable data access components

Introduction

In this article, I am going to explain how to build a set of data access components built on ADO.NET that can use different types of databases without many problems. When you build a database application, you will probably want to start off with a simple Microsoft Access database, but upgrade later to SQL Server or Oracle. In this case, you would have to completely rewrite your code before you can use the other types of databases. However, this isn't strictly necessary. You can always build the components so that they accept different types of databases. There are different approaches to solve this problem; I am going to explain just one way to build the data access components.

In this article, I am going to explain how to build data access components through the use of interfaces and a helper class. An interface provides us with a way to make calls to classes we don’t really know, but as long as they are built according to the interface, we can use them.

Multiple types of databases, same data

To make it possible to use different types of databases for the same application, you must first be sure that the databases deliver the same type of data to your application. After that, you can simply build the components.

The interface

The interface is, in this case, simple. I assume that there is only one table we want to link at the moment. So, we start off creating the interface like this:

public interface IDataAccess
{
 DataSet GetArticles();
 DataSet UpdateArticles(DataSet ds);
}

The interface simply implements 2 methods, one method for getting the data from the database, and another method for synchronizing the data with the database.

OLEDB connection

To connect this to OLEDB, we will need a Data Access Class for OLEDB. For easy use, we will create a new component and name that OleDbDataAccess. If you have Visual Studio .NET or SharpDevelop, you can then design the component by dragging an OleDbConnection and an OleDbDataAdapter to the designer. Configure them to use the database you made with Microsoft Access. After we designed the component itself, we go to the code and implement the interface. This could look something like this:

public class OleDbDataAccess: Component, IDataAccess
{
 public DataSet GetArticles()
 {
  DataSet ds = new DataSet();

  daArticles.Fill(ds);

  return ds;
 }

 public DataSet UpdateArticles(DataSet ds)
 {
    If(ds == null)
      return null;

    daArticles.Update(ds);
    return ds;
  }
}

The helper class

The helper class is nothing more than just a class that can spawn the right data access components for a given setting. The class implements a method GetDataAccessComponent which returns an instance of IDataAccess. The class looks like this:

public class DataAccessHelper
{
  IDataAccess GetDataAccessComponent()
  {
    string drivertype = ConfigurationSettings.AppSettings[“DriverType”];
    IDataAccess dal = null;

    switch(drivertype)
    {
      case “OleDb”:
        dal = new OleDbDataAccess();
        break;
      default:
        throw new NotSupportedException(“This drivertype is not supported”);
    }

    return dal;
  }
}

The method looks in the App.Config file of the application for an entry with the key DriverType. If the value of this entry is OleDb or any other valid driver type, a new instance of that data access component will be created and returned. If no valid driver type is found, the method will throw an exception.

Conclusion

You can add more Data Access components to your application that implement the interface IDataAccess. And change the switch statement in the GetDataAccessComponent method. I normally use OLEDB and SQL as my database types, but you can also use Oracle or ODBC if you like. As long as the data fits in a DataSet, you can use the interface.

Other implementations may be possible; this is the way I chose to use multiple databases with my applications. I am curious how other people have solved this problem, feel free to post a message with your idea :)