How should I integrate SqlTransaction legacy code with TranactionScope?

Question

I have an existing application that uses a lot of SqlTranaction calls. The way the application is built we get a bunch of code that looks a bit like this (error handling etc removed for brevity):

Using transaction As SqlTransaction = Database.CreateSqlTransaction()
    If Not Me.FitnessSession.FitnessTestSessionID.HasValue Then
        Me.FitnessSession.Insert(transaction)
    Else
        Me.FitnessSession.Update(transaction)
    End If
    SaveTestScores(transaction)

    Database.CommitTransaction(transaction) 'This essentially just calls transaction.Commit()

 End Using

We are in the process of ditching out hand rolled DAL and moving over to Linq to SQL, but this will be incremental as we don't have the budget to simply ditch all our legacy code. As such we will have areas where we need to use both our old code and the Linq to SQL code, all in the same transaction. I believe this can be done like this:

Using scope As New TransactionScope()
Using transaction As SqlTransaction = Database.CreateSqlTransaction()
    If Not Me.FitnessSession.FitnessTestSessionID.HasValue Then
        Me.FitnessSession.Insert(transaction)
    Else
        Me.FitnessSession.Update(transaction)
    End If
    SaveTestScores(transaction)

    Database.CommitTransaction(transaction)

 End Using
 End Using

What I am unclear on is which commit to call, do I have to call scope.Complete() or Database.CommitTransaction(transaction)? Or is there some other option to link the two bits of code together?

Follow Up Question
Thanks @programming-hero that’s really helpful, what I am now unsure of is, when should I close sql connections? The code inside the DAL methods makes a check to see if it has been passed a transaction, if it has, then it uses the connection on that transaction and does not close the connection once it is complete, if no transaction is passed then a new connection is opened and closed for the operation. If I close a SqlConnection inside a TransactionScope does this cause a problem for the ambient transaction? I ask, because if I close a SqlConnection that has a SqlTransaction object the transaction is rolled back.

Answer 1

The purpose of the System.Transactions namespace is to allow you to control transactions from a single model, across your entire application. When using the transaction model provided by System.Transactions, you should avoid using any explicit transactions like SqlTransaction; mixing in explicit transactions will make your life considerably harder as you have to manually manage both models.

The great news for you: When you use ADO.NET components you get support for ambient transactions built-in. This means that you can remove all references to SqlTransaction and instances of passing transactions objects around, as the System.Transactions model is going to take care of everything for you.

The ambient transaction model works as a wrapper and coordinator for various other specific transactions. The ADO.NET components know to look for an ambient transaction and to set up their own specific transactions internally (like SqlTransaction). The System.Transactions components alert all enlisted transactions to the state of the "global" transaction the TransactionScope blocks manage.

Provided your code is executing within a TransactionScope block, there will be an ambient transaction available for transaction-aware components to take part in. For your data-access code, all interactions will occur within the same transaction, regardless of whether they are part of your own DAL or Linq to SQL components. All you need to know is that they will enlist in an ambient transaction whenever one is present.

To complete a TransactionScope block successfully, simply call TransactionScope.Complete() on the local instance at the end of the work. Do that for every block you work within and when the top-most block completes, the physical transactions will commit.

There is no need to call any other transaction-specific code, as they should all (behind-the-scenes) be coordinating with your System.Transaction.

With all this in mind, your code can look like this:

Using scope As New TransactionScope()

    If Not Me.FitnessSession.FitnessTestSessionID.HasValue Then
        Me.FitnessSession.Insert()
    Else
        Me.FitnessSession.Update()
    End If

    SaveTestScores()

    scope.Complete()

End Using

All references to database-specific transactions are gone. All operations will occur within the ambient transaction provided by the TransactionScope. In this block you could also make a direct call using a SqlCommand or use a Linq-to-SQL object without specifying any explicit transactions and they would participate in the ambient transaction.