Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why use a using statement with a SqlTransaction?

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?

like image 653
MDStephens Avatar asked Jul 14 '09 20:07

MDStephens


People also ask

Can we use transaction in C#?

We implemented transactions in front-end code(C# code) but we can also define transactions in back-end code like the SQLServer database. Transaction Control: There are the following commands used to control transactions. COMMIT To save changes.

What is SqlTransaction C#?

SqlTransaction class of ADO.NET is used to execute transactions. Learn how to use the SqlTransaction in C# and SQL. Database transaction takes a database from one consistent state to another.

Why do we use SQL transaction?

For any business, transactions that may be comprised of many individual operations and even other transactions, play a key role. Transactions are essential for maintaining data integrity, both for multiple related operations and when multiple users that update the database concurrently.


1 Answers

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.

like image 120
John Saunders Avatar answered Sep 28 '22 05:09

John Saunders