TransactionScope not rolling back transaction

Michael Kniskern picture Michael Kniskern · Dec 4, 2008 · Viewed 17.1k times · Source

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

John Allers picture John Allers · Mar 5, 2009

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?