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 is 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, and 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 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 an OdbcConnection object depends on the connection type argument provided by the user at runtime.

Listing 1.

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Microsoft.Data.Odbc
Namespace GenericDataAccessApp
_
Public Class GenericAdoNetComp
Private idbConn As IDbConnection = Nothing
Private idbAdapter As IDbDataAdapter = Nothing
Private dbAdapter As DbDataAdapter = Nothing
Private iReader As IDataReader = Nothing
Public Sub New()
End Sub 'New
' GetConnection returns IDbConnection
Public Function GetConnection(connType As Integer, connString As String) As IDbConnection
Select Case connType
Case 1 ' OleDb Data Provider
idbConn = New OleDbConnection(connString)
Case 2 ' Sql Data Provider
idbConn = New SqlConnection(connString)
Case 3 ' ODBC Data Provider
idbConn = New OdbcConnection(connString)
' case 3: // Add your custom data provider
Case Else
End Select
Return idbConn
End Function 'GetConnection
' GetDataAdapter returns IDbDataAdapter
Public Function GetDataAdapter(connType As Integer, connString As String, sql As String) As IDbDataAdapter
Select Case connType
Case 1 ' OleDb Data Provider
idbAdapter = New OleDbDataAdapter(sql, connString)
Case 2 ' Sql Data Provider
idbAdapter = New SqlDataAdapter(sql, connString)
Case 3 ' ODBC Data Provider
idbAdapter = New OdbcDataAdapter(sql, connString)
' case 3: // Add your custom data provider
Case Else
End Select
Return idbAdapter
End Function 'GetDataAdapter
End Class 'GenericAdoNetComp
End Namespace 'GenericDataAccessApp

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 connString, sql As String
Dim conn As IDbConnection = Nothing
Dim adapter As IDbDataAdapter = Nothing

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 Sub ConnectBtn_Click(sender As Object, e As System.EventArgs)
Dim genDP As New GenericAdoNetComp()
sql = "SELECT * FROM Employees"
If radioButton1.Checked Then
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 Then
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 Then
' Construct your connection string here
conn = genDP.GetConnection(3, connString)
adapter = genDP.GetDataAdapter(3, connString, sql)
End If
End If
End If
Try
conn.Open()
' Fill a DataSet
Dim ds As New DataSet()
adapter.Fill(ds)
dataGrid1.DataSource = ds.Tables(0).DefaultView
Catch exp As Exception
MessageBox.Show(exp.Message)
Finally
conn.Close()
End Try
End Sub 'ConnectBtn_Click

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.