Generic Data Access Component in VB.NET

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.
  • 2952
Download Files:
 

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.

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(ByVal connType As Integer, ByVal 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(ByVal connType As Integer, ByVal connString As String, ByVal 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

Listing 1.

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.

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(ByVal sender As Object, ByVal 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

Listing 2.

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.

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.