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