Geeks With Blogs
John Oriente blog
| Home |

Hello everyone

This is my first blog entry at this site and first in a couple of years, but I wanted to share something I created with some help.  Since generics came out with VS 2005 I wondered if it were possible to create a completely generic data access layer.  I also wanted to take advantage of some cool features like custom attributes, enterprise library, and reflection.  One that could get and update objects regardless of the type of object being manipulated.  With the help of some great authors I have at least a working class.  So here we go.

Step One  Creating the interface

First thing we need is an interface so that all of our business objects know how to get data from the database into itself.  I also want to use an interface as a constraint so that only the business classes that implements this interface will interact with the database

Imports Microsoft.VisualBasic
Imports System.Data

Interface IBusinessEntity
    Sub Fill(ByVal reader As IDataReader)
End Interface

Very simple right!  Each business object will have a fill method and is passed in a datareader

 

Step Two  Creating a custom attribute

When saving a record to the database, pre-generics the pseudo code goes something like this

  • Pass a bunch of parameters to a save function
  • create a connection
  • create a command
  • manually map the passed parameters to the stored procedures parameter (usually hard-coded)
  • then execute a non query

Don't worry, there is a better way.  Let's create a custom attribute to decorate our properties

Imports Microsoft.VisualBasic

<AttributeUsage(AttributeTargets.Property, AllowMultiple:=True, Inherited:=False)> _
Public Class StoredProcParameterAttribute
    Inherits Attribute

    Private _storedProcParameter As String

    Public Property StoredProcParameter() As String
        Get
            Return _storedProcParameter
        End Get
        Set(ByVal value As String)
            _storedProcParameter = value
        End Set
    End Property

    Public Sub New(ByVal StoredProcParameter As String)
        Me.StoredProcParameter = StoredProcParameter
    End Sub

End Class

Ok...so what's going on here.  We are creating a new attribute that is targeted for properties only and we are allowing multiple properties in a class to get this attribute.  The attribute will associate the stored procedure's parameter name with a class property.  This will rely on the fact that every stored procedure that uses a certain field will be consistently named (not too much to ask for I think).   So lets take a look at our business class

Step 3  Create a business class

Imports Microsoft.VisualBasic

Public Class Contact
    Implements IBusinessEntity

    Private _contactID As Nullable(Of Integer)
    Private _firstName As String
    Private _lastName As String
    Private _retailer As String
    Private _storeID As String
   
    <StoredProcParameter("@piContactID")> _
    Property ContactID() As Nullable(Of Integer)
        Get
            Return _contactID
        End Get
        Set(ByVal value As Nullable(Of Integer))
            _contactID = value
        End Set
    End Property

    <StoredProcParameter("@psFN")> _
    Property FirstName() As String
        Get
            Return _firstName
        End Get
        Set(ByVal value As String)
            _firstName = value
        End Set
    End Property

    <StoredProcParameter("@psLN")> _
    Property LastName() As String
        Get
            Return _lastName
        End Get
        Set(ByVal value As String)
            _lastName = value
        End Set
    End Property

    Property Retailer() As String
        Get
            Return _retailer
        End Get
        Set(ByVal value As String)
            _retailer = value
        End Set
    End Property

    <StoredProcParameter("@piStoreID")> _
    Property StoreID() As Integer
        Get
            Return _storeID
        End Get
        Set(ByVal value As Integer)
            _storeID = value
        End Set
    End Property

    Public Sub Fill(ByVal reader As System.Data.IDataReader) Implements IBusinessEntity.Fill
        _contactID = reader.GetInt32(0)
        _firstName = reader.GetString(5)
        _lastName = reader.GetString(6)
        _retailer = reader.GetString(3)
    End Sub

End Class

"It's all making sense now, and in ways you could never have imaged" (Voldermort to Harry Potter in the Deathly Hallows).  Each property is now associated with a stored proc parameter via an attribute and we are going to use reflection to find the property of the class and it value when we are going to update the record.  I used nullable(of T) for the primary key fields because that is what the proc uses to determine if an update or a insert is occurring (if the passed parameter is null).  Notice the Retailer field.  It has no attributes, that is because that field is not part of the contacts table, it is from a joined table. 

Step 4  Building the DAL

Now we are down to the nitty gritty.

First we will handle getting the objects.  In all cases we are either going to get 1 object or a list of them so we need to handle both.

There are 2 private methods in the DAL GetItem and GetItems

Private Function GetItem(Of T As {IBusinessEntity, New})(ByVal commandText As String, ByVal parameterNames As String(), ByVal parameterValues As Object()) As T

        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim dbCommand As DbCommand = db.GetStoredProcCommand(commandText)
        Dim param As System.Data.SqlClient.SqlParameter

        If Not IsNothing(parameterNames) And parameterNames.Length > 0 Then
            For i As Integer = 0 To parameterNames.Count - 1
                param = New System.Data.SqlClient.SqlParameter
                param.ParameterName = parameterNames(i)
                param.Direction = ParameterDirection.Input
                param.Value = parameterValues(i)
                dbCommand.Parameters.Add(param)
            Next
        End If

        Dim dataItem As T = New T
        Try
            Dim dbReader As IDataReader = db.ExecuteReader(dbCommand)
            If Not IsNothing(dbReader) Then
                dbReader.Read()
                dataItem.Fill(dbReader)
            End If
        Catch
        Finally
            dbCommand.Connection.Close()
        End Try

        Return dataItem

    End Function

    Private Function GetItems(Of T As {IBusinessEntity, New})(ByVal commandText As String, ByVal parameterNames As String(), ByVal parameterValues As Object()) As List(Of T)

        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim dbCommand As DbCommand = db.GetStoredProcCommand(commandText)
        Dim param As System.Data.SqlClient.SqlParameter

        If Not IsNothing(parameterNames) And parameterNames.Length > 0 Then
            For i As Integer = 0 To parameterNames.Count - 1
                param = New System.Data.SqlClient.SqlParameter
                param.ParameterName = parameterNames(i)
                param.Direction = ParameterDirection.Input
                param.Value = parameterValues(i)
                dbCommand.Parameters.Add(param)
            Next
        End If

        Dim dataItem As T
        Dim listItems As List(Of T) = New List(Of T)
        Try
            Dim dbReader As IDataReader = db.ExecuteReader(dbCommand)
            If Not IsNothing(dbReader) Then
                While dbReader.Read()
                    dataItem = New T
                    dataItem.Fill(dbReader)
                    listItems.Add(dataItem)
                End While
            End If
        Catch e As Exception

        Finally
            dbCommand.Connection.Close()
        End Try

        Return listItems

    End Function

If the signatures look a little funny that is because I am using generic constraints.  I am only allowing new object that implements our interface into these methods.  What's nice is that this check happens at design time so you can't just stick any old object in there.  I am passing a command string, a array of parameter names and values and am returning whatever T I have passed in or a list(of T).  I use the fill method (I know it is there by the constraint) to ship the reader to the object to fill the fields

Public access

How to get the data  out.  It's easy, just a one liner really and here are some examples

Public Function GetContacts(ByVal account As String) As List(Of Contact)
       Return GetItems(Of Contact)("d_prc_Accounts_SearchContacts", New String() {"@psAccount", "@piRepID"}, New String() {account, 1})
   End Function

   Public Function GetContact(ByVal account As String) As Contact
       Return GetItem(Of Contact)("d_prc_Accounts_SearchContacts", New String() {"@psAccount", "@piRepID"}, New String() {account, 1})
   End Function

and finally the call from the UI layer

Dim dal As New DAL
Dim account As String

        account = Me.TextBox1.Text
        Me.UltraWebGrid1.DataSource = dal.GetContacts(account)
        Me.UltraWebGrid1.DataBind()

Ok  how about updating.  Again there are 2 private function in the DAL class

Private Sub SaveItem(Of T As IBusinessEntity)(ByVal SaveObject As T, ByVal commandText As String)

        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim dbCommand As DbCommand = db.GetStoredProcCommand(commandText)

        db.DiscoverParameters(dbCommand)

        FillParameters(SaveObject, dbCommand)
        db.ExecuteNonQuery(dbCommand)

    End Sub

    Private Sub FillParameters(Of T As IBusinessEntity)(ByVal InputClass As T, ByRef CurrentCommand As DbCommand)

        Dim inputType As Type = InputClass.GetType
        Dim storedProcAttribute As StoredProcParameterAttribute
        Dim typeProperties() As PropertyInfo = inputType.GetProperties
        Dim attributes() As Attribute

        For Each parameter As SqlParameter In CurrentCommand.Parameters

            For Each propInfo As PropertyInfo In typeProperties
                attributes = propInfo.GetCustomAttributes(GetType(StoredProcParameterAttribute), True)
                If Not IsNothing(attributes) And attributes.Length = 1 Then
                    storedProcAttribute = attributes(0)
                    If Not IsNothing(storedProcAttribute) And storedProcAttribute.StoredProcParameter.Length > 0 Then
                        If storedProcAttribute.StoredProcParameter = parameter.ParameterName Then
                            parameter.Value = propInfo.GetValue(InputClass, Nothing)
                            Exit For
                        End If
                    End If
                End If
            Next
        Next

    End Sub

The first sub sets up the connection and command and uses discoverparameters to fill the parameters collection of the command.  It then calls FillParameters which uses reflection to find our custom attribute of each property  if there is a match between the parameter name and the attribute value, the parameter value is set to the property value (again using reflection).  I am using constraints again so only our business objects can be passed.

Public access is again a one liner

Public Sub SaveContact(ByVal contact As Contact)
        SaveItem(contact, "d_prc_Accounts_SaveContact")
    End Sub

and the UI call is also pretty simple

Dim newcontact As New Contact

       newcontact.StoreID = Integer.Parse(Me.TextBox3.Text)
       newcontact.FirstName = Me.TextBox1.Text
       newcontact.LastName = Me.TextBox2.Text


       Dim DAL As New DAL

       DAL.SaveContact(newcontact)

So that's it.  It is actually pretty easy and I think will reduce the amount of code you will need to write in your DAL, just 4 private functions and a bunch of 1 liners.

I would like to improve on some things.  Like passing a Dictionary(of TKey, TValue) instead on string and object arrays in the get methods.

Hope you find this useful

and feedback is always appreciated

Posted on Monday, March 31, 2008 9:58 AM | Back to top


Comments on this post: A Generic Data Access Layer in VB.Net

# re: A Generic Data Access Layer in VB.Net
Requesting Gravatar...
John,

Please keep writing, this is awesome. Excellent and elegant

Thanks
Left by dan on Sep 17, 2008 9:01 PM

# re: A Generic Data Access Layer in VB.Net
Requesting Gravatar...
what about performance ?? Thanks
Left by alhambra eidos kiquenet on Feb 18, 2009 2:56 PM

# re: A Generic Data Access Layer in VB.Net
Requesting Gravatar...
Have you done more work on this? I would like to see a full example.
Left by chris on Jul 16, 2009 7:26 PM

# re: A Generic Data Access Layer in VB.Net
Requesting Gravatar...
I can see that you use DAAB (Data Access Application Block). One of the advantages of using this block is that we can write DAL code without referencing at all the type of the database we use.
So according to the above example i would like to post two things if i may.
First we can avoid using the @ character ahead of a parameter name. DAAB can see from the connection string that we access MS SQL database and it puts the character by "itself". Thus we don't have any problem if we target a database that don't use the @ character as a parameter name.
Second i see the use of
Dim param As System.Data.SqlClient.SqlParameter. So according to what i said before this violates and cancels the benefits of using DAAB.
But yes it is an elegant solution after all and will use it with the changes i mentioned.
Left by George P. on Nov 05, 2009 1:48 PM

# re: A Generic Data Access Layer in VB.Net
Requesting Gravatar...
Amazing article.. ty John. Keep writing.
Left by Eliel on Dec 10, 2010 6:34 AM

# re: A Generic Data Access Layer in VB.Net
Requesting Gravatar...
You're hitting the DAL from the UI though...
Left by Lightsabre on Mar 21, 2011 8:20 AM

# re: A Generic Data Access Layer in VB.Net
Requesting Gravatar...
hello! gud day.. can you pls send me a sample system like this?.. tnx
Left by Deane Francis on Sep 04, 2011 8:35 PM

# www.wallpapersup.com
Requesting Gravatar...
learn about the best of news, updates, reviews and more at chahi articles www.wallpapersup.com
Left by Asmat hayat on Dec 10, 2011 3:32 AM

Your comment:
 (will show your gravatar)


Copyright © John Oriente | Powered by: GeeksWithBlogs.net