http://www.developer.com/net/vb/article.php/3650241/The-Only-Data-Access-Layer-Youll-Ever-Need.htm

Back to article

The Only Data Access Layer You'll Ever Need?


December 20, 2006

Audacity and hyperbole are great attention grabbers. Do I really think the data access layer (DAL) I discuss in this article is the only one you will ever need? In a sense, yes. The sample in this article can work in most situations, so you may not have to spend much time in the future implementing a basic DAL. Read on to determine how well it suits your needs.

A DAL moves data in and out of a database; a useful one makes managing persistence easier than writing CRUD—create, read, update, and delete—behaviors all over your code. The DAL demonstrated in this article does just that with a relatively few lines of code and maximum flexibility and simplicity. It supports reading, writing, and transactions, as well as any database provider using an abstract factory pattern, and a core reader that uses reflection.

The result is that all you should have to write generally is an event handler for reads and one for writes for each kind of singular or composite object you want to construct. This means that you will need to write additional code to do only the following:

  1. Move data from a read event handler
  2. Grab items like primary keys for inserts in a writer event handler
  3. Manage composite object transaction writes

Implementing a Data Access Layer Kernel

Normalized relational databases and objects generally don't have one-to-one matchups between them. This means that someone has to write code that transmogrifies normalized rows and columns into business objects. This code is often called the DAL, which poses three challenges to the developer.

The first challenge of a DAL is that a relational database defines four DB operations: insert, update, delete, and select. In the object-oriented programming (OOP) world, the first thing developers can do to deal with these four behaviors is simplify them to two OOP behaviors: read and write. A select is the read, and update, insert, and delete are all consolidated into the write.

The second thing developers need to do is realize that business objects don't need—and shouldn't have—internal read and write baggage. Why? Because every business object would need redundant code for reading and writing. This code can be and should be factored out just as Microsoft does it with DataSets and DataTables. Factoring out read/write shrinks the overall size of the code base.

Finally, the only real differences between any read and write are the SQL that performs the action, the inputs and outputs, and how these inputs and outputs are handled. Everything else about reading and writing is a connection, transaction, command, or reader, and the initiator—the behavior that sends the request to your database server. This means developers can factor out all of the latter behaviors and reduce the number of times they need to write the connect, transact, and command behavior.

Collectively, these three elements—consolidate behaviors to read and write, factor out persistence from business objects, and separate basic database operations from object-specific details—make up the collective data access solution this article provides.

Implementing the Abstract Provider Factory

The application development industry has generally accepted the notion that good code includes refactored code and patterns. This example uses both refactored code and design patterns. Specifically, it uses the Singleton and Abstract Factory creational patterns to support using multiple providers. The code in Listing 1 simply defines a factory class that returns ADO interfaces and a child class that returns a specific instance of ADO classes that implement these interfaces.

Listing 1: The Abstract Factory and Child SQL Server Database Factory Classes

Public MustInherit Class DbFactory

   Public Shared Function CreateFactory() As DbFactory
      Return New SqlFactory()
   End Function


   Public MustOverride Function CreateConnection(ByVal _
      connectionString As String) As IDbConnection
   Public MustOverride Function CreateCommand(ByVal commandText _
      As String) As IDbCommand
   Public MustOverride Function CreateAdapter(ByVal commandText _
      As String) As IDbDataAdapter
   Public MustOverride Function CreateParameter() As IDbDataParameter
   Public MustOverride Function CreateParameter(ByVal name As String, _
      ByVal value As Object) As IDbDataParameter
   Public MustOverride Function CreateParameter(ByVal name As String, _
      ByVal type As DbType, ByVal size As Integer) As IDbDataParameter
   Public MustOverride Function GetParameterValue(ByVal parameter _
      As Object) As Object
   Public MustOverride ReadOnly Property ConnectionString() As String

End Class

Public Class SqlFactory
   Inherits DbFactory


   Public Overrides ReadOnly Property ConnectionString() As String
      Get
         Return "Data Source=localhost;Initial Catalog=Northwind; _
            Integrated Security=True"
      End Get
   End Property

   Public Overrides Function CreateAdapter(ByVal commandText _
      As String) As System.Data.IDbDataAdapter
      Return New SqlDataAdapter(New SqlCommand(commandText))
   End Function

   Public Overrides Function CreateCommand(ByVal commandText As String) _
      As System.Data.IDbCommand
      Return New SqlCommand(commandText)
   End Function

   Public Overrides Function CreateConnection(ByVal connectionString _
      As String) As System.Data.IDbConnection
      Return New SqlConnection(connectionString)
   End Function

   Public Overloads Overrides Function CreateParameter() _
      As System.Data.IDbDataParameter
      Return New SqlParameter()
   End Function

   Public Overloads Overrides Function CreateParameter(ByVal name _
      As String, ByVal value As Object) As System.Data.IDbDataParameter
      Return New SqlParameter(name, value)
   End Function

   Public Overloads Overrides Function CreateParameter(ByVal name _
      As String, ByVal type As System.Data.DbType, ByVal size _
      As Integer) As System.Data.IDbDataParameter
      Dim parm As SqlParameter = New SqlParameter()
      parm.ParameterName = name
      parm.DbType = type
      parm.Size = size
      Return parm
   End Function

   Public Overrides Function GetParameterValue(ByVal parameter _
      As Object) As Object
      Debug.Assert(parameter <> Nothing)
      If (parameter Is Nothing) Then Return Nothing

      Return CType(parameter, SqlParameter).Value
   End Function
End Class

Listing 1 includes an abstract base factory and a SQL child factory support using the DAL code with multiple providers. (You will have to implement other child factories, but these are pretty easy if you use the SQL factory as a exemplar.) If you implement your DAL in terms of interfaces, you don't have to change your DAL if you change providers (which does happen).

Implementing Generic Event Handlers

Developers can never determine in advance which objects they may be reading and writing. As a solution, they can define the hooks—or event handlers—as generic event handlers. Listing 2 shows how these support managing any object in the sample DAL.

Listing 2: Generic Event Handlers Defer What Kinds of Objects the Data Layer Will Manage

Public Delegate Sub WriteEventHandler(Of T)(ByVal o As T, _
   ByVal command As IDbCommand)
Public Delegate Function ReadEventHandler(Of T)(ByVal reader _
   As IDataReader) As T

When developers invoke a read operation, the type will be defined by whatever type the developers initialize the parameterized type to be. The same goes for the write operation.

Writing the Basic CRUD Methods

The example defines the basic DAL using ADO.NET interfaces and defines the bits that read and write as parameterized—or generic—types, which collectively offers the greatest flexibility. To satisfy the previously stated objective, developers need to define a read behavior, a write behavior, and a composite object write behavior (in other words, developers need a write that supports transactions). Listing 3 shows the bulk of the DataAccess class supporting read, write, and transaction write.

Listing 3: The DataAccess Class

Public Class DataAccess
   Private Shared factory As DbFactory = DbFactory.CreateFactory()

   Private Sub New()

   End Sub

   Public Shared Function Read(Of T)(ByVal procedureName As String, _
      ByVal handler As ReadEventHandler(Of T), _
      ByVal ParamArray parameters() As IDbDataParameter) As T

      Debug.Assert(handler <> Nothing)

      Using connection As IDbConnection = _
         factory.CreateConnection(factory.ConnectionString)

         connection.Open()
         Dim command As IDbCommand = factory.CreateCommand(procedureName)
         command.Connection = connection
         command.CommandType = CommandType.StoredProcedure

         If (parameters Is Nothing = False) Then
            Dim p As IDbDataParameter
            For Each p In parameters
               command.Parameters.Add(p)
            Next
         End If

         Dim reader As IDataReader = command.ExecuteReader()

         Return handler(reader)

      End Using

   End Function

   Public Shared Sub Write(Of T)(ByVal o As T, _
      ByVal procedureName As String, _
      ByVal handler As WriteEventHandler(Of T), _
      ByVal ParamArray parameters() As IDbDataParameter)

      Using connection As IDbConnection = _
         factory.CreateConnection(factory.ConnectionString)
         connection.Open()
         Write(o, procedureName, handler, connection, Nothing, parameters)
      End Using
   End Sub

   Public Shared Sub Write(Of T)(ByVal o As T, _
      ByVal procedureName As String, _
      ByVal handler As WriteEventHandler(Of T), _
      ByVal connection As IDbConnection, _
      ByVal transaction As IDbTransaction, _
      ByVal ParamArray parameters() As IDbDataParameter)

      Dim command As IDbCommand = factory.CreateCommand(procedureName)
      command.CommandType = CommandType.StoredProcedure
      command.Connection = connection
      command.Transaction = transaction

      If (parameters Is Nothing = False) Then
         Dim p As IDbDataParameter
         For Each p In parameters
            command.Parameters.Add(p)
         Next

      End If

      If (handler <> Nothing) Then
         handler(o, command)
      End If

   End Sub

End Class

The DAL works like this. You pass in a stored procedure name, an event handler to manage reading or writing, and any number of parameters to filter the result set. The nuts and bolts operations, such as creating a connection or a command object, are performed, and then the reader or writer, as the case may be, is used to manage the business objects.

No matter how many objects you define, you can use this basic DataAccess class as it is. You also may want to add an additional read or two that return scalars in addition to the reader, but that depends on how you write your stored procedures.

The Write behavior's event handler is used to update objects after an insert, for example, to obtain update a new object's unique identifier.

Writing a One-Size-Fits-Many Reader

Another benefit to the DataAccess class is that developers can handle basic reads with a single read event handler that uses reflection. Because the example defined the DataAccess methods as generic methods also, the same read can read one object or a collection of objects. To support non-composite reads of single objects or collections, I added methods to the DataAccess class (see Listing 4).

Listing 4: Added Methods to the DataAccess Class

Public Shared Function SafeRead(Of T)(ByVal field As T, _
   ByVal reader As IDataReader, ByVal name As String) As T

   If (reader(name).Equals(System.DBNull.Value) = False) Then
      Dim result As Object = reader(name)
      Return CType(Convert.ChangeType(result, GetType(T)), T)
   Else
      Return field
   End If

End Function


' read the public properties and use these to read field names
Public Shared Function OnReadAny(Of T As New)(ByVal reader _
   As IDataReader) As T

   Dim genericType As Type = GetType(T)

   Dim properties() As PropertyInfo = _
      genericType.GetProperties(BindingFlags.Instance _
      Or BindingFlags.Public)

   Dim prop As PropertyInfo
   Dim obj As T = New T()

   For Each prop In properties

      Try
         Dim columnName As String = GetColumnName(prop)

         If (reader(columnName).Equals(System.DBNull.Value) = False) Then
            Dim value As Object = reader(columnName)
            prop.SetValue(obj, value, Nothing)
         End If

      Catch ex As Exception
         Debug.WriteLine("Couldn't write " + prop.Name)
      End Try

   Next

   Return obj
End Function

Private Shared Function GetColumnName(ByVal prop As PropertyInfo) _
   As String

   Debug.Assert(prop Is Nothing = False)
   If (prop Is Nothing) Then Return ""

   Dim attributes() As Object = prop.GetCustomAttributes(True)
   Dim attr As Object
   For Each attr In attributes
      If (TypeOf attr Is MYVB.BusinessObjects.ColumnNameAttribute) Then
         Debug.WriteLine("Uses ColumnNameAttribute")
         Return CType(attr, _
            MYVB.BusinessObjects.ColumnNameAttribute).ColumnName
      End If
   Next

   Return prop.Name

End Function


' you can do this - no appreciable difference between this and
' non-reflected version. if db field names don't match public
' properties, you can use an attribute to indicate the field
' name
Public Shared Function OnReadAnyList(Of T As New)(ByVal reader _
   As IDataReader) As List(Of T)
   If (reader Is Nothing) Then Return New List(Of T)()

   Dim list As List(Of T) = New List(Of T)()

   While (reader.Read())
      list.Add(OnReadAny(Of T)(reader))
   End While

   Return list
End Function

Note that I added a method called GetColumnName. This method looks for custom attributes. By default, the code in Listing 4 looks for column names that match property names, but this may not always be the case. You can manually map columns to properties with a custom attribute.

I know that code that uses reflection may be slower than code that doesn't, but I tested this code and the reflection did not seem noticeably slower than non-Reflective readers.

Creating the Custom Column-Matching Attribute

Listing 5 defines a custom attribute that permits me to specify a column name for a property. You can use this attribute when you want a property name like CustomerName and some helpful DBA creates a database column like CustNm (or some such nonsense).

Listing 5: A Custom Attribute to Tag a Property with a Mismatched Column Name

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

   Private FColumnName As String

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

   Public Property ColumnName() As String
      Get
         Return FColumnName
      End Get
      Set(ByVal value As String)
         FColumnName = value
      End Set
   End Property

End Class
Tip: Use whole words and complete names for things as much as possible, and be wary of any code, tool, or language that does not support real words.

Reviewing Write Behaviors

Write behaviors work pretty much like reads. You need to use the factory to create a parameter object for every field you'd like to write. If you are writing composite objects, you custom write a class (for example, the CustomerAccess class) and will need to kick open a connection and create a transaction. You can manage inserts versus updates by examining the primary key field in the stored procedure. And, of course, you will need to write your own stored procedures.

Putting It All Together

With the basic code described so far, you need to do significantly less work to flesh out the rest of any DAL. You will probably want a data access class for groups of business objects. These classes will contain code to initiate read and write calls and the read and write handlers themselves.

Listing 6 shows a basic CustomerAccess class that kicks off reads (and writes, not shown), and contains the read (and write) handlers. It demonstrates how to implement a read event handler and it also demonstrates how to invoke the reflection-read.

Listing 6: The CustomerAccess Class

Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Configuration
Imports System.Collections.Generic
Imports System.Web
Imports MYVB.BusinessObjects


Public Class CustomerAccess

   Public Shared Function GetCustomers() As List(Of Customer)
      'Return DataAccess.Read(Of List(Of Customer))("GetCustomers",
      'AddressOf OnReadCustomers)
      Return DataAccess.Read(Of List(Of Customer))("GetCustomers", _
         AddressOf DataAccess.OnReadAnyList(Of Customer))

   End Function

   Public Shared Function OnReadCustomers(ByVal reader As IDataReader) _
      As List(Of Customer)
      If (reader Is Nothing) Then Return New List(Of Customer)()

      Dim customers As List(Of Customer) = New List(Of Customer)()

      While (reader.Read())
         customers.Add(OnReadCustomer(reader))
      End While

      Return customers

   End Function

   Private Shared Function OnReadCustomer(ByVal reader _
      As IDataReader) As Customer
      Debug.Assert(reader Is Nothing = False)
      Dim customerID As String = ""
      Dim companyName As String = ""
      Dim contactName As String = ""
      Dim contactTitle As String = ""
      Dim address As String = ""
      Dim city As String = ""
      Dim region As String = ""
      Dim postalCode As String = ""
      Dim country As String = ""
      Dim phone As String = ""
      Dim fax As String = ""

      customerID = DataAccess.SafeRead(Of String)(customerID, reader, _
         "CustomerID")
      companyName = DataAccess.SafeRead(Of String)(companyName, _
         reader, "CompanyName")
      contactName = DataAccess.SafeRead(Of String)(contactName, _
         reader, "ContactName")
      contactTitle = DataAccess.SafeRead(Of String)(contactTitle, _
         reader, "ContactTitle")
      address = DataAccess.SafeRead(Of String)(address, reader, "Address")
      city = DataAccess.SafeRead(Of String)(city, reader, "City")
      region = DataAccess.SafeRead(Of String)(region, reader, "Region")
      postalCode = DataAccess.SafeRead(Of String)(postalCode, reader, _
         "PostalCode")
      country = DataAccess.SafeRead(Of String)(country, reader, "Country")
      phone = DataAccess.SafeRead(Of String)(phone, reader, "Phone")
      fax = DataAccess.SafeRead(Of String)(fax, reader, "Fax")

      Return New Customer(customerID, companyName, contactName, _
         contactTitle, address, city, region, postalCode, country, _
         phone, fax)

   End Function
End Class

You can use this exemplar to implement all of your custom data access classes.

Tip: Although you do need to write some additional data access code for your custom objects, you could easily write a CodeDOM code generator or use a tool such as CodeRush to really speed up this part of the data access development.

Listing 7 shows the custom Customer object populated by the CustomerAccess class. If you need a collection of objects, use List<T>, the generic collection class.

Listing 7: An Example of a Basic Custom Business Object

<Serializable()> _
Public Class Customer

   Private FCustomerID As String
   <ColumnName("CustomerID")> _
   Public Property CustomerID() As String
      Get
         Return FCustomerID
      End Get
      Set(ByVal value As String)
         FCustomerID = value
      End Set
   End Property

   Private FCompanyName As String
   Public Property CompanyName() As String
      Get
         Return FCompanyName
      End Get
      Set(ByVal value As String)
         FCompanyName = value
      End Set
   End Property

   Private FContactName As String
   Public Property ContactName() As String
      Get
         Return FContactName
      End Get
      Set(ByVal value As String)
         FContactName = value
      End Set
   End Property


   Private FContactTitle As String
   Public Property ContactTitle() As String
      Get
         Return FContactTitle
      End Get
      Set(ByVal value As String)
         FContactTitle = value
      End Set
   End Property

   Private FAddress As String
   Public Property Address() As String
      Get
         Return FAddress
      End Get
      Set(ByVal value As String)
         FAddress = value
      End Set
   End Property

   Private FCity As String
   Public Property City() As String
      Get
         Return FCity
      End Get
      Set(ByVal value As String)
         FCity = value
      End Set
   End Property

   Private FRegion As String

   Public Property Region() As String
      Get
         Return FRegion
      End Get
      Set(ByVal value As String)
         FRegion = value
      End Set
   End Property


   Private FPostalCode As String
   Public Property PostalCode() As String
      Get
         Return FPostalCode
      End Get
      Set(ByVal value As String)
         FPostalCode = value
      End Set
   End Property

   Private FCountry As String
   Public Property Country() As String
      Get
         Return FCountry
      End Get
      Set(ByVal value As String)
         FCountry = value
      End Set
   End Property

   Private FPhone As String
   Public Property Phone() As String
      Get
         Return FPhone
      End Get
      Set(ByVal value As String)
         FPhone = value
      End Set
   End Property

   Private FFax As String
   Public Property Fax() As String
      Get
         Return FFax
      End Get
      Set(ByVal value As String)
         FFax = value
      End Set
   End Property

   Public Sub New()

   End Sub


   Public Sub New(ByVal customerID As String, ByVal companyName _
      As String, _
   ByVal contactName As String, ByVal contactTitle As String, _
      ByVal address As String, _
   ByVal city As String, ByVal region As String, _
      ByVal postalCode As String, _
   ByVal country As String, ByVal phone As String, ByVal fax As String)

      Me.FCustomerID = customerID
      Me.FCompanyName = companyName
      Me.FContactName = contactName
      Me.FContactTitle = contactTitle
      Me.FAddress = address
      Me.FCity = city
      Me.FRegion = region
      Me.FPostalCode = postalCode
      Me.FCountry = country
      Me.FPhone = phone
   End Sub

End Class

The DAL refers to the business object layer and not the other way around. This means that business objects don't need to carry any database knowledge (or baggage) around with them.

By using generic Lists—List(Of T)—you don't need to write custom collections. Generic lists are enumerable and bindable. That said, I often inherit from the generic List—for example, List(Of Customer)—to add additional behaviors to my custom business object collections.

Flexible, Useful Data Access Layer

The data access code shown in this article has proven to be the most flexible and useful DAL I have created to date. It significantly reduces DAL errors for improper connection usage; it separates and speeds up development of the persistence layer of implementation; and it removes a lot of code from business objects. Of course, you can tell that it requires newer technologies such as generics and reflection, but even earlier versions of .NET supported this style of implementation—just use object types instead of generics.

I hope you find the solution useful. Many of you wrote to asked me to write this article, and I suspect some of you may discover some limitations to my solution. Send these to me as well. I am looking forward to your feedback.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his new book UML DeMystified from McGraw-Hill/Osborne. Paul is a software architect for Tri-State Hospital Supply Corporation. You may contact him for technology questions at pkimmel@softconcepts.com.

If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org.

Copyright © 2006 by Paul T. Kimmel.

Sitemap | Contact Us