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?

For "less" nesting, I believe you can skip the first pair of curly brackets. (or is it braces...) — Svish
– Svish, Commented Jul 14, 2009 at 20:38

Michael come lately · Accepted Answer · 2014-04-10 20:36:30Z

62

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.

edited Apr 10, 2014 at 20:36

Michael come lately

9,6418 gold badges70 silver badges97 bronze badges

answered Jul 14, 2009 at 20:30

John Saunders

162k26 gold badges252 silver badges403 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

marc_s Over a year ago

....because it guarantees that the "Dispose" method of the IDisposble interface being implemented will be called - no matter what happens in your code.

Stefan Steinegger Over a year ago

+1, generally correct, but you can't always use a using statement, sometimes you need to implement IDisposable yourself. I would say: "Whenever possible", which means "whenever creating an IDisposable instance used only within a bloc" or something like this.

John Saunders Over a year ago

Yes, if Dispose is called on the SqlTransaction before a Commit, then the transaction will be rolled back. Of course, Dispose will be called if an exception is thrown within the block and not handled.

Zo Has Over a year ago

Do we have to explicitly declare tran.rollBack() inside a catch or will this be handled by the using block?

Erik Bergstedt Over a year ago

@ZoHas it will be handled in the Dispose

|

Ken Keenan · Accepted Answer · 2009-07-14 20:32:18Z

30

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.

answered Jul 14, 2009 at 20:32

Ken Keenan

10.7k5 gold badges35 silver badges52 bronze badges

2 Comments

ddotsenko Over a year ago

Confirmed this by de-compiling. Calls this.Rollback() on this.Dispose().

d512 Over a year ago

Actually, whether or not Rollback is called inside Dispose() dependent on the implementation of the driver you're using (see msdn.microsoft.com/en-us/library/bf2cw321(v=vs.110).aspx). Driver implementors are supposed to call Rollback, but Microsoft recommends not counting on it. So if you know the only driver you'll ever use does call Rollback inside Dispose() you're safe. Otherwise, it's safer to explicitly call it.

Joel Coehoorn · Accepted Answer · 2009-07-14 20:43:18Z

16

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();
        }
    }
}

edited Jul 14, 2009 at 20:43

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

answered Jul 14, 2009 at 20:35

heavyd

17.8k5 gold badges59 silver badges76 bronze badges

3 Comments

Robert Rossney Over a year ago

You actually have an extra set of braces in your first example: you can nest using statements without creating a new block, e.g. using (x = new X) using (y = new Y) { }

heavyd Over a year ago

Just being explicit, same reason we always put braces around all our if statements, even if they're one-liners

Laurynas Lazauskas Over a year ago

You should also do cn.Open(); before using (SqlTransaction tr .... Otherwise you will receive InvalidOperationException. Or am I missing something?

Joel Coehoorn · Accepted Answer · 2021-04-26 13:15:56Z

9

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?

edited Apr 26, 2021 at 13:15

answered Jul 14, 2009 at 20:34

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Comments

Scott Ivey · Accepted Answer · 2009-07-14 20:39:31Z

4

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();
}

edited Jul 14, 2009 at 20:39

answered Jul 14, 2009 at 20:32

Scott Ivey

41.7k21 gold badges85 silver badges118 bronze badges

2 Comments

Joel Coehoorn Over a year ago

Very close, but there's also an additional anonymous scope block in there. The code as you have it won't compile, because cn and tr are out of scope in the finally block.

WiiMaxx Over a year ago

@ZoHas it will automatically Rollback at Dispose(); if there was none call of Commit(); see also

Matthew Groves · Accepted Answer · 2009-07-14 20:31:03Z

3

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.

answered Jul 14, 2009 at 20:31

Matthew Groves

26.2k11 gold badges76 silver badges126 bronze badges

2 Comments

Joel Coehoorn Over a year ago

Would not cause memory leaks, but it might cause resource leaks.

Andrei Rînea Over a year ago

... which will efff you worse and faster.

Levitikon · Accepted Answer · 2012-04-06 16:51:03Z

2

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.

answered Apr 6, 2012 at 16:51

Levitikon

7,9079 gold badges60 silver badges74 bronze badges

Comments

jyotishka bora · Accepted Answer · 2009-07-14 20:40:33Z

1

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

answered Jul 14, 2009 at 20:40

jyotishka bora

3,9732 gold badges26 silver badges19 bronze badges

2 Comments

John Saunders Over a year ago

Connection - and the SqlTransaction object.

John Saunders Over a year ago

If an object implements IDisposable, then a using block (or statement) should be used, which guarantees that Dispose will be called.

Collectives™ on Stack Overflow

Why use a using statement with a SqlTransaction?

8 Answers 8

10 Comments

2 Comments

3 Comments

Comments

2 Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

10 Comments

2 Comments

3 Comments

Comments

2 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related