Simplify Your Code with TransactionScope
In an earlier column, I referenced using TransactionScope instead of the ADO.NET Transaction object. The problem with an ADO.NET Transaction object is that it must be associated with each ADO.NET Command object that's involved in the transaction. In addition, all of those Command objects must use the same Connection object. If those Command objects are spread over multiple methods, then you end up having to pass the Transaction object to each method. And, unless you've declared your Connection object globally (not a great idea), you'll also have to pass the Connection to those methods.
For example, you end up writing code like this:
Try
Dim trans As SqlTransaction
Dim cn As new SqlConnection("…")
Dim cmdNewClaim As SqlCommand = New SqlCommand("…")
cmdNewClaim.CommandType = CommandType.StoredProcedure
cmdNewClaim.Connection = cn
cmdNewClaim.Transaction = trans
cmdNewClaim.ExecuteNonQuery()
GetRecordOwner(processableInvoices.First.RecordOwner, cn, trans)
trans.Commit
Catch Ex As Exception
trans.Rollback
End Try
Using TransactionScope simplifies this code tremendously by enclosing every ADO.NET call within a transaction -- even when the call is inside another method. You need to add a reference to System.Transactions and include an Imports/Using statement for System.Transactions. Wrapping the previous code in TranscationSocpe object declared in a using block eliminates some lines of code and simplifies the call to the enclosed method. You don't need to include a rollback call because if the code reaches the end of the Using block without a call to Complete, your transaction is automatically rolled back:
Using trns As TransactionScope = New TransactionScope
Try
Dim cn As new SqlConnection("…")
Dim cmdNewClaim As SqlCommand = New SqlCommand("…")
cmdNewClaim.CommandType = CommandType.StoredProcedure
cmdNewClaim.Connection = cn
cmdNewClaim.ExecuteNonQuery()
GetRecordOwner(processableInvoices.First.RecordOwner)
trns.Complete
End Using
As an extra benefit, if the enclosed method uses a different connection, the connection should be automatically elevated to a distributed transaction.
Posted by Peter Vogel on 11/03/2014