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.
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 firstname.lastname@example.org.
If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org.
Copyright © 2006 by Paul T. Kimmel.