.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

  • Death of the Dev Machine?

    Here's a takeaway from this week's Ignite 2020 event: An advanced Azure cloud portends the death of the traditional, high-powered dev machine packed with computing, memory and storage components.

  • COVID-19 Is Ignite 2020's Elephant in the Room: 'Frankly, It Sucks'

    As in all things of our new reality, there was no escaping the drastic changes in routine caused by the COVID-19 pandemic during Microsoft's big Ignite 2020 developer/IT pro conference, this week shifted to an online-only event after drawing tens of thousands of in-person attendees in years past.

  • Visual Studio 2019 v16.8 Preview Update Adds Codespaces

    To coincide with the Microsoft Ignite 2020 IT pro/developer event, the Visual Studio dev team shipped a new update, Visual Studio 2019 v16.8 Preview 3.1, with the main attraction being support for cloud-hosted Codespaces, now in a limited beta.

  • Speed Lines Graphic

    New for Blazor: Azure Static Web Apps Support

    With Blazor taking the .NET web development world by storm, one of the first announcements during Microsoft's Ignite 2020 developer/IT event was its new support in Azure Static Web Apps.

  • Entity Framework Core 5 RC1 Is Feature Complete, Ready for Production

    The first release candidate for Entity Framework 5 -- Microsoft's object-database mapper for .NET -- has shipped with a go live license, ready for production.

Upcoming Events