Why use a using statement with SqlTransaction?

Question

I am having problems with SqlTransaction, which I use in my code. During my Googling, I see a lot of people using the using statement with SqlTransaction.

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

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

Currently my code is as follows:

 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 method over another?

Answer 1

Operator

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

In particular, your code only catches managed exceptions and then destroys the stack frame, causing a new exception instead of re-creating the existing one.

The correct way to do this 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 instances of instances of types that implement IDisposable , then your class must implement IDisposable itself and delete these members during its own call to Dispose() .

Answer 2

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

Answer 3

Essentially, using does the same thing you do, with the exception of int finally block instead of catching all exceptions:

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

- this is the same as 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 to the template. But this is a very useful and useful shortcut, because it guarantees the correct implementation of the template and means that you can do this with less code.

In this case, you have implemented the template incorrectly. What happens in your code if calling tr.RollBack() also throws an exception?

Answer 5

using statement closes and deletes your connection and transaction for you. This is the equivalent of having a finally block on your try / catch, which does dispose.

You might as well condense using blocks like this ...

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

which will be about 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 are not using the using () block, you will have to explicitly call the .Dispose () method of the SqlConnection and SqlTransaction objects. If you do not, then unmanaged resources will not be released and may cause a memory leak or other problems.

Answer 7

In addition to all this, it exaggerates your code. Do 7 lines of code look better than 14 lines? I feel relieved every time I see a block. It is like that little skin of fog that comes from this joyful smelly thing. Mmm, I'm pretty effective code. See how well I manage my memory and how pleasing I am for the eyes.

Answer 8

Using gurantees is that your connection object will be deleted after returning the code. Dispose is useful for freeing unmanages resources. As a good practice, if an object implements IDisposable, the dispose method should always be called

Answer 9

The Using statement is a shorthand for the proper handling of a resource. You can find more information on the MSDN article on how to use the instructions.