Inheriting Data Access Layer functions

• Download source - 28.2 Kb

Introduction

There are a number of different ways to program the Data Access Layer (DAL). One way involves dragging data access components onto the IDE and letting visual studio automatically build the SQL. This works fine until you want to change the database from say MS SQL to Oracle, in which case you need to go through all the auto generated SQL strings to change the SQL syntax to run on Oracle. The approach described here is to inherit all the basic SQL functions from one class, then override these functions as necessary. In this way all functions defined in the Table classes and inherited classes can be seen in Intellisense. This makes application building much faster because all the tedious SQL generating code is located in one place leaving the programming of table data access only a question of defining the database table structure.

Using the code

Lets start by looking at how the table DAL class would look like

Public Class Adr_Address
    Inherits DBTable

    Public Structure Address_Type
        Public Address_ID As Integer
        Public Name As String
        Public Address As String
        Public Birthday As DateTime
    End Structure

    Private DBConnStr As String

    Public Sub New(ByVal DBConnStr As String)
        MyBase.New(DBConnStr)
    End Sub
End Class

The Structure Address_Type mirrors the structure of the database table. A variable of type Address_Type is passed to the inherited functions of DBTable. These functions use reflection to determine the names of the table fields and their corresponding value.

Inside the business layer an update to a specific row of Adr_Address can be made as follows

Try
    Dim AddressDB As New Adr_Address(strConn)        
'An instance of the DAL Component dealing with a table called Adr_Address

'strConn contains the connection string

    Dim AddressRow As Adr_Address.Address_Type       
'One Row of the Adr_Address table

    AddressRow.Name = "Nigel"
    AddressRow.Address = "Sunny Switzerland"
    AddressRow.Address_ID = 1

    If Not AddressDB.PutRecord(AddressRow) Then  
       MessageBox.Show("Unable to update address")
    End If

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

The class name is the same as the table name Adr_Address. The structure of the table is identical to the structure Address_Type. By passing an object of type Address_Type it is possible for the inherited class DBTable to determine the structure of the UPDATE command.

In the inherited DBTable we know the name of the table because it is the same as the class name. This can be determined by Me.GetType.Name. We receive the definition of the table structure and the updated values in ByRef Row As ValueType. The rest of the code then iteratively goes through this structure to build the SQL statement

Imports System.Reflection
Public Class DBTable
  
   Public Function PutRecord(ByRef Row As ValueType, _
   ByVal RowIndex As Integer) As Boolean
      Dim ErrLoc As String = ClassName + ".PutRecord" 
      Dim SQL As String
      Try
         Dim i As Integer
          Dim Seperator As String = ""
         Dim fields() As FieldInfo
          fields = Row.GetType.GetFields

          REM Build Database query

          SQL = "UPDATE " + ClassName + " SET "
         For i = 0 To fields.Length - 1
            If fields(i).Name <> TableIndex() Then
               Select Case fields(i).FieldType.ToString
                  Case "System.String"
                      If Not fields(i).GetValue(Row) Is Nothing Then
                         SQL &= Seperator & fields(i).Name & " = "
                             SQL &= SQLStringValue(fields(i).GetValue(Row).ToString)
                             Seperator = ","
                         End If
                  Case "System.DateTime"
                         SQL &= Seperator & fields(i).Name & " = "
                      SQL &= SQLDateValue(fields(i).GetValue(Row))
                         Seperator = ","
                     Case "System.Boolean"
                         SQL &= Seperator & fields(i).Name & " = "
                         SQL &= SQLBooleanValue(fields(i).GetValue(Row))
                         Seperator = ","
                     Case "System.Int32"
                      If fields(i).GetValue(Row) <> -1 Then
                             SQL &= Seperator & fields(i).Name & " = "
                             SQL &= " " & CStr(fields(i).GetValue(Row)) & " "
                             Seperator = ","
                         End If
                   End Select
                End If
             Next
               SQL &= "WHERE "
               SQL &= TableIndex() + "= " + RowIndex.ToString + " "
               If ExecuteSQL(SQL) Then
                Return True
             Else
                Return False
             End If
           Catch ex As Exception
               ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
               Return False
         End Try
   End Function
..
End Class 

In the above code we pass the row of data that we want to update through the parameter ByRef Row As ValueType. The type and value of the data is made by filling the array Fields of type FieldInfo in the line fields = Row.GetType.GetFields. The column name is determined by fields(i).Name, the type by fields(i).FieldType.ToString and the value by fields(i).GetValue(Row).ToString. The database dependent SQL Syntax is made by inherited functions from DBAccess, for example the syntax of date values is dealt with in the inherited function SQLDateValue. This code expects the table to have the following structure.

In this case the table index is of type autonumber.

The advantage of this method is that if you have many tables you program the SQL only once. If you require special functions you can override the underlying functions or simply make new ones in the child class. DBTable can be extended to include synchronization, Import/Export, search functions, transaction logging etc. These functions will then be inherited throughout your application. As applications grow and become more complex it is a great advantage to have the SQL centralized.

Improvements

To change the database in this example you need to recompile the code. This could be made at runtime if I where to pass the database type to the DAL. I could then load an assembly corresponding to the database thus making it possible to change the database at runtime. Recently I have been reading about ObjectSpaces. This will probably be the way to go in the future however I believe this will not be shipped with Whidbey.