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:
|Interface||SQLClient Class||ODBC.NET Class||OLEDB Class||Description|
|Represents a connection to the database|
|Represents a set of command related properties that are used to work with a |
|Represents a SQL statement (or command) to be executed against a data source|
|Represents a parameter to the command object|
|Represents one or more result sets which can be accessed in read only, forward only manner|
|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
Shown above is a function that executes a
Command and returns a
DataReader object. Note that the function takes two arguments of types
IDbConnection respectively and returns an object of type
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
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
Public Class ProviderFactory
Public Enum EnumProviders
Public Shared Function GetConnection()
Select Case GetProvider()
Return New OdbcConnection()
Return New SqlConnection()
Return New OleDbConnection()
Public Shared Function GetProvider()
Dim Idt As IDictionary = _
Return CType(Idt("Provider"), EnumProviders)
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
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
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)
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.
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.