.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

Featured

  • Entity Framework Core 6: What Developers Want

    Microsoft outlined its plan for Entity Framework Core 6, which in November will take its place as the data access component of the landmark .NET 6, a long-term support (LTS) release that will mark Microsoft's transition from the Windows-only .NET Framework to an open source, cross-platform umbrella offering of all things .NET.

  • AWS Open Sources .NET Porting Assistant GUI

    After previously open sourcing components of its Porting Assistant for .NET, Amazon Web Services open sourced the tool's GUI.

  • .NET Core Ranks High Among Frameworks in New Dev Survey

    .NET Core placed high in a web-dominated ranking of development frameworks published by CodinGame, which provides a tech hiring platform.

  • Here's a One-Stop Shop for .NET 5 Improvements

    Culled from reams of Microsoft documentation, here's a high-level summary of what's new for performance, networking, diagnostics and more, along with links to the nitty-gritty details for those wanting to dig in more.

Upcoming Events