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) = _

      For Each c As Customer In customers

      ' Add a customer, modify a second customer, and save
      Dim newCustomer As Customer = New Customer( _
         "PAUKI", "Fatman Soda Company", "P. Kimmel", _
         "Boss", "", "", "", "", "", "", "")

      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
   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 = _
      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
   End If


   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 _

      For Each c As Customer In customers


   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( _


      ' 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, _

   End Using

End Sub

'ALTER PROCEDURE dbo.GetCustomersAndOrders
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
            End If
   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())
   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, _
   contactName = DataAccess.SafeRead(Of String)(contactName, reader, _
   contactTitle = DataAccess.SafeRead(Of String)(contactTitle, reader, _
   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, _
   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.

Handling Database Writes in Data Access Layer Management

Managing Transactions with TransactionsScope

The new System.Transactions.TransactionScope class in .NET 2.0 can simplify managing transactions. The TransactionScope class (see Listing 3, PutCustomers) enlists objects that support transactions automatically. This works through COM+ and the distributed transaction coordinator. Simply create the TransactionScope object in a Using statement to ensure its Dispose method is called, and then call TransactionScope.Complete if all of the code in the Using block runs to completion. Comment out the line scope.Complete in PutCustomers and you will see that the two writes—one update and one insert—aren't committed.

You might get an exception about the Distributed Transaction Coordinator (DTC) when you run the sample code or use TransactionScope. To start the DTC, click Start|Run, type services.msc, and click OK. This will run the Microsoft Management Console with the services module. Scroll to the Distributed Transaction Coordinator and start it. Or, you can enter net start msdtc from the command line.

For more information on the TransactionScope, read my previous article "The TransactionScope Object Makes ADO.NET Transactions Easy."

Writing Each Object

The PutCustomer method calls DataAccess.Write to write each object. As you might expect, PutCustomer "knows" which stored procedure to call and builds a parameter for each field in the Customer object. In the sample (see Listing 3), PutCustomer attempts to write every object. It is helpful to do the following:

  1. Try to write only objects that have changed by tracking changes in the objects themselves (not shown in the sample);
  2. Write stored procedures to compare fields against fields in the row; or
  3. Both

Each of the aforementioned techniques works equally well. In one application, I elected to permit the user to indicate which objects to try to persist; it was incumbent on the user to decide which objects to save. This technique worked as well.

Writing Compound Objects

Using transactions (the old way or with the TransactionScope) means that composite object writes are protected. Suppose you modify the Customer and Orders part of a Customer class containing a list of Order objects. All you would need to do is think of the PutCustomer method as an orchestrator. PutCustomer writes the Customer part of the object and OrderAccess.PutOrders (or PutOrder) writes the Order part of the object, all protected by the same transaction.

Implementing the Stored Procedure

The PutCustomer stored procedure is straightforward. I prefer fewer entry points to the database, so I typically create a Write or Put procedure that executes an insert or update depending on some aspect of the object's state. For example, if no primary key is present, an insert is invoked. If the row exists, then an update is invoked. Listing 4 shows a monolithic PutCustomer stored procedure. In practice, I prefer splitting out the update and insert parts and using the exec sproc command to call the insert or update behavior.

Listing 4: The Monolithic Put Procedure for Writes and Updates

   @CustomerID   nchar(5),
   @CompanyName  nvarchar(40),
   @ContactName  nvarchar(30) = null,
   @ContactTitle nvarchar(30) = null,
   @Address      nvarchar(60) = null,
   @City         nvarchar(15) = null,
   @Region       nvarchar(15) = null,
   @PostalCode   nvarchar(10) = null,
   @Country      nvarchar(15) = null,
   @Phone        nvarchar(24) = null,
   @Fax          nvarchar(24) = null,
                WHERE CustomerID = @CustomerID) )
   /* insert */
   INSERT INTO Customers
   /* update */
   UPDATE Customers
      CompanyName  = @CompanyName,
      ContactName  = @ContactName,
      ContactTitle = @ContactTitle,
      Address      = @Address,
      City         = @City,
      Region       = @Region,
      PostalCode   = @PostalCode,
      Country      = @Country,
      Phone        = @Phone,
      Fax          = @Fax
      CustomerID = @CustomerID

   IF @@ERROR <> 0
      RAISERROR('Something (%d) went wrong with %s', 16, 1, @@ERROR, _

There is nothing remarkable about the stored procedure (unless you are unfamiliar with stored procedures). In the example, if the CustomerID exists update; else, insert.

Handling Object Changes

To prevent writing unchanged objects, you can maintain a single internal property (for instance, changed) as a Boolean. In every public setter, set changed to true. If you want to maintain the original state of an object, duplicate each of the fields with an internal copy of the original value or use the Memento behavioral pattern. This way, you need write only objects whose changed state is true.

Managing Automatic Identity Fields and Return Values

The Northwind database's Customers table uses a manual string key. Some tables will use auto-generated fields, and sometimes you will want to read return values and output fields. For these times, define a WriteEventHandler. As you saw in Listing 2, the WriteEventHandler is called after the command is sent to the database so output and return values will be available in the parameters collection of the command object (see Listing 5).

Listing 5: The Definition of the WriteEventHandler

Public Delegate Sub WriteEventHandler(Of T)(ByVal o As T, _
   ByVal command As IDbCommand)

As you can see from the WriteEventHandler delegate, the object and the command are available. Because you implement the write method and the handler, you will know what values are returned by the database and how to assign them back to the object.

Validating Business Objects

In most cases, before you insert or update a row you will want to perform some validation. OOP purists may argue that classes should be self-aware and self-validating. To some extent this is a good idea, but in some ways it is impractical.

Consider the physical world. When you don't feel well sometimes you take a pill—don't suffer, take a pill—but sometimes the pill doesn't help. What do you do then? You go see a doctor. Mapping the analogy to the digital world, sometimes objects have to look outside themselves to validate. For example, suppose your company has a commissioned sales force. When you are defining a sales agreement, you may have to go to the database to determine whether a particular price for a particular product will yield a commission and how much that commission will be. It's impractical for the agreement object to store all this knowledge and capability.

I glibly refer to an approach I like for validating as the Cuisinart. Throw a bunch of data in the Cuisinart and see whether it passes whatever tests are needed. This approach keeps the entity objects smaller and facilitates sharing rules across entities. My favorite approach to validation to date is the Visitor behavior pattern with Verdict. Run the object or objects through the Visitor pattern Cuisinart and leave behind a Verdict object. This object can contain whatever it needs to contain to indicate what about the object needs to be corrected.

For more information on the Visitor pattern, see the Design Patterns book by Erich Gamma, et. al. You can find a great definition and example of the Visitor pattern on this page. I am pretty sure I didn't invent the with Verdict part, but I am not sure where the idea originated. (However, if it turns out I invented it, please send royalty checks to...)

Deleting Business Objects

You have to keep track of deleted objects. You also don't want these objects showing up in the primary collection (for example, collection of customers) when you bind that collection to a GridView or something. One way I handle this situation is to keep an internal list of deleted objects and remove those objects from the outer list. When you write the objects, you call a delete stored procedure for everything in the deleted list.

The key here is to define a new list that inherits from List(Of T)—for example, List(Of Customer)—and define the internal list of deleted objects. You also can use this custom list to add additional behaviors such as advanced searching or business logic.

Two Quick FYIs

Tracking deleted objects and performing writes in a group instead of one at a time is generally less problematic in Windows applications. The reason for this is that Windows is an always-connected environment. However, the DAL described in this article will work for a Windows or a Web environment.

Also, Microsoft is working on the data access impedance problem. They are designing ADO.NET 3.0, LINQ, and the ADO.NET Entity Framework to reconcile the impedance mismatch between relational databases and object models. To get the most out of the ADO.NET Entity Framework, you will need to learn LINQ. For more on that technology, check out my article "Introducing LINQ for Visual Basic."


This DAL works for any version of .NET. For pre-generics versions, change the event handlers to use object instead of generics and employ typecasting. For versions of .NET without the TransactionScope, use an IDbTransaction.

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.

Copyright © 2007 by Paul T. Kimmel. All Rights Reserved.



  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds