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
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.
Page 2 of 5