TransactionScope And Function Scope- Are These Connections In-Scope?

Question

Suppose you set up a TransactionScope object as illustrated per the Microsoft example here. Now suppose that you need to update a lot of database tables, and you want them all in the scope of the TransactionScope object. Continually nesting SqlConnection and SqlCommand objects 10 deep will create a source code mess. If instead you call other functions which create connections (in your data access layer, for example), will they be within scope of the TransactionScope object?

Example:

' Assume variable "x" is a business object declared and populated with data.
Using scope As New TransactionScope()

    Dal.Foo.SaveProducts(x.Products)
    Dal.Foo.SaveCustomer(x.Customer)
    Dal.Foo.SaveDetails(x.Details) 
    ' more DAL calls ...
    Dal.Foo.SaveSomethingElse(x.SomethingElse)

    scope.Complete()

End Using

Assume that each DAL function contains its own using statements for connections. Example:

Public Shared Sub SaveProducts(x As Object)

    Using conn As New SqlConnection("connection string")

        Using cmd As New SqlCommand("stored procedure name", conn)

            With cmd

                ' etc.                        

            End With

        End Using

    End Using

End Sub

Answer 1

Yes, they will be inside the TransactionScope. What the TransactionScope basically does is to create a Transaction object and set Transaction.Current to that.

In other words, this:

Using scope As New TransactionScope()
    ... blah blah blah ...
End Using

is basically the same as this:

try
{
    // Transaction.Current is a thread-static field
    Transaction.Current = new CommittableTransaction();
    ... blah blah blah ...
}
finally
{
    Transaction.Current.Commit(); // or Rollback(), depending on whether the scope was completed
    Transaction.Current = null;
}

When a SqlConnection is opened, it checks if Transaction.Current (on this thread) is null or not, and if it is not null then it enlists (unless enlist=false in the connection string). So this means that SqlConnection.Open() doesn't know or care if the TransactionScope was opened in this method or a method that called this one.

(Note that if you wanted the SqlConnection in the child methods to NOT be in a transaction, you can make an inner TransactionScope with TransactionScopeOption.Suppress)