Does TransactionScope work with pre-existing connections?

Question

I have a code like this:

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        some_db_function();

        for (i = 0; i < 10; i++)
        {
            some_other_db_function();
        }

        scope.Complete();
    }
}
catch (Exception ex)
{
   MessageBox.Show(ex.Message + " all done transactions will rollback");   
}

and inside the db functions something like this happens:

private void some_db_functions()
{
    using (TransactionScope scope = new TransactionScope())
    {
       //some processing on db
       scope.Complete();
    }
}

It is supposed to be that if there was any problem in the database transactions, like an error inserting or updating in the functions; all the transactions that had been done so far get rolled back. But it does not work like that; and although it throws an exception and the scope.Complete() in the parent function never gets triggered, still nothing get rolled back.

Where is the problem?

Answer 1

If the open connection already exists it will not automatically enlist in an ambient transaction. You would have to set it explicitly.

Implicitly enlisting connections is not supported. To enlist in a transaction scope, you can do the following:

Open a connection in a transaction scope.

Or, if the connection is already opened, call EnlistTransaction method on the connection object.

Ref.

This will enlist an existing connection:

connection.EnlistTransaction(Transaction.Current)

Answer 2

IIRC, automatic enlisting into ambient transactions happens at connection creation/opening time; if you create the connection inside the scope of the transaction, all should be good. However:

they are all using the same connection, declared previously

if the connection exists outside of the transaction, it won't enlist.

Best practice is to create/open a connection only around a unit of work, not forever (and: let connection pooling do its job). If you follow that practice, it should work fine. So:

This won't work:

using(var conn = CreateAndOpenConnection()) {
    // ...
    using(var tran = new TransactionScope()) {
        SomeOperations(conn);
        tran.Complete();
    }
    // ...
}

where-as this should work:

using(var tran = new TransactionScope()) {
    // ...
    using(var conn = CreateAndOpenConnection()) {
        SomeOperations(conn);
    }
    tran.Complete();
    // ...
}