Database Independent Development using ASP.NET 2.0

The entire solution (source code) for this article is available as a free download (in the form of a zip). All the applications in this series have been developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Standard Edition together with Microsoft SQL Server 2005 Express Edition and Oracle 10g Express editions as the database. I didn't really test any of the code in any other tools/IDEs/servers/editions/versions. If you have any problems, please feel free to post in the discussion area.

Provider independent model in ADO.NET 2.0

ADO.NET internally works with .NET data providers (or .NET data bridge providers) to connect to and access data from different kinds of data sources (including databases). The same data provider model existing in ADO.NET 1.1 is further enhanced in ADO.NET 2.0 (with a few factory classes) to leverage the flexibility of developing database independent applications.

The purpose of a factory class is to provide an interface for creating families of related objects, with or without specifying their concrete (method implementation) classes. If the factory class is created without one or more implementations of methods, we call it an abstract factory class.

The provider-independent programming model in ADO.NET 2.0 revolves around the classes in the System.Data.Common namespace. There are mainly two new factory classes that implement the provider-independent model (with the same namespace):

• DbProviderFactories
• DbProviderFactory

The DbProviderFactories class is mainly used to enumerate all .NET data providers installed on your machine. Using the same class, we can also create instances related to a specific provider (to access databases specific to that provider). These instances are nothing but the objects of the DbProviderFactory class.

Using the DbProviderFactory class, we can create Connection, Command, DataAdapter and other objects. These objects will be provider specific (like SQLConnection, OracleConnection, ODBCConnection etc.), but will be returned into a "commons" like parent classes called DBConnection, DBCommand, DBDataAdapter etc. which are available as part of System.Data.Common namespace.

The upcoming sections will show you how to develop a database independent data access layer using ADO.NET 2.0

Developing a common class which works with Factories: source code

The following is the entire source code for a class (DBFactory), which mainly works with the factory classes available in ADO.NET 2.0:

Imports System.Data
Imports System.Data.Common

Public Class DBFactory

#Region "Private members"

  Private _DBProviderName As String
  Private _ConnectionString As String 
  Private _dpf As DbProviderFactory

  Private Function GetDBProviderFactory() As DbProviderFactory
    Return _dpf
  End Function

#End Region

Public Sub New(ByVal ProviderName As String, ByVal ConnectionString As String)
  _DBProviderName = ProviderName
  _ConnectionString = ConnectionString
  If DbProviderFactories.GetFactoryClasses.Select _
    ("InvariantName='" & _DBProviderName & "'").Length = 0 Then
    Throw New Exception("Invalid .NET Data Provider specification: " & _DBProviderName)
    Exit Sub
  End If
  _dpf = DbProviderFactories.GetFactory(_DBProviderName)
End Sub

Public Function GetDBConnection() As DbConnection
  Dim dbConn As DbConnection = GetDBProviderFactory.CreateConnection
  dbConn.ConnectionString = _ConnectionString
  Return dbConn
End Function

Public Function GetDBCommand() As DbCommand
  Dim dbCmd As DbCommand = GetDBProviderFactory.CreateCommand
  dbCmd.Connection = GetDBConnection()
  Return dbCmd
End Function

Public Function GetDBDataAdapter() As DbDataAdapter
  Dim dbAdap As DbDataAdapter = GetDBProviderFactory.CreateDataAdapter
  Return dbAdap
End Function

End Class

The next section gives you a complete explanation for the above code.

Developing a common class which works with Factories: explanation

This section explains the source code provided in the previous section in a step-by-step manner. Let us start with the private members:

Private _DBProviderName As String
Private _ConnectionString As String

Those two are the variables which hold the .NET data provider to use and the connection string to connect. For example, _DBProviderName can be assigned with "System.Data.SqlClient" (as a string itself) to work with Microsoft SQL Server specific .NET data provider. Similarly, you can even assign "Oracle.DataAccess.Client" to work with ODP.NET (or Oracle's .NET data provider to access Oracle).

Going a bit further down, we have the following constructor:

Public Sub New(ByVal ProviderName As String, ByVal ConnectionString As String)
  _DBProviderName = ProviderName
  _ConnectionString = ConnectionString
  If DbProviderFactories.GetFactoryClasses.Select _
    ("InvariantName='" & _DBProviderName & "'").Length = 0 Then
    Throw New Exception("Invalid .NET Data Provider specification: " & _DBProviderName)
    Exit Sub
  End If
  _dpf = DbProviderFactories.GetFactory(_DBProviderName)
End Sub

The above constructor receives ProviderName (ex: System.Data.SqlClient as a string) and ConnectionString as parameters, and checks whether or not the provider is installed on the machine. If the provider is not installed (say ODP.NET is not installed but you are trying to use Oracle.DataAccess.Client), then it raises an exception.

If the .NET data provider is already installed, then it creates a factory object (based on the provider specification), which can be used later to create other objects like connections, commands etc. specific to that provider. Proceeding further down, we have the following:

Public Function GetDBConnection() As DbConnection
  Dim dbConn As DbConnection = GetDBProviderFactory.CreateConnection
  dbConn.ConnectionString = _ConnectionString
  Return dbConn
End Function

The above function simply creates a Connection object (based on the .NET data provider selected by the factory), assigns the connection string and returns the same Connection object to the calling method. We can use this Connection object later to create other objects like Command, DataAdapter and so forth.

Going further down, we have two more function which create and return Command/DataAdapter objects to the calling methods:

Public Function GetDBCommand() As DbCommand
  Dim dbCmd As DbCommand = GetDBProviderFactory.CreateCommand
  dbCmd.Connection = GetDBConnection()
  Return dbCmd
End Function

Public Function GetDBDataAdapter() As DbDataAdapter
  Dim dbAdap As DbDataAdapter = GetDBProviderFactory.CreateDataAdapter
  Return dbAdap
End Function

Developing a simple data access class to work with the factory: skeleton

The following is the skeleton of the DataAccess class, which works with the DBFactory class (listed in the previous section) and acts as a helper class to the business logic classes:

Imports System.Data
Imports System.Data.Common
Public Class DataAccess
  Inherits DBFactory

  Public Sub New(ByVal ProviderName As String, ByVal ConnectionString As String)
    MyBase.New(ProviderName, ConnectionString)
  End Sub

  Public Function GetDataTable(ByVal sqlSELECT As String) As DataTable
    .
    .
    .
  End Function 

  Public Sub SQLExecute(ByVal sql As String)
    .
    .
    .
  End Sub 

  Public Function GetDataRow(ByVal sqlSELECT As String) As DataRow
    .
    .
    .
  End Function 

  Public Function GetValue(ByVal sqlSELECT As String) As String
    .
    .
    .
  End Function

End Class

For the sake of clarity, I removed the implementations of methods. You can find them in the upcoming sections.

Developing a simple data access class to work with the factory: methods

This section explains the skeleton provided in the previous section. Before looking at the methods, if you can observe the declaration of the class DataAccess, it is derived (or inherited) from the DBFactory class. That means all the methods of the DBFactory class can be accessed as part of the DataAccess class.

In simple terms, we can get Connection, Command and DataAdapter objects from the base class DBFactory itself. Furthermore, you can also instantiate and work with the DBFactory class directly!

The following is the method which is used to execute any SQL statement in the database:

Public Sub SQLExecute(ByVal sql As String)
  Dim cmd As DbCommand = GetDBCommand()
  cmd.CommandText = sql
  cmd.CommandType = CommandType.Text
  Try
    cmd.Connection.Open()
    cmd.ExecuteNonQuery()
  Catch ex As Exception
    Throw New Exception(ex.Message & "-->" & sql)
  Finally
    If cmd.Connection.State = ConnectionState.Open Then
      cmd.Connection.Close()
    End If
    cmd.Dispose()
  End Try
End Sub

In the above method, I am simply receiving the Command object from the GetDBCommand method available in the DBFactory class. Once the Command object is available, the process of executing the SQL statement is exactly the same as the traditional ADO.NET 1.1 approach.

The following is the method which retrieves a set of rows in the form of a data table:

Public Function GetDataTable(ByVal sqlSELECT As String) As DataTable
  Dim dt As New DataTable
  Dim da As DbDataAdapter = GetDBDataAdapter()
  Dim cmd As DbCommand = GetDBCommand()
  cmd.CommandText = sqlSELECT
  cmd.CommandType = CommandType.Text
  da.SelectCommand = cmd
  Try
    da.Fill(dt)
  Catch ex As Exception
    Throw New Exception(ex.Message & "-->" & sqlSELECT)
  Finally
    If cmd.Connection.State = ConnectionState.Open Then
      cmd.Connection.Close()
    End If
    cmd.Dispose()
    da.Dispose()
  End Try
  Return dt
End Function

In the above function, you can observe that I used both DataAdapter and Command objects (from the methods of DBFactory class) to fill the DataTable object.

Developing a simple data access class to work with the factory: methods extended

The following is the method which retrieves a single row in the form of a data row:

Public Function GetDataRow(ByVal sqlSELECT As String) As DataRow
  Dim dt As DataTable = GetDataTable(sqlSELECT)
  If dt.Rows.Count > 0 Then
    Return dt.Rows(0)
  Else
    Return Nothing
  End If
End Function

The above function works with the previous GetDataTable function to return a set of rows. The following is the method which retrieves a single value in the form of a string from the database:

Public Function GetValue(ByVal sqlSELECT As String) As String
  Dim cmd As DbCommand = GetDBCommand()
  cmd.CommandText = sqlSELECT
  cmd.CommandType = CommandType.Text
  Try
    cmd.Connection.Open()
    Return cmd.ExecuteScalar & ""
  Catch ex As Exception
    Throw New Exception(ex.Message & "-->" & sqlSELECT)
  Finally
    If cmd.Connection.State = ConnectionState.Open Then
      cmd.Connection.Close()
    End If
    cmd.Dispose()
  End Try
End Function

You can further enhance the above classes with the support of transactions, store procedures etc. You can find an ASP.NET application for testing along with the above classes as part of the free download.

I hope you enjoyed the article and any comments, suggestions, feedback, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com