SIGN UP MEMBER LOGIN:    
Writing a Generic Data Access Component
Mahesh Chand
06/17/2003

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.

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.

About the Author
Mahesh Chand is a .NET consultant, author and the admin and founder of C# Corner. He has been working with .NET technology since pre beta releases.  Mahesh's background includes Master's in Computer Science and Applications and B.Sc. Mathematics. He is also a Microsoft Certified Professional.  Publication: Mahesh's publication includes the following titles -
Graphics Programming with GDI+
"This is the most comprehensive book about graphics programming using GDI+ so far. This book will be a very useful handbook for everyone who does graphics programming for Windows." --Min Liu, Software Design Engineer of GDI+, Microsoft CorporationGraphics Programming with GDI+ is the .NET developer's guide to writing graphics applications for Windows and the Web. Through the use of detailed examples it provides experienced programmers with a deep understanding of the entire GDI+ API defined in the .NET Framework class library.
 
Applied ADO.NET: Building Data-Driven Solutions
User level: Intermediate This book is written for intermediate to advanced developers who want to design database based Windows and Web solutions Applied ADO.NET: Building Data-Driven Solutions provides extensive coverage of ADO.NET technology including ADO.NET internals, namespaces, classes, and interfaces. Where most books cover only SQL and OLE DB data providers, Mahesh Chand and David Talbot cover SQL, OLE DB, ODBC data providers and the latest additions to ADO.NET: Oracle, MySQL, and XML .NET data providers. Chand and Talbot also cover internals of data binding and they provide detailed coverage on both Windows Forms and Web Forms data binding and data-bound controls. Since XML plays a major role in .NET development, the authors also provide a comprehensive look at XML namespaces and classes, and how to integrate both with ADO.NET.
The Complete Guide for Visual C# Programmers
User level: Intermediate This book covers major components that make up C# and the .NET environment. The book is geared toward the intermediate programmer, but contains enough material to satisfy the advanced developer. The book starts by introducing the reader to the .NET environment and some basic C# program examples.  The book then goes in depth on how to navigate the Visual Studio environment and introduces the reader to the C# language From here the book dives into the .NET framework and how to take advantage of its capabilities using C#.  Some of the topics covered in great detail include ADO.NET database programming,  ASP.NET and Web Services, COM interoperability programming,  security in .NET,  mobile phone programming,  GDI+ graphic programming,  multithreading, Windows Services,  XML and SOAP in .NET, and much more. Chapters in the book contain programming examples, reference tables, and step-by-step tutorials to guide you through one of Microsoft’s greatest achievements in programming environments.
A Programmer's Guide to ADO .NET in C#
User level: Beginner This book is written for beginners who want to write database applications using ADO.NET and C#.  This is the book on ADO.NET. ADO.NET is the latest database technology from Microsoft and represents the most powerful way to manipulate a database to date. A Programmer's Guide to ADO.NET in C# begins by taking readers through an overview of C# and then delves into ADO.NET. Author Mahesh Chand provides details on each of the major data providers of the .NET platform, such as OleDb, Sql, and ODBC, which enable developers to read and write data to the targeted databases. This book also serves as a good reference for finding detailed methods and properties for these data provider classes.