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.
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:
- Try to write only objects that have changed by tracking changes in the objects themselves (not shown in the sample);
- Write stored procedures to compare fields against fields in the row; or
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.
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 firstname.lastname@example.org.
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.
Page 2 of 2