The Only Data Access Layer You'll Ever Need?

Wednesday Dec 20th 2006 by Paul Kimmel

Share:

A useful data access layer (DAL) makes managing persistence easier than writing CRUD behaviors all over your code. Learn to build a flexible, simple one that does just that with a relatively few lines of code.

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.

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.

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.

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.

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.