Writing Less & Generic Data Access Code in ADO.NET 2.0

Most applications use data access code to access the underlying data store to perform basic data queries such as Select, Insert, Update and Delete.

This is a quick and lightweight data access class that you can extend later.. This class will encapsulate data access code for SQL Server and MS Access Databases and Supports paging using the methods discussed in [Paging in ASP and ASP.Net]

We will use the System.data.Common Namespace and The most important one is the DbProviderFactory Class. This abstract factory class accepts a provider name and in return provides us with objects of all necessary ADO.NET classes.

We start by creating a new Command Class. that will use DbCommand and implicitly use DbConnection. If you've used ADO before you are probably not happy with those many objects you have to deal with in ADO.Net just to query some records.. So this way you would write less & generic code.

First,your ConnectionString should be saved in web.config(for web Apps..), as follows:
<connectionStrings>
<add name="StrConn" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\data.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/>
<add name="StrConn2" connectionString="Data Source=127.0.0.1\sqlexpress,1433;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn" providerName="System.Data.SqlClient"/>
</connectionStrings>

Which contains 2 connection strings; one for Access that is saved in "App_Data" folder and second one for connecting to SQL Server 2005 Express(on local machine & default port) using the Network Library , and as you can see each one defines its ProviderName which is needed to tell DbProviderFactory what Data access object it should create..

Imports System.Data
Imports System.Data.Common
Imports System.Configuration

Public Class GenericCommand
   Private ConnStringID As String = ""
   Private ConnString As String = ""
   Private Provider As String = ""
   Private PFactory As DbProviderFactory
   Private Conn As DbConnection
   Private Cmd As DbCommand
   ''' <summary>Count SQL Query used with ExecuteDataSet or ExecuteDateReader to calculate PageCount</summary>
   Public CountCommandText As String = ""
   Private mPageCount As Integer = 0
   Public PageSize As Integer = 0
   ''' <summary>1-based Page Index</summary>
   Public PageIndex As Integer = 0

   Public Sub New(ByVal Config_ConnStringID As String, Optional ByVal SQL As String = "")
      ConnStringID = Config_ConnStringID
      Init()
      CommandText = SQL
   End Sub

   Public Sub Init()
      If ConnString = "" Then
         ConnString = ConfigurationManager.ConnectionStrings(ConnStringID).ConnectionString
         Provider = ConfigurationManager.ConnectionStrings(ConnStringID).ProviderName
      End If
      If Conn Is Nothing Then
         PFactory = DbProviderFactories.GetFactory(Provider)
         Conn = PFactory.CreateConnection
         Conn.ConnectionString = ConnString
         Cmd = PFactory.CreateCommand
         Cmd.Connection = Conn
      End If
   End Sub

   Public ReadOnly Property PageCount() As Integer
      Get
         Return mPageCount
      End Get
   End Property

   Public Property CommandType() As Data.CommandType
      Get
         Return Cmd.CommandType
      End Get
      Set(ByVal value As Data.CommandType)
         Cmd.CommandType = value
      End Set
   End Property

   Public Property CommandText() As String
      Get
         Return Cmd.CommandText
      End Get
      Set(ByVal SQL As String)
         Cmd.CommandText = SQL
      End Set
   End Property

   Public Sub AddParam(ByVal Name As String, ByVal Value As Object)
      Dim p As DbParameter = PFactory.CreateParameter
      p.ParameterName = Name
      p.Value = Value
      Cmd.Parameters.Add(p)
      p = Nothing
   End Sub

   Public Sub AddParam(ByVal Name As String, ByVal Dir As Data.ParameterDirection, ByVal DType As DbType, Optional ByVal Value As Object = Nothing)
      Dim p As DbParameter = PFactory.CreateParameter
      p.ParameterName = Name
      p.Value = Value
      If DType > -1 Then p.DbType = DType
      p.Direction = Dir
      Cmd.Parameters.Add(p)
      p = Nothing

   End Sub

   Public Function ParamValue(ByVal Name As String) As Object
      Dim p As DbParameter
      p = Cmd.Parameters(Name)
      If p Is Nothing Then
         Return Nothing
      Else
         Return p.Value
      End If
   End Function

   Public Sub ClearParams()
      Cmd.Parameters.Clear()
   End Sub

   ''' <summary>Executes Command and return DataSet
   ''' ,Does Paging If PageSize and PageCount is set
   ''' and Calculate PageCount if CountCommandText is set</summary>
   ''' <param name="SrcTable">Table name</param>
   Public Function ExecuteDataSet(ByVal SrcTable As String) As DataSet
      mPageCount = 0
      Conn.Open()
      ExecuteCount()

      Dim DS As New DataSet
      Dim DA As DbDataAdapter = PFactory.CreateDataAdapter()
      DA.SelectCommand = Cmd

      'Do Paging
      If PageSize > 0 And PageIndex > 0 Then
         DA.Fill(DS, (PageIndex - 1) * PageSize, PageSize, SrcTable)
      Else
         DA.Fill(DS, SrcTable)
      End If
      DA.Dispose()
      DA = Nothing
      Conn.Close()
      Return DS
   End Function

   ''' <summary>Uses ExecuteDataSet to execute the command and return only the DataTable</summary>
   ''' <param name="SrcTable">Table name</param>
   Public Function ExecuteDataTable(ByVal SrcTable As String) As DataTable
      Dim DS As DataSet = ExecuteDataSet(SrcTable)
      Dim DT As DataTable
      DT = DS.Tables(SrcTable)
      DS = Nothing
      Return DT
   End Function

   Public Enum ReaderPaging
      ''' <summary>Normal command Execution</summary>
      None = 0
      ''' <summary>Moves reader to start record based on PageSize and PageIndex</summary>
      MovePaging = 1
      ''' <summary>Fix for Top Clause Paging: If it is Last Page and Records Count is less than Pagesize</summary>
      TopClausePaging = 2
   End Enum

   ''' <summary>Executes Command and return DataReader
   ''' ,does paging by moving to start record if MovePaging is selected
   ''' and/or Calculate PageCount if CountCommandText,PageSize,PageIndex are set </summary>
   ''' <param name="Paging"></param>
   Public Function ExecuteReader(Optional ByVal Paging As ReaderPaging = ReaderPaging.None) As DbDataReader
      mPageCount = 0
      Conn.Open()
      Dim count As Integer = ExecuteCount()

      Dim Reader As DbDataReader, i As Integer = 0
      Reader = Cmd.ExecuteReader(CommandBehavior.CloseConnection)
      'Do Paging
      If PageSize > 0 And PageIndex > 0 Then
         If Paging = ReaderPaging.MovePaging Then
            For i = 0 To ((PageIndex - 1) * PageSize) - 1
               If Not Reader.Read Then Exit For
            Next
         ElseIf Paging = ReaderPaging.TopClausePaging Then
            If PageSize > 1 And PageCount > 1 And PageIndex = PageCount And (count Mod PageSize <> 0) Then
               For i = 1 To PageSize - (count Mod PageSize)
                  If Not Reader.Read Then Exit For
               Next
            End If
         End If
      End If

      Return Reader
   End Function

   ''' <summary>execute count command if paging params are set and calculate PageCount</summary>
   Private Function ExecuteCount() As Integer
      If CountCommandText > "" And PageSize > 0 And PageIndex > 0 Then
         Dim Tmp As String = Cmd.CommandText
         Cmd.CommandText = CountCommandText
         Dim Count As Integer = CInt(Cmd.ExecuteScalar())
         mPageCount = Math.Ceiling(Count / PageSize)
         Cmd.CommandText = Tmp
         Return Count
      End If
      Return 0
   End Function

   ''' <summary>Executes Non Query,that's it!</summary>
   Public Function ExecuteNonQuery() As Integer
      Dim rows As Integer
      Conn.Open()
      rows = Cmd.ExecuteNonQuery()
      Conn.Close()
      Return rows
   End Function

   ''' <summary>Executes an Insert query and return Identity column by appending ";Select Scope_Identity()" for SQL Server or executing another command for MS Access</summary>
   Public Function ExecuteIdentity() As Object
      Dim ID As Object
      Conn.Open()
      If Provider.ToLower = "System.Data.SqlClient".ToLower Then
         Cmd.CommandText = Cmd.CommandText & ";Select Scope_Identity()"
         ID = Cmd.ExecuteScalar()
      Else
         Cmd.ExecuteNonQuery()
         Cmd.CommandText = "Select @@Identity"
         ID = Cmd.ExecuteScalar()
      End If
      Conn.Close()
      Return ID
   End Function
   ''' <summary>Executes scalar</summary>
   ''' <returns>Object</returns>
   Public Function ExecuteScalar() As Object
      Dim o As Object
      Conn.Open()
      o = Cmd.ExecuteScalar()
      Conn.Close()
      Return o
   End Function

   Protected Overrides Sub Finalize()
      If Conn.State = ConnectionState.Open Then Conn.Close()
      Conn = Nothing
      Cmd = Nothing
      MyBase.Finalize()
   End Sub
End Class


Example of usage for Insert query
Dim Cmd As New GenericCommand("StrConn2")
Cmd.CommandText = "insert into Users(Name,Email) values (@Name,@Email)"
Cmd.AddParam("@Name", "Some Name")
Cmd.AddParam("@Email", "Some Email")
Dim UserID as integer = Cmd.ExecuteIdentity()
Cmd = Nothing

And example of usage for Select Query with paging using DataSet method
Dim Cmd As New GenericCommand("StrConn2")
Cmd.PageSize = 10
Cmd.PageIndex = 3
'Count Query
Cmd.CountCommandText = "SELECT COUNT(*) From Users where Status>=@Status"
'Select Query ; if you don't use Top Cmd.PageSize * Cmd.PageIndex : performance degrades badly in large tables
Cmd.CommandText = "SELECT Top " & (Cmd.PageSize * Cmd.PageIndex) & " * FROM Users where Status>=@Status"
Cmd.AddParam("@Status", 1)
Dim Tbl As DataTable = Cmd.ExecuteDataTable("Users")
Dim PageCount As Integer = Cmd.PageCount
For Each Row As DataRow In Tbl.Rows
  'Do Something
Next
Tbl = Nothing
Cmd = Nothing

Read more on the subject @ MSDN [Writing Generic Data Access Code in ASP.NET 2.0 and ADO.NET 2.0]

Tags: ADO.Net , ASP.Net , DAL , VB.Net