August 29, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

The Only Data Access Layer You'll Ever Need?

  • December 20, 2006
  • By Paul Kimmel
  • Send Email »
  • More Articles »

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:

  1. Move data from a read event handler
  2. Grab items like primary keys for inserts in a writer event handler
  3. 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).





Page 1 of 5



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel