TransactionScope not rolling back transaction

Question

Here is the current architecture of my transaction scope source code. The third insert throws an .NET exception (Not a SQL Exception) and it is not rolling back the two previous insert statements. What I am doing wrong?

EDIT: I removed the try/catch from insert2 and insert3. I also removed the exception handling utility from the insert1 try/catch and put "throw ex". It still does not rollback the transaction.

EDIT 2: I added the try/catch back on the Insert3 method and just put a "throw" in the catch statement. It still does not rollback the transaction.

UPDATE:Based on the feedback I received, the "SqlHelper" class is using the SqlConnection object to establish a connection to the database, then creates a SqlCommand object, set the CommandType property to "StoredProcedure" and calls the ExecuteNonQuery method of the SqlCommand.

I also did not add Transaction Binding=Explicit Unbind to the current connection string. I will add that during my next test.

public void InsertStuff()
{
    try
    {
        using(TransactionScope ts = new TransactionScope())
        {
            //perform insert 1
            using(SqlHelper sh = new SqlHelper())
            {
                SqlParameter[] sp = { /* create parameters for first insert */ };

                sh.Insert("MyInsert1", sp);
            }

            //perform insert 2
            this.Insert2();

            //perform insert 3 - breaks here!!!!!
            this.Insert3();

            ts.Complete();            
        }
    }
    catch(Exception ex)
    {
        throw ex;
    }
}

public void Insert2()
{
    //perform insert 2
    using(SqlHelper sh = new SqlHelper())
    {
        SqlParameter[] sp = { /* create parameters for second insert */ };

        sh.Insert("MyInsert2", sp);
    }
}

public void Insert3()
{
    //perform insert 3
    using(SqlHelper sh = new SqlHelper())
    {
        SqlParameter[] sp = { /*create parameters for third insert */ };

        sh.Insert("MyInsert3", sp);
    }
}

Answer 1

It looks like you are catching the exception in Insert3() so your code continues after the call. If you want it to rollback you'll need to let the exception bubble up to the try/catch block in the main routine so that the ts.Complete() statement never gets called.

Answer 2

An implicit rollback will only occur if the using is exited without calling ts.complete. Because you are handling the exception in Insert3() the exception never causes an the using statement to exit.

Either rethrow the exception or notify the caller that a rollback is needed (make change the signature of Insert3() to bool Insert3()?)

Answer 3

Your example is not complete enough to answer the question. You need to show us what SqlHelper is doing (both the constructor and Insert method).

Answer 4

(based on the edited version that doesn't swallow exceptions)

How long do the operations take? If any of them are very long running, it is possible that the Transaction Binding bug feature has bitten you - i.e. the connection has become detached. Try adding Transaction Binding=Explicit Unbind to the connection string.

Answer 5

I have also run into a similar issue. My problem occurred because the SqlConnection I used in my SqlCommands was already open before the TransactionScope was created, so it never got enlisted in the TransactionScope as a transaction.

Is it possible that the SqlHelper class is reusing an instance of SqlConnection that is open before you enter your TransactionScope block?