Writing a Generic Data Access Component

OK, I've received couple of emails people asking me how can they use a common data provider to access various types of data sources without loosing the power and flexibility of native data provider libraries. One guy even asked if he can write some code which lets you specify at runtime what type of data provider do you want to use.

Introduction

ADO.NET library provides different types of data providers to work with different data sources. Three common data providers are OLE DB, SQL, and ODBC. The main reason of using different data providers to maintain the performance and not loose native data provider functionality. For example, when you access an Access database using OLE DB data provider, it uses native OLE DB provider to access the database, But when you use ODBC data provider to access an Access database, it uses ODBC layer on top of the native layer. 

In brief, all data provider classes such as a connection, command, data adapter and data reader are inherited from interfaces. I wish I could discuss this whole but It will take me days to finish the article.
Any way, the point in this article is to show you how you can write a generic class, which can access data by using OLE DB, SQL, and ODBC data providers based on the user selection at runtime.

Interface Model

Each data provider's implements some interfaces. These interfaces are defined in the System.Data namespace. For example, SqlConnection, OleDbConnection, and OdbcConnection classes are derived from IDbConnection interface. 

Similar to the connection classes, other ADO.NET components such as DataAdapter, DataReader, Command also implement their relative interfaces.

To make my story sort, you're going to use these interfaces to write generic data access class. I'm not going to write every functionality of the class but I'll give you a pretty good idea how it works and how you can extend this functionality.

The code listed in Listing 1 shows a class GenericAdoNetComp, which provides two methods GetConnection and GetDataAdapter. These both methods read information from the user and based on the connection type and other information provided by the user, these methods create and return the desired output. Here is the definition of both methods:

public IDbConnection GetConnection(int connType,string connString)
public IDbDataAdapter GetDataAdapter(int connType,string connString, string sql)

As you can see from here, instead of returning a Connection object related to a data provider, method returns IDbConnection. From Listing 1, you can see we create SqlConnection, OleDbConnection, or OdbcConnection objects depends on the connection type argument provided by the user at runtime.

Listing 1.

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.Data.Odbc;
namespace GenericDataAccessApp
{
    public class GenericAdoNetComp
    {
        private IDbConnection idbConn = null;
        private IDbDataAdapter idbAdapter = null;
        private DbDataAdapter dbAdapter = null;
        private IDataReader iReader = null;
        public GenericAdoNetComp()
        {
        }
        // GetConnection returns IDbConnection
        public IDbConnection GetConnection(int connType, string connString)
        {
            switch (connType)
            {
                case 1: // OleDb Data Provider
                    idbConn = new OleDbConnection(connString);
                    break;
                case 2: // Sql Data Provider
                    idbConn = new SqlConnection(connString);
                    break;
                case 3: // ODBC Data Provider
                    idbConn = new OdbcConnection(connString);
                    break;
                // case 3: // Add your custom data provider
                default:
                    break;
            }
            return idbConn;
        }
        // GetDataAdapter returns IDbDataAdapter
        public IDbDataAdapter GetDataAdapter(int connType, string connString, string sql)
        {
            switch (connType)
            {
                case 1: // OleDb Data Provider
                    idbAdapter = new OleDbDataAdapter(sql, connString);
                    break;
                case 2: // Sql Data Provider
                    idbAdapter = new SqlDataAdapter(sql, connString);
                    break;
                case 3: // ODBC Data Provider
                    idbAdapter = new OdbcDataAdapter(sql, connString);
                    break;
                // case 3: // Add your custom data provider
                default:
                    break;
            }
            return idbAdapter;
        }
    }
}

Consumer Application

Now let's see how to use this class in a Windows application. To test this, I create a Windows application and the interface of the form looks like Figure 1. In this application, we have three radio buttons, a button control, a group box, and a DataGrid control.

As you can pretty much guess from this form, I provide a user options to select the kind of data provider they want to use. As you can see from Figure 1, the form has three options and you can select any one of them and click the Connect button. Based on the selection, the Connect button connects to a database and fills data from the database to the DataGrid.

Figure 1.

Now in my application, I define the following variables.

private string connString, sql;
IDbConnection conn = null;
IDbDataAdapter adapter = null;

And here is the code on the Connect button event handler, which creates an instance of GenericAdoNetComp class and calls its GetConnection and GetDataAdapter methods. Once you've a DataAdapter, you can simply call Fill and Update methods to read and write data.

private void ConnectBtn_Click(object sender, System.EventArgs e)
{
    GenericAdoNetComp genDP = new GenericAdoNetComp();
    sql = "SELECT * FROM Employees";
    if (radioButton1.Checked)
    {
        connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Northwind.mdb";
        conn = genDP.GetConnection(1, connString);
        adapter = genDP.GetDataAdapter(1, connString, sql);
    }
    else if (radioButton2.Checked)
    {
        connString = "Data Source=MCB;Initial Catalog=Northwind;user id=sa;password=;";
        conn = genDP.GetConnection(2, connString);
        adapter = genDP.GetDataAdapter(2, connString, sql);
    }
    else if (radioButton3.Checked)
    {
        // Construct your connection string here
        conn = genDP.GetConnection(3, connString);
        adapter = genDP.GetDataAdapter(3, connString, sql);
    }
    try
    {
        conn.Open();
        // Fill a DataSet
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        dataGrid1.DataSource = ds.Tables[0].DefaultView;
    }
    catch (Exception exp)
    {
        MessageBox.Show(exp.Message);
    }
    finally
    {
        conn.Close();
    }
}

Conclusion

In this article, I discussed how to write a generic data access class. By putting same theory together, you can extend this class's functionality for other ADO components. Again, I tried to keep this article very easy to follow and understand. Your comments and feedback are most welcome as usual.