Implementing a Provider Independent Data Access Layer in .NET

• Download source - 25.9 Kb

Introduction

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:

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 
    Cmd.Connection.Open() 
    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
        ODBC
        SQLClient
        OLEDB
    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:

• MS Data Access Guide
• Building a Data Services Layer - Dan Fox

Conclusion

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.