Introduction

New software can be evolutionary or revolutionary. The problem with revolutionary software is that sometimes people aren't ready for the revolutionary. So, in general most software plods along with relatively simple changes. Whether there is a long term design or plan, it is hard to say. It is highly probable though that in the presence of such a plan, plans change, and it is easier to change things in small ways over time.

Database technologies seem to be moving along at the evolutionary pace of a glacier. Think about it: In many ways we are still using basically 20 years old—at least—relational database technology. Revolutionary object oriented databases were invented but like a wet band aid they didn't hold.

This article is about a very modest, evolutionary change to database technologies vis-á-vis .NET, the TransactionScope. The TransactionScope makes it easier to use transactions with your database, reducing your lines-of-code workload, thus helping ensure a greater database consistency. I will show you how to use TransactionScope in this article.

Even better news is that changes to ADO.NET like TransactionScope, new technologies like LINQ (Language Integrated Query), and the upcoming ADO.NET Entity Framework may all prove to be part of something revolutionary, something that might just stick.

Employ Using Statement

The Using statement sets up an implicit try..finally block without you having to actually write the try..finally statements. Try and finally are a part of exception management that ensures that finite resources like database access are properly cleaned up.

The TransactionScope class is defined in the System.Transactions namespace. When you combine using and TransactionScope the .NET framework will enlist the block of code and any called methods in an available transaction. If an exception occurs, the transaction will ensure changes are rolled back. If everything goes okay, call Complete on the TransactionScope object and the transaction is committed implicitly.

Using TransactionScope

The basic model for using database transactions is to create a connection and a transaction. Every command that is part of the transaction is initialized with the transaction object. The upshot is that the older way required several more lines of code than using the TransactionScope does.

To use TransactionScope, add an Imports statement for System.Transaction. Use the new Using statement in VB and create the TransactionScope object. In the Using block, create the connection, execute the SQL write (insert, update, or delete), and call Transactionscope.Complete. The framework will take care of enlisting in existing transactions or creating one and figuring out whether to commit or rollback. Listing 1 shows an example.

Listing 1: Just a couple of lines of code enlists your code in a protected transaction.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions

Module Module1

  Const CONNECTION_STRING As String = _
      "Data Source=localhost;Initial Catalog=Northwind;" + _
      "Integrated Security=True"

    Sub Main()
      Dim SQL As String = _
        "insert into customers(CustomerID, CompanyName, ContactName, ContactTitle," + _
        "Address, city, Region, PostalCode, Country, Phone, Fax) values (" + _
        "'FATMA', 'Kimmel', 'Paul Kimmel', 'The Fatman', '', '', '', '', 'USA', '', '')"

      Using Scope As TransactionScope = New TransactionScope()
        Using connection As SqlConnection = New SqlConnection(CONNECTION_STRING)
          connection.Open()
          Dim command As SqlCommand = New SqlCommand(SQL, connection)
          command.ExecuteNonQuery()
          Scope.Complete()
        End Using
      End Using
    End Sub

End Module

Tip: Remember to add a reference to the System.Transactions.dll assembly.

If you are using SQL Server pre-SQL Server 2005, and you receive an error such as the image on the right (click for large version) you may need to Start|Run services.smc and start the Distributed Transaction Coordinator.

I have used Reflector and Anakrino to examine how TransactionScope works. Unfortunately I don't have enough information to know "precisely" how it works. Here is my approximate guess.

Since TransactionScope will likely be used for database transactions initially but isn't limited to them, I suspect COM+ is involved. At some low-level, TransactionScope figures out what it is talking to via COM+ and queries the thing. Hey, thing, do you support transactions? If so, a transaction is started or attached to and the rest looks like magic.

You can also look at the TransactionScopeOption to vary how the transaction is enlisted. If you are interested in the plumbing, and there is a lot of plumbing, then download Lutz Roeder's Reflector (http://www.aisto.com/roeder/dotnet/) and decompile the System.Transactions assembly. Begin at the TransactionScope.Dispose method. (Pack a lunch.)

Summary

TransactionScope is a general purpose tool for enlisting blocks of code in transactions. It isn't just for database transactions but it does make using database transactions a little easier, and things that are easy are more likely to get done. (Don't blame me; I am just a realist.)

Of greater import is that database technology is getting a lot more attention of late. This is good because the technology, although reliable, is getting long in the tooth. Start exploring LINQ (Language INtegrated Query, at http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx) and check out the ADO.NET Entity Framework.

Paul Kimmel is an architect for TriState Hospital Supply Corporation and the co-founder of the Greater Lansing Area .NET Users Group (glugnet.org). Look for his recent book UML DeMystified (McGraw-Hill/Osborne, October 2005) and his shortcut on LINQ to be published in PDF form on infromIT.com (early 2007). If you have a programming question you can contact him at pkimmel@softconcepts.com. If you are interested in joining a .NET Users Group in the Lansing, Michigan area contact pkimmel@softconcepts.com. For other areas contact INETA, http://www.ineta.org.

Discuss Managing Database Transactions with the TransactionScope   >>> Be the FIRST to comment on this article!

>>> More Languages Articles          >>> More By Paul Kimmel