.NET Tips and Tricks

Blog archive

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 at 1:51 PM


comments powered by Disqus
Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.