Microsoft & .NETVisual BasicHandling Database Writes in Data Access Layer Management

Handling Database Writes in Data Access Layer Management

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

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

ALTER PROCEDURE dbo.PutCustomer
(
   @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,
)
AS
IF( NOT EXISTS (SELECT CustomerID FROM Customers
                WHERE CustomerID = @CustomerID) )
BEGIN
   /* insert */
   INSERT INTO Customers
   (
      CustomerID,
      CompanyName,
      ContactName,
      ContactTitle,
      Address,
      City,
      Region,
      PostalCode,
      Country,
      Phone,
      Fax)
   VALUES
   (
      @CustomerID,
      @CompanyName,
      @ContactName,
      @ContactTitle,
      @Address,
      @City,
      @Region,
      @PostalCode,
      @Country,
      @Phone,
      @Fax)
   END
   ELSE
   BEGIN
   /* update */
   UPDATE Customers
   SET
      CompanyName  = @CompanyName,
      ContactName  = @ContactName,
      ContactTitle = @ContactTitle,
      Address      = @Address,
      City         = @City,
      Region       = @Region,
      PostalCode   = @PostalCode,
      Country      = @Country,
      Phone        = @Phone,
      Fax          = @Fax
   WHERE
      CustomerID = @CustomerID
   END
   IF @@ERROR <> 0
   BEGIN
      RAISERROR('Something (%d) went wrong with %s', 16, 1, @@ERROR, _
                @CustomerID)
   END

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.”

Download the Code

Download the demo for this article.

Summary

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.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories