The Only Data Access Layer You'll Ever Need?, Page 2

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.