CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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:
- Move data from a read event handler
- Grab items like primary keys for inserts in a writer event handler
- 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.
