Why use a using statement with a SqlTransaction?

Question

I've been running into some problems concerning a SqlTransaction I'm using in my code. During my Googling I see many people using a using statement with a SqlTransaction.

What is the benefit and/or difference of using this type of statement with a SqlTransaction?

using (SqlConnection cn = new SqlConnection())
{
     using (SqlTransaction tr = cn.BeginTransaction())
     {
      //some code
      tr.Commit();
     }
}

Currently my code looks like this:

SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["T3"]);
cn.Open();
SqlTransaction tr = cn.BeginTransaction();

try
{
     //some code
     tr.Commit();
     cn.Close();
}
catch(Exception ex)
{
      tr.Rollback();
      cn.Close();
      throw ex;
}

What is the advantage of one way over the other?

Answer 1

A using statement should be used every time you create an instance of a class that implements IDisposable within the scope of a block. It ensures that the Dispose() method will be called on that instance, whether or not an exception is thrown.

In particular, your code only catches managed exceptions, then destroys the stack frame by throwing a new exception instead of rethrowing the existing one.

The correct way to do it is:

using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["T3"])) {
    cn.Open();
    using (SqlTransaction tr = cn.BeginTransaction()) {
        //some code
        tr.Commit();
    }
}

Note that if your class has instance members of types that implement IDisposable, then your class must implement IDisposable itself, and dispose of those members during its own Dispose() call.

Answer 2

The reason for this is that the SqlTransaction object will roll back in its Dispose() method if it was not explicitly committed (e.g. if an exception is thrown). In other words, it has the same effect as your code, just a little bit cleaner.

Answer 3

Essentially the using does the same thing that you are doing, except int a finally block instead of catching all exceptions:

using (SqlConnection cn = new SqlConnection())
{
     using (SqlTransaction tr = cn.BeginTransaction())
     {
      //some code
      tr.Commit();
     }
}

is the same as, just much less code :)

{
    SqlConnection cn = null;
    try
    {
       cn = new SqlConnection();
       {
           SqlTransaction tr = null;
           try
           {
               tr = cn.BeginTransaction())

               //some code
               tr.Commit();
            }
            finally
            {
                if(tr != null && tr is IDisposable)
                {
                    tr.Dispose();
                }
            }
        }
    }
    finally
    {
        if(cn != null && cn is IDisposable)
        {
            cn.Dispose();
        }
    }
}

Answer 4

In the end, using is just a shortcut for a pattern. But it's a very useful and helpful shortcut, because it ensures you implement the pattern correctly and means you can do it with less code.

In this case, you haven't implemented the pattern correctly. What happens in your code if the call to tr.RollBack() also throws an exception?

Answer 5

The using statement is closing and disposing your connection and transaction for you. It's the equivalent of having a finally block on your try/catch that does the dispose.

You could also condense the using blocks down a bit like this...

using (SqlConnection cn = new SqlConnection())
using (SqlTransaction tr = cn.BeginTransaction())     
{
      //some code
      tr.Commit();
}

which would be roughly the same as:

SqlConnection cn = null;
SqlTransaction tr = null;
try
{
    cn = new SqlConnection());
    tr = cn.BeginTransaction());

    //some code
    tr.Commit();
}
finally
{
    if (cn != null)
        cn.Dispose();
    if (tr != null)    
        tr.Dispose();
}

Answer 6

If you don't use a using() block, you'll have to explicitly call the .Dispose() method of the SqlConnection and SqlTransaction objects. If you fail to do that, then unmanaged resources will not be released and could cause memory leaks or other problems.

Answer 7

In addition to all that, it prettifies your code. Doesn't the 7 lines of code look better than the 14 lines? I breath a sign of relief every time I see a using block. It's like that little squirt of mist that comes out of that glad smelly thing. Mmm, I'm a pretty block of efficient code. Look at how well I manage memory and how pleasing I am to the eye.

Answer 8

Using using gurantees that your connection object will be disposed after the code returns. Dispose is useful to release unmanages resources, As a good practice, if an object implements IDisposable, dispose method always should be called