Click here to Skip to main content
13,346,708 members (64,833 online)
Click here to Skip to main content
Add your own
alternative version


106 bookmarked
Posted 4 May 2003

Implementing a Provider Independent Data Access Layer in .NET

, 3 Nov 2003
Rate this:
Please Sign up or sign in to vote.
This article tells how you can implement a DAL which is provider independant.


For applications built using Microsoft DNA, ADO provided one of the easiest ways of data access. ADO gave developers, COM based API for data access and using ADO components, a multitude of data sources could be accessed. The main reason for this was that ADO used the OLEDB provider internally to connect to data sources and more importantly there existed an OLEDB provider for ODBC. This meant that developers could use ADO to connect to ODBC sources using DSNs in their connection strings as well.

Data access in ADO.NET is not quite the same. Unlike ADO which just used OLEDB, ADO.NET provides many independent providers targeted to specific data sources. For example, the SQLClient provider deals specifically with SQL Server 7 and SQL Server 2000 as the data sources. The ODBC.NET provider deals with access to data sources using ODBC and DSNs. Likewise the OLEDB.NET provider is used to connect to OLEDB data sources. Also, there exists a separate .NET provider for Oracle databases.

Having independent providers for data access has the advantage that each provider is tuned for a specific data source and provides the most efficient access for that data source. For example, SQLClient is by far, the most efficient way of accessing SQL Server 7 or 2000 data sources. But the disadvantage here is that developers may have to decide on a particular provider and the generic way of access is not as easy as in ADO.

Now, what if the decision of selecting a provider is taken as late as application runtime? How should the data access layer (let’s call it DAL) be implemented so that applications that use the DAL be unaware of the provider being used and still work seamlessly with the DAL? This article deals with the design and implementation of the DAL which answers the above questions.

Designing the Provider independent DAL

The key to programming a provider independent DAL is to use some of the interfaces provided by the System.Data namespace and these interfaces are implemented by all providers. For example, there is an interface called IDbCommand which represents a SQL statement to be executed against a data source. Providers like SQLClient and OLEDB.NET, all implement this interface in their respective command classes. Shown in the table below are the interfaces that are typically used in a DAL. The table also gives the class of the provider which implements this interface:

InterfaceSQLClient ClassODBC.NET ClassOLEDB ClassDescription
IDbConnection SqlConnection OdbcConnection OledbConnection Represents a connection to the database
IDbDataAdapter SqlDataAdapterOdbcDataAdapter OleDbDataAdapter Represents a set of command related properties that are used to work with a DataSet
IDbCommand SqlCommand OdbcCommand OleDbCommand Represents a SQL statement (or command) to be executed against a data source
IDataParameterSqlParameter OdbcParameter OleDbParameter Represents a parameter to the command object
IDataReaderSqlDataReader OdbcDataReader OleDbDataReader Represents one or more result sets which can be accessed in read only, forward only manner
IDbTransactionSqlTransaction OdbcTransaction OleDbTransaction Represents a transaction to be performed against a data source

Note: The Oracle .NET data provider also implements these interfaces.

Now, let’s see how these interfaces can be used with a simple example:

Private Function RunCommandReturnReader(ByRef Cmd As IDbCommand, 
                    ByRef conn As IDbConnection ) As IDataReader 
    Cmd.Connection = conn 
    Return Cmd.ExecuteReader(CommandBehavior.CloseConnection) 
End Function

Shown above is a function that executes a Command and returns a DataReader object. Note that the function takes two arguments of types IDbCommand and IDbConnection respectively and returns an object of type IDataReader.

The function shown above was just a private utility function. In the later sections of this article, I shall provide the implementation of the function that is ultimately exposed by the DAL that uses this utility function to return a DataReader object.

Provider Factory

The provider factory is a class which exposes methods that return objects of the required type to the caller. The data access class uses the provider factory to obtain command, connection objects etc. The data access class is shielded from the actual type of the objects. Given below is a skeleton of the ProviderFactory class:

Imports Microsoft.Data.Odbc
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Configuration

Public Class ProviderFactory
    Public Enum EnumProviders
    End Enum

    Public Shared Function GetConnection()
        As IDbConnection

        Select Case GetProvider()
        Case EnumProviders.ODBC
            Return New OdbcConnection()
        Case EnumProviders.SQLClient
            Return New SqlConnection()
        Case EnumProviders.OLEDB
            Return New OleDbConnection()

        End Select

    End Function 

    Public Shared Function GetProvider()
        As EnumProviders 

        Dim Idt As IDictionary = _           
          CType(ConfigurationSettings.GetConfig("AppProvider"), IDictionary)
        Return CType(Idt("Provider"), EnumProviders) 

    End Function 
End Class

The ProviderFactory class shown above includes an enumeration EnumProviders, of the supported providers. In this case, three providers are supported: SQLClient, OLEDB.NET and ODBC.NET. Also exposed is a method GetConnection that returns a connection object based on what is set in the application configuration file. If the developer has set it to 0, then GetConnection returns an ODBCConnection object and if it is set to 2, then an OledbConnection object is returned. In the above example, we had implemented a method to return a connection object. In a similar fashion, the provider factory can implement methods returning Command, DataReader, DataAdapter objects etc.

Using the Provider Factory

Understand that it is the provider factory that takes care of returning the objects of the right type. So, the clients of the provider factory work only with interfaces. In the example shown below, we have a function that executes a command and returns a DataReader. Note that we had dealt with the helper function RunCommandReturnReader earlier:

Public Function RunSQLReturnDataReader(ByVal strSQL As String,ByVal 
            cmdtype As CommandType) As IDataReader 
    Dim cmd As IDbCommand = ProviderFactory.GetCommand(strSQL, cmdtype) 
    Dim conn As IDbConnection = ProviderFactory.GetConnection("") 
    Return RunCommandReturnReader(cmd, conn) 
End Function

About the source

The source zip file includes a VB.NET class library project containing simple data access routines. This helper may not suffice to all the requirements of a DAL of an enterprise application, but definitely illustrates the concept of the class factory pattern of application design.

V1.0 of the source code was very naive and didn't do anything much. The source here was compiled with v1.0 of the framework, where ODBC.NET provider was a separate download. In v1.1 of the framework, all the ODBC classes are now a part of the System.Data.ODBC namespace, which ships with the framework.

Many of the readers wanted a sample application using the DAL. Well, here it is, the V2.0 of the source has a simple application that shows some instances of how the DAL can be used. Among the many things added to the DAL are:

  • ADO.NET transaction support
  • Parameter caching (Based on the data access application block)
  • Parameter discovery (Based on the data access application block)

The last two should be of some interest to developers. In the sample source, I haven't really exploited the use of parameter caching and discovery. In real world applications, these can be of great use to the application and can boost the performance of the application while reducing the coding effort at the same time. For example, we can have a Windows service hosting a cache of parameters which are discovered and updated periodically. With this, the application developers need not write any code to build the parameter collection and just rely on the service for the same. Of course, you will require a remoting infrastructure to talk to the service, but this too would be very efficient.

A note to the readers: The method signatures in the source code may be slightly different from the ones in this article text. The concept however, remains the same.

Other resources

I would like to recommend two great resources for Data Access in .NET:


Having a provider independent DAL definitely gives an application more flexibility in terms of selecting the right provider at runtime and also prevents other parts of the application from locking down to a particular provider.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Manoj G
Web Developer
India India
I am a software developer and have worked on Microsoft technologies for about five years now. I have always been fascinated by Microsoft technologies and with the advent of .NET , this fascination has reached new heights. I take a lot of interest reading technical articles and equally enjoy writing them. I really like to be called a .NET junkie and will surely try to live up to this name Smile | :)

I am .NET MVP and have also completed MCAD, MCSD(VS 6), MCDBA (SQL Server 2000), MCSA (Win 2K) and MCTS (Distributed Apps) certfications.

You may also be interested in...


Comments and Discussions

QuestionUnable to get DS when using ExecDataSet function Pin
aswadgurjar13-Oct-09 6:51
memberaswadgurjar13-Oct-09 6:51 
GeneralGood idea. Pin
Xin Zhao22-Jul-07 15:39
memberXin Zhao22-Jul-07 15:39 
GeneralReturn Value is not Getting Pin
pesala6-Nov-06 20:50
memberpesala6-Nov-06 20:50 
GeneralRe: Return Value is not Getting Pin
psvgopal14-Jul-08 5:12
memberpsvgopal14-Jul-08 5:12 
GeneralAbstract Factory Pattern limits application to standard SQL statements Pin
Dipak V Bava30-Oct-06 20:50
memberDipak V Bava30-Oct-06 20:50 
In normal usage, Abstract Factory Pattern provides a concrete implementation of the abstract factory and then uses the generic interfaces to create the concrete objects. But it’s not totally independent from various data stores as each data stores have its own capabilities of handling and manipulating data and also variation in syntax.

D V Bava

Generalneed help finding out pattern in data acces layer. Pin
usafz26-Oct-06 7:01
memberusafz26-Oct-06 7:01 
General.NET v 2.0 source Pin
Luv White Sand28-Feb-06 19:33
memberLuv White Sand28-Feb-06 19:33 
GeneralRe: .NET v 2.0 source Pin
Pascal Ganaye16-Jul-06 11:28
memberPascal Ganaye16-Jul-06 11:28 
GeneralDoesn't work with DB2 .NET provider Pin
rnn21-Jul-05 11:14
memberrnn21-Jul-05 11:14 
AnswerRe: Doesn't work with DB2 .NET provider Pin
dodiggitydag3-Apr-06 8:48
memberdodiggitydag3-Apr-06 8:48 
NewsRe: Doesn't work with DB2 .NET provider Pin
Raja Venkatesh19-May-09 9:48
memberRaja Venkatesh19-May-09 9:48 
GeneralTestDAL.exe Pin
Ashley030922-Jun-05 5:15
memberAshley030922-Jun-05 5:15 
GeneralMicrosoft Access Pin
KevinTr19-May-05 5:25
memberKevinTr19-May-05 5:25 
GeneralCreating Dynamic ASP.NET Server Controls Using XML Pin
aspshah19-Jul-04 20:11
sussaspshah19-Jul-04 20:11 
Questioncan not get the Access conn to work Pin
jks@jkay.nl20-May-04 4:28
memberjks@jkay.nl20-May-04 4:28 
AnswerRe: can not get the Access conn to work Pin
KevinTr19-May-05 5:37
memberKevinTr19-May-05 5:37 
AnswerRe: can not get the Access conn to work Pin
holen8227-Apr-06 22:05
memberholen8227-Apr-06 22:05 
QuestionHow can i loop through all parameters Pin
vbnetuk7-Apr-04 6:35
membervbnetuk7-Apr-04 6:35 
GeneralThanks for your DAL Pin
Anonymous24-Mar-04 20:54
sussAnonymous24-Mar-04 20:54 
GeneralThanks for this DAL Pin
vbnetuk24-Mar-04 2:20
membervbnetuk24-Mar-04 2:20 
JokeRe: Thanks for this DAL Pin
dodiggitydag3-Apr-06 10:05
memberdodiggitydag3-Apr-06 10:05 
GeneralPlaying with Null decimal values Pin
lolofb22-Jan-04 7:03
memberlolofb22-Jan-04 7:03 
GeneralRe: Playing with Null decimal values Pin
breagan29-Jan-04 3:17
memberbreagan29-Jan-04 3:17 
GeneralUsing for Oracle DB Pin
meierk25-Nov-03 0:57
membermeierk25-Nov-03 0:57 
GeneralRe: Using for Oracle DB - My problems Pin
lolofb26-Nov-03 5:36
memberlolofb26-Nov-03 5:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180111.1 | Last Updated 4 Nov 2003
Article Copyright 2003 by Manoj G
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid