CODEWITHSUNDEEP
c#sqltransactions
Dewald Swanepoel
Dewald Swanepoel

Reputation: 1681

Nested TransactionScope() won't roll back

I am trying to make use of nested transactions in C# for the first time. In the past I've always wrapped my SqlCommands inside SqlTransactions inside SqlConnections. Something like this:

using (SqlConnection myCon = new SqlConnection(...))    
    using (SqlTransaction myTran = myCon.BeginTransaction())
    {
        using (SqlCommand myCom1 = new SqlCommand("...", myCon, myTran))
        {
            ...
        }       
        using (SqlCommand myCom2 = new SqlCommand("...", myCon, myTran))
        {
            ...
        }
        .
        .
        .
        myTran.Commit();
    }
}

All of this, with the necessary try...catch handling of course so if an exception occurs anywhere inside the SqlTransaction, I know that none of the SqlCommands will be committed.

So I thought I'd give TransactionScope a try but it's not working. Here's what I'm doing; I have two transactions, one after the other, but both inside an outer transaction. Depending on which checkbox had been checked on the form, the code throws an exception either: 1. just before the first inner transaction's commit, or 2. between the two inner transactions, or 3. just before the second inner transaction's commit, or 4. just before the outer transaction's commit

The problem I'm getting is that, regardless of which checkbox I tick, the code executes as if no transactions were present. So in other words, the fact that I bug jump out of the try block because of an exception doesn't roll back any of the transactions.

Would appreciate some help. Below is my code for the little test application.

try
{
    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
    {
        sqlConnection.Open();

        using (TransactionScope transactionOuter = new TransactionScope())
        {
            using (TransactionScope transactionInner1 = new TransactionScope())
            {
                using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO BasicTable (Value) VALUES ('Inside Inner Transaction 1')", sqlConnection))
                {
                    sqlCommand.ExecuteNonQuery();
                }

                if (checkBox_FailInner1.Checked)
                    throw (new Exception("Failed inside inner transaction 1"));

                transactionInner1.Complete();
            }

            if (checkBox_FailBetween.Checked)
                throw (new Exception("Failed between inner transactions"));

            using (TransactionScope transactionInner2 = new TransactionScope())
            {
                using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO BasicTable (Value) VALUES ('Inside Inner Transaction 2')", sqlConnection))
                {
                    sqlCommand.ExecuteNonQuery();
                }

                if (checkBox_FailInnner2.Checked)
                    throw (new Exception("Failed inside inner transaction 2"));

                transactionInner2.Complete();
            }

            if (checkBox_FailOuter.Checked)
                throw (new Exception("Failed before outer transaction could commit"));

            transactionOuter.Complete();
        }
    }
}
catch (Exception exc)
{
    MessageBox.Show(exc.Message);
}

MessageBox.Show("Done");

Upvotes: 2

Views: 2503

Answers (1)

usr
usr

Reputation: 171178

A SqlConnection enlists itself when it is opened. Only this point in time matters. The connection will be enlisted into whatever transaction is active at that time. You are opening the connection before any transaction is installed.

I believe you have a 2nd misunderstanding: It is not possible to nest transactions. You can only nest scopes. When you rollback an inner scope, the whole transaction is rolled back. All a TransactionScope does is set and manipulate Transaction.Current. Nothing more. The outermost scope installs a transaction. The inner scopes are mostly no-ops. All they do is provide a way to doom the transaction by not completing them.

Upvotes: 3

Related Questions