Alex Rivera | Logout

TransactionScope not rolling back transaction

Asked 2008-12-04T00:42:27.050
13

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 par
Edit
Report

1 Answer

1

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()?)

answered 2008-12-04T00:51:56.267

Your Answer