C# - Rollback SqlTransaction in catch block - Problem with object accessability

Question

Hi there.
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 thats a bit risky, since both can throw multiple exceptions.

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

Thanks in advance, Marks

Answer 1

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

Answer 2

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

        using (SqlCommand Com = new SqlCommand(ComText, Conn))
        {
            /* 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;
    }
}

Answer 3

Microsoft samples, place the begin trans outside of the try/catch (see http://msdn.microsoft.com/en-us/library/2k2hy99x.aspx). I assume that the BeginTransaction method should either throw an exception OR begin a transaction but never both (although the documentation does not say this is impossible).

However, you may be better of using TransactionScope which manages a lot of the (not so) heavy lifting for you: http://msdn.microsoft.com/en-us/library/ms172070.aspx.

Answer 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;
    }
}