Cannot access SqlTransaction object to rollback in catch block

Question

I've got a problem, and all articles or examples I found seem to not care about it.

I want to do some database actions in a transaction. What I want to do is very similar to most examples:

using (SqlConnection Conn = new SqlConnection(_ConnectionString))
{
    try
    {
        Conn.Open();
        SqlTransaction Trans = Conn.BeginTransaction();

        using (SqlCommand Com = new SqlCommand(ComText, Conn))
        {
            /* DB work */
        }
    }
    catch (Exception Ex)
    {
        Trans.Rollback();
        return -1;
    }
}

But the problem is that the SqlTransaction Trans is declared inside the try block. So it is not accessable in the catch() block. Most examples just do Conn.Open() and Conn.BeginTransaction() before the try block, but I think that's a bit risky, since both can throw multiple exceptions.

Am I wrong, or do most people just ignore this risk? What's the best solution to be able to rollback, if an exception happens?

Solution 1

using (var Conn = new SqlConnection(_ConnectionString))
{
    SqlTransaction trans = null;
    try
    {
        Conn.Open();
        trans = Conn.BeginTransaction();

        using (SqlCommand Com = new SqlCommand(ComText, Conn, trans))
        {
            /* DB work */
        }
        trans.Commit();
    }
    catch (Exception Ex)
    {
        if (trans != null) trans.Rollback();
        return -1;
    }
}

or you could go even cleaner and easier and use this:

using (var Conn = new SqlConnection(_ConnectionString))
{
    try
    {
        Conn.Open();
        using (var ts = new System.Transactions.TransactionScope())
        {
            using (SqlCommand Com = new SqlCommand(ComText, Conn))
            {
                /* DB work */
            }
            ts.Complete();
        }
    }
    catch (Exception Ex)
    {     
        return -1;
    }
}

Solution 2

I don't like typing types and setting variables to null, so:

try
{
    using (var conn = new SqlConnection(/* connection string or whatever */))
    {
        conn.Open();

        using (var trans = conn.BeginTransaction())
        {
            try
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    /* setup command type, text */
                    /* execute command */
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                /* log exception and the fact that rollback succeeded */
            }
        }
    }
}
catch (Exception ex)
{
    /* log or whatever */
}

And if you wanted to switch to MySql or another provider, you'd only have to modify 1 line.

Solution 3

use this

using (SqlConnection Conn = new SqlConnection(_ConnectionString))
{
    SqlTransaction Trans = null;
    try
    {
        Conn.Open();
        Trans = Conn.BeginTransaction();

        using (SqlCommand Com = new SqlCommand(ComText, Conn))
        {
            /* DB work */
        }
    }
    catch (Exception Ex)
    {
        if (Trans != null)
            Trans.Rollback();
        return -1;
    }
}

BTW - You did not commit it in case of successful processing

Solution 4

using (SqlConnection Conn = new SqlConnection(_ConnectionString))
{
    try
    {
        Conn.Open();
        SqlTransaction Trans = Conn.BeginTransaction();

        try 
        {
            using (SqlCommand Com = new SqlCommand(ComText, Conn))
            {
                /* DB work */
            }
        }
        catch (Exception TransEx)
        {
            Trans.Rollback();
            return -1;
        }
    }
    catch (Exception Ex)
    {
        return -1;
    }
}

Solution 5

When I found this question the first time end of 2018 I didn't thought there could be a bug in the then top voted answer, but there it goes. I first thought about simply commenting the answer but then again I wanted to back up my claim with my own references. And tests I did (based on .Net Framework 4.6.1 and .Net Core 2.1.)

Given the OP's constraint, the transaction should be declared within the connection which leaves us to the 2 different implementations already mentioned in other answers:

Using TransactionScope

using (SqlConnection conn = new SqlConnection(conn2))
{
    try
    {
        conn.Open();
        using (TransactionScope ts = new TransactionScope())
        {
            conn.EnlistTransaction(Transaction.Current);
            using (SqlCommand command = new SqlCommand(query, conn))
            {
                command.ExecuteNonQuery();
                //TESTING: throw new System.InvalidOperationException("Something bad happened.");
            }
            ts.Complete();
        }
    }
    catch (Exception)
    {
        throw;
    }
}

Using SqlTransaction

using (SqlConnection conn = new SqlConnection(conn3))
{
    try
    {
        conn.Open();
        using (SqlTransaction ts = conn.BeginTransaction())
        {
            using (SqlCommand command = new SqlCommand(query, conn, ts))
            {
                command.ExecuteNonQuery();
                //TESTING: throw new System.InvalidOperationException("Something bad happened.");
            }
            ts.Commit();
        }
    }
    catch (Exception)
    {
        throw;
    }
}

You should be aware that when declaring a TransactionScope within a SqlConnection that connection object is not automatically enlisted into the Transaction, instead you have to enlist it explicitely with conn.EnlistTransaction(Transaction.Current);

Test and prove
I have prepared a simple table in a SQL Server database:

SELECT * FROM [staging].[TestTable]

Column1
-----------
1

The update query in .NET is as follows:

string query = @"UPDATE staging.TestTable
                    SET Column1 = 2";

And right after command.ExecuteNonQuery() an exception is thrown:

command.ExecuteNonQuery();
throw new System.InvalidOperationException("Something bad happened.");

Here is the full example for your reference:

string query = @"UPDATE staging.TestTable
                    SET Column1 = 2";

using (SqlConnection conn = new SqlConnection(conn2))
{
    try
    {
        conn.Open();
        using (TransactionScope ts = new TransactionScope())
        {
            conn.EnlistTransaction(Transaction.Current);
            using (SqlCommand command = new SqlCommand(query, conn))
            {
                command.ExecuteNonQuery();
                throw new System.InvalidOperationException("Something bad happened.");
            }
            ts.Complete();
        }
    }
    catch (Exception)
    {
        throw;
    }
}

If the test is executed it throws an exception before the TransactionScope is completed and the update is not applied to the table (transactional rollback) and the value rests unchanged. This is the intended behavior as everybody would expect.

Column1
-----------
1

What happens now if we forgot to enlist the connection in the transaction with conn.EnlistTransaction(Transaction.Current);?

Rerunning the example provokes the exception again and the execution flow jumps immediately to the catch block. Albeit ts.Complete(); is never called the table value has changed:

Column1
-----------
2

As the transaction scope is declared after the SqlConnection the connection is not aware of the scope and does not implicitly enlist in the so called ambient transaction.

Deeper analysis for database nerds

To dig even deeper, if execution pauses after command.ExecuteNonQuery(); and before the exception is thrown we are able to query the transaction on the database (SQL Server) as follows:

SELECT tst.session_id, tat.transaction_id, is_local, open_transaction_count, transaction_begin_time, dtc_state, dtc_status
  FROM sys.dm_tran_session_transactions tst
  LEFT JOIN sys.dm_tran_active_transactions tat
  ON tst.transaction_id = tat.transaction_id
  WHERE tst.session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE program_name = 'TransactionScopeTest')

Do note that it is possible to set the session program_name through the Application Name property in the connection string: Application Name=TransactionScopeTest;

The currently existing transaction is unfolding below:

session_id  transaction_id       is_local open_transaction_count transaction_begin_time  dtc_state   dtc_status
----------- -------------------- -------- ---------------------- ----------------------- ----------- -----------
113         6321722              1        1                      2018-11-30 09:09:06.013 0           0

Without the conn.EnlistTransaction(Transaction.Current); no transaction is bound to the active connection and therefore the changes do not happen under a transactional context:

session_id  transaction_id       is_local open_transaction_count transaction_begin_time  dtc_state   dtc_status
----------- -------------------- -------- ---------------------- ----------------------- ----------- -----------

Remarks .NET Framework vs. .NET Core
During my tests with .NET Core I came across the following exception:

System.NotSupportedException: 'Enlisting in Ambient transactions is not supported.'

It seems .NET Core (2.1.0) does currently not support the TransactionScope approach no matter whether the Scope is initialized before or after the SqlConnection.

View more solutions

Comments

[Marks]

Is the second version really doing a rollback when a exception is thrown? Edit: OK, after reading documentation i've seen it.

[Ray]

In your first example, don't you need to specify that the sqlcommand is associated with the transaction? such as using (SqlCommand Com = new SqlCommand(ComText, Conn, **trans**))? Or is that unnecessary? is it implicitly associated?

[Dave Markle]

Yes, thank you. With TransactionScope, you don't, but I'd omitted it from my first example. Edited accordingly.

[JWilliams]

While there is more to code, this provides the best granularity for being able to determine why each step would be failing. However, do note that the SqlCommand has to be associated with the transaction.

[NickG]

In the second example, I think you need to close the connection within the TransactionScope using block or you get an exception when it leaves the block saying the connection was not closed.

[Coops]

Just wrap this line trans = Conn.BeginTransaction(); in a using statement and block and then if an exception happens before the call to commit, then Rollback() will be called for you before it gets disposed.

[dev]

@DaveMarkle In store procedure i have setup to do commit and Rollback. Should i still write Commit and Rollback logic in C# when i call that SPROC?

[Dave Markle]

If your stored procedure handles the commit and rollback logic correctly, and if you don't need to interact with other transactional resources, you won't need to even have a TransactionScope object in C# at all.