Handling Database Writes in Data Access Layer Management

My first article on this topic demonstrated database reads-to-entities. Reads are relatively easy. Just read one or more result sets and build objects; you don't need transactions. This article demonstrates database writes. The write part of managing a data access layer (DAL) is where things can go awry. Writing requires managing changes, validation, transactions, and a variety of changes, including new, changed, and deleted objects.

This article demonstrates how to add and modify objects in a collection and then, using the basic DAL introduced in my previous article, how to manage writing those changes back to the persistence store. Although the code sample implicitly demonstrates insert and update behaviors, the text provides hints for handling validation and deletion too.

Modifying the Custom Objects

This example uses a simple console application to modify objects. (Listing 1 shows the code for the demo.) The beauty of a custom objects approach is that literally any kind of client can be used to modify the objects; they are just classes. Custom business objects also are lightweight and easy to use, and they behave in a persistence-layer-agnostic way.

Listing 1: Custom Business Objects Demo

Imports MYVB.BusinessObjects
Imports MYVB.DataAccess

Module Module1
   Sub Main()
      Dim customers As List(Of Customer) = _
         CustomerAccess.GetCustomers()
      For Each c As Customer In customers
         Console.WriteLine(c.ContactName)
      Next
      ' Add a customer, modify a second customer, and save
      Dim newCustomer As Customer = New Customer( _
         "PAUKI", "Fatman Soda Company", "P. Kimmel", _
         "Boss", "", "", "", "", "", "", "")
      customers.Add(newCustomer)
      customers(0).ContactName = "Paul Kimmel"
      ' Trying to write all is terribly inefficient
      ' You could A) track changes or B) insert only those you
      ' know are new or have changed
      CustomerAccess.PutCustomers(customers)
      Console.ReadLine()
   End Sub
End Module

As you can see, you can modify the collection and objects in any way desired. All of the writing is done at the collection level. However, you could easily write one object at a time, manage deletes, or incorporate change management logic to promote writing only objects that have changed.

Implementing the Write Behavior

For the CustomerAccess class to work with the basic DAL, you simply need to orchestrate calling the basic DataAccess class's Write method (see Listing 2). The DataAccess.Write method needs to know the stored procedure to call (although you could include a literal SQL overloaded method), what kind of object it is writing, a write event handler, whether anything comes back from the write, and the list of parameters and values to send to the stored procedure.

Listing 2: Both DataAccess.Write Methods

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
   command.ExecuteNonQuery()
   If (handler <> Nothing) Then
      handler(o, command)
   End If
End Sub

The first version of Write creates a connection for you, and the second version permits passing in both a connection and a transaction.

Because the basic DataAccess class manages just the general behavior, you do need to extend the specific entity data-access classes to indicate the objects to act on and how to act on them. For example, writing a Customer means you have to tell the DataAccess class about Customer stored procedures and Customer fields. The complete listing for the CustomerAccess class is shown in Listing 3, and the remaining sub-sections describe each of the new elements.

Listing 3: The Complete Implementation of the CustomerAccess Class

Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Configuration
Imports System.Transactions
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)
End Function
Public Shared Sub PutCustomers(ByVal customers As List(Of Customer))
   ' Reference and import System.Transactions
   Using scope As TransactionScope = New _
      TransactionScope(TransactionScopeOption.Required)
      For Each c As Customer In customers
         PutCustomer(c)
      Next
      scope.Complete()
   End Using
End Sub

' what if unique key is auto. Use write handler to set after insert
' change stored proedure to insert on null key
' key becomes output parameters
Public Shared Sub PutCustomer(ByVal cust As Customer)
   Debug.Assert(cust Is Nothing = False)
   Dim Factory As DbFactory = DbFactory.CreateFactory()
   Using connection As IDbConnection = Factory.CreateConnection( _
      Factory.ConnectionString)
      connection.Open()
      ' build parameters and write
      Dim customerID As IDbDataParameter = _
      Factory.CreateParameter("@CustomerID", cust.CustomerID)
      customerID.DbType    = DbType.String
      customerID.Size      = 5
      customerID.Direction = ParameterDirection.Input
      Dim companyName As IDbDataParameter = _
      Factory.CreateParameter("@CompanyName", cust.CompanyName)
      companyName.DbType    = DbType.String
      companyName.Size      = 40
      companyName.Direction = ParameterDirection.Input
      Dim contactName As IDbDataParameter = _
      Factory.CreateParameter("@ContactName", cust.ContactName)
      contactName.DbType    = DbType.String
      contactName.Size      = 30
      contactName.Direction = ParameterDirection.Input
      Dim contactTitle As IDbDataParameter = _
      Factory.CreateParameter("@ContactTitle", cust.ContactTitle)
      contactTitle.DbType    = DbType.String
      contactTitle.Size      = 30
      contactTitle.Direction = ParameterDirection.Input
      Dim address As IDbDataParameter = _
      Factory.CreateParameter("@Address", cust.Address)
      address.DbType    = DbType.String
      address.Size      = 60
      address.Direction = ParameterDirection.Input
      Dim city As IDbDataParameter = _
      Factory.CreateParameter("@City", cust.City)
      city.DbType    = DbType.String
      city.Size      = 15
      city.Direction = ParameterDirection.Input
      Dim region As IDbDataParameter = _
      Factory.CreateParameter("@Region", cust.Region)
      region.DbType    = DbType.String
      region.Size      = 15
      region.Direction = ParameterDirection.Input
      Dim postalCode As IDbDataParameter = _
      Factory.CreateParameter("@PostalCode", cust.PostalCode)
      postalCode.DbType    = DbType.String
      postalCode.Size      = 10
      postalCode.Direction = ParameterDirection.Input
      Dim country As IDbDataParameter = _p>
      Factory.CreateParameter("@Country", cust.Country)
      country.DbType    = DbType.String
      country.Size      = 15
      country.Direction = ParameterDirection.Input
      Dim As IDbDataParameter = _
      Factory.CreateParameter("@Phone", cust.Phone)
      phone.DbType    = DbType.String
      phone.Size      = 24
      phone.Direction = ParameterDirection.Input
      Dim fax As IDbDataParameter = _
      Factory.CreateParameter("@Fax", cust.Fax)
      fax.DbType    = DbType.String
      fax.Size      = 24
      fax.Direction = ParameterDirection.Input
      DataAccess.Write(Of Customer)(cust, _
         "PutCustomer", Nothing, connection, Nothing, _
         customerID, _
         companyName, _
         contactName, _
         contactTitle, _
         address, _
         city, _
         region, _
         postalCode, _
         country, _
         phone, _
         fax)
   End Using

End Sub
'ALTER PROCEDURE dbo.GetCustomersAndOrders
'AS
'SELECT * FROM CUSTOMERS
* FROM ORDERS
Public Shared Function GetCustomersWithOrders() As List(Of Customer)
   Return DataAccess.Read(Of List(Of Customer)) _
      ("GetCustomersAndOrders", _
      AddressOf OnReadCustomersWithOrders)
End Function
Public Shared Function OnReadCustomersWithOrders( _
   ByVal reader As IDataReader) As List(Of Customer)
   Debug.Assert(reader Is Nothing = False)
   Dim customers As List(Of Customer) = New List(Of Customer)()
   If (reader Is Nothing) Then Return customers
   customers = OnReadCustomers(reader)
   If (reader.NextResult()) Then
      Dim allOrders As List(Of Orders) = _
         DataAccess.OnReadAnyList(Of Orders)(reader)
      Dim customer As Customer
      Dim _order As Orders
      For Each customer In customers
         For Each _order In allOrders
            If (_order.CustomerID = customer.CustomerID) Then
               customer.CustomerOrders.Add(_order)
            End If
         Next
      Next
   End If
   Return customers
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

Although this code is straightforward and monolithic, its consistency across entities in any domain promotes code generation. At a minimum, a tool like CodeRush (when it finally offers a VB version) will make writing this code a breeze.