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:

  1. public IDbConnection GetConnection(int connType,string connString)  
  2. 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.

  1. using System;  
  2. using System.Data;  
  3. using System.Data.Common;  
  4. using System.Data.OleDb;  
  5. using System.Data.SqlClient;  
  6. using Microsoft.Data.Odbc;  
  7. namespace GenericDataAccessApp  
  8. {  
  9.     public class GenericAdoNetComp  
  10.     {  
  11.         private IDbConnection idbConn = null;  
  12.         private IDbDataAdapter idbAdapter = null;  
  13.         private DbDataAdapter dbAdapter = null;  
  14.         private IDataReader iReader = null;  
  15.         public GenericAdoNetComp()  
  16.         {  
  17.         }  
  18.         // GetConnection returns IDbConnection   
  19.         public IDbConnection GetConnection(int connType, string connString)  
  20.         {  
  21.             switch (connType)  
  22.             {  
  23.                 case 1: // OleDb Data Provider  
  24.                     idbConn = new OleDbConnection(connString);  
  25.                     break;  
  26.                 case 2: // Sql Data Provider  
  27.                     idbConn = new SqlConnection(connString);  
  28.                     break;  
  29.                 case 3: // ODBC Data Provider  
  30.                     idbConn = new OdbcConnection(connString);  
  31.                     break;  
  32.                 // case 3: // Add your custom data provider  
  33.                 default:  
  34.                     break;  
  35.             }  
  36.             return idbConn;  
  37.         }  
  38.         // GetDataAdapter returns IDbDataAdapter  
  39.         public IDbDataAdapter GetDataAdapter(int connType, string connString, string sql)  
  40.         {  
  41.             switch (connType)  
  42.             {  
  43.                 case 1: // OleDb Data Provider  
  44.                     idbAdapter = new OleDbDataAdapter(sql, connString);  
  45.                     break;  
  46.                 case 2: // Sql Data Provider  
  47.                     idbAdapter = new SqlDataAdapter(sql, connString);  
  48.                     break;  
  49.                 case 3: // ODBC Data Provider  
  50.                     idbAdapter = new OdbcDataAdapter(sql, connString);  
  51.                     break;  
  52.                 // case 3: // Add your custom data provider  
  53.                 default:  
  54.                     break;  
  55.             }  
  56.             return idbAdapter;  
  57.         }  
  58.     }  
  59. } 

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.

GenericDtAccess1.jpg

Figure 1.

Now in my application, I define the following variables.

  1. private string connString, sql;  
  2. IDbConnection conn = null;  
  3. 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.

  1. private void ConnectBtn_Click(object sender, System.EventArgs e)  
  2. {  
  3.     GenericAdoNetComp genDP = new GenericAdoNetComp();  
  4.     sql = "SELECT * FROM Employees";  
  5.     if (radioButton1.Checked)  
  6.     {  
  7.         connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Northwind.mdb";  
  8.         conn = genDP.GetConnection(1, connString);  
  9.         adapter = genDP.GetDataAdapter(1, connString, sql);  
  10.     }  
  11.     else if (radioButton2.Checked)  
  12.     {  
  13.         connString = "Data Source=MCB;Initial Catalog=Northwind;user id=sa;password=;";  
  14.         conn = genDP.GetConnection(2, connString);  
  15.         adapter = genDP.GetDataAdapter(2, connString, sql);  
  16.     }  
  17.     else if (radioButton3.Checked)  
  18.     {  
  19.         // Construct your connection string here  
  20.         conn = genDP.GetConnection(3, connString);  
  21.         adapter = genDP.GetDataAdapter(3, connString, sql);  
  22.     }  
  23.     try  
  24.     {  
  25.         conn.Open();  
  26.         // Fill a DataSet  
  27.         DataSet ds = new DataSet();  
  28.         adapter.Fill(ds);  
  29.         dataGrid1.DataSource = ds.Tables[0].DefaultView;  
  30.     }  
  31.     catch (Exception exp)  
  32.     {  
  33.         MessageBox.Show(exp.Message);  
  34.     }  
  35.     finally  
  36.     {  
  37.         conn.Close();  
  38.     }  
  39. } 

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.


Similar Articles
Mindcracker
Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.