Using Transactions

The nature of a relational database is that the database designer tries to reconcile a reduction in redundancy and waste with an increase in complexity. This reconciliation is referred to in database jargon as normalization . The trade-off of normalization is the complexity introduced and the hoops we have to jump through to reassemble disparate tables into usable chunks . For example, from a programmer's perspective, orders and order details are related , but a reasonable database design would separate these pieces of information to avoid repeating order information for every detail item in the order. Then when the user wants to see a complete order, the order and detail information must be reassembled. Additionally, when operations need to be performed on an order, the programmer must ensure that an entire operation, which may span several tables, completes successfully. For instance it is usually considered unacceptable to bill a customer for an order but not deliver the ordered items. When we have operations that must be treated as a whole, we can protect them with transactions.

A transaction is the notion of treating a group of database interactions as one interaction. For example, when a customer orders some product, it is considered vital that all aspects of the order (including the items ordered, billing information, shipping, and any subsequent promises) be delivered. Such a promise may technically translate to adding data to the repository for the order, billing, and inventory information.

Listing 12.9 contains an excerpt from TransactionDemo.sln . The example demonstrates how complex database behavior can be as well as how to use a transaction object. The specific example in a production database would be better served if constraints between the tables managed the deletion of child tables, or at least if the code were converted to a stored procedure. Technically, however, the solution in Listing 12.9 functions well enough.

Listing 12.9 Executing Multiple Queries as Part of a Single Transaction

 1:  Private Sub DeleteCustomer(ByVal CustomerID As String) 2:    If (Warning()) Then Exit Sub 3: 4:    Const DeleteCustomers As String = _ 5:      "DELETE * FROM CUSTOMERS WHERE CustomerID = '{0}'" 6:    Const DeleteOrders As String = _ 7:      "DELETE * FROM ORDERS WHERE CustomerID = '{0}'" 8:    Const DeleteOrderDetails As String = _ 9:      "DELETE * FROM [Order Details] WHERE OrderID = '{0}'" 10:   Const SelectOrders As String = _ 11:     "SELECT OrderID FROM Orders WHERE CustomerID = '{0}'" 12: 13:   Dim Transaction As OleDbTransaction 14:   Dim Command As OleDbCommand 15:   Connection.Open() 16:   Try 17:     Transaction = Connection.BeginTransaction() 18: 19:     Command = New OleDbCommand( _ 20:       String.Format(SelectOrders, CustomerID), _ 21:       Connection, Transaction) 22: 23:     Dim Reader As OleDbDataReader = Command.ExecuteReader() 24:     While (Reader.Read()) 25:       Command = New OleDbCommand( _ 26:         String.Format(DeleteOrderDetails, _ 27:         CType(Reader("OrderID"), String)), _ 28:         Connection, Transaction) 29:     End While 30: 31:     Reader.Close() 32: 33:     Command = New OleDbCommand( _ 34:       String.Format(DeleteOrders, CustomerID), _ 35:       Connection, Transaction) 36: 37:     Command.ExecuteNonQuery() 38: 39:     'Throw New Exception("Intentionally cause rollback") 40: 41:     Command = New OleDbCommand( _ 42:       String.Format(DeleteCustomers, CustomerID), _ 43:       Connection, Transaction) 44:     Command.ExecuteNonQuery() 45: 46:     Transaction.Commit() 47:   Catch 48:     Transaction.Rollback() 49:     Throw 50:   Finally 51:     Connection.Close() 52:   End Try 53: 54: End Sub 

Listing 12.9 assumes that we are provided with a valid CustomerID . Line 2 is just a warning in the sample program reminding you to back up the database ( NWIND.mdb ) if you want to be able to restore it to its original condition after experimenting with this code. Lines 4 through 11 define some constant, parameterized SQL text that is used to complete the entire transaction. The first through third statements in that block delete the Customers , Orders , and Order Details rows, respectively. The fourth statement is used to get all the OrderID values.

The OleDbTransaction and OleDbCommand objects are declared in lines 13 and 14. These have to be declared outside of the Try . . . Catch . . . Finally . . . End Try block because each part of the block is a distinct scope. For example, if we defined the OleDbTransaction object in the Try part, it would not be accessible in the Finally or Catch parts of the exception handler.

The connection is opened in line 15. The DeleteCustomers method relies on the connection already being created and initialized . The Try part of the exception handler begins in line 16. The basic flow of the code in the Try part is to start the transaction (line 17), get all the OrderID values, and use those to delete the Order Details rows for this customer. Following the deletion of Order Details rows, rows for the Orders and Customers tables are deleted based on CustomerID .

Lines 23 through 29 show how to use OleDbDataReader to read all the OrderID values from the Orders table, using those identifiers to delete the Order Details rows. Remember to close the reader when finished with it (line 31).

Finally, if we execute all the commands and reach line 46, the transaction (that is, all changes following the time we began the transaction to this point) is committed to the database. If an error has occurred, the transaction (all changes) is rolled back, or undone. In line 49 we call Throw to rethrow the exception to the caller. The Finally block of code is always run, which is precisely what we want ”we always want the connection to close at the end.

This code works correctly. However, I added the commented out Throw statement in line 39 to facilitate experimentation with the rollback behavior.

This listing presents some techniques I hope you can use in your own code. The first is in line 39. Line 39 demonstrates how to raise an exception. This is the preferred way of indicating an error in code that you write, and it mirrors what the framework does when it encounters an error. You can create and throw an instance of any exception, including custom exceptions. Another technique is using a DataReader object for forward-only data access. I also wanted to show you the rhythm of a Try . . . Catch . . . Finally . . . End Try block. It is harder to make mistakes in VB .NET than in some other languages, but mistakes will happen. You can use exception handlers to make sure things like database connections get cleaned up, and you can rethrow exceptions (line 49), allowing the database code to handle the database problem and some other chunk of code to handle error processing.

A final note: If you find yourself writing complex database interactions like the one shown in Listing 12.9, think "stored procedure." Complex transactions spanning multiple entities in a database are ideally suited for stored procedures.