Refactoring ADO.NET - SqlTransaction vs. TransactionScope

Question

I have "inherited" a little C# method that creates an ADO.NET SqlCommand object and loops over a list of items to be saved to the database (SQL Server 2005).

Right now, the traditional SqlConnection/SqlCommand approach is used, and to make sure everything works, the two steps (delete old entries, then insert new ones) are wrapped into an ADO.NET SqlTransaction.

using (SqlConnection _con = new SqlConnection(_connectionString))
{
   using (SqlTransaction _tran = _con.BeginTransaction())
   {
      try
      {
         SqlCommand _deleteOld = new SqlCommand(......., _con);
         _deleteOld.Transaction = _tran;
         _deleteOld.Parameters.AddWithValue("@ID", 5);

         _con.Open();

         _deleteOld.ExecuteNonQuery();

         SqlCommand _insertCmd = new SqlCommand(......, _con);
         _insertCmd.Transaction = _tran;

         // add parameters to _insertCmd

         foreach (Item item in listOfItem)
         {
            _insertCmd.ExecuteNonQuery();
         }

         _tran.Commit();
         _con.Close();
       }
       catch (Exception ex)
       {
          // log exception
          _tran.Rollback();
          throw;
       }
    }
}

Now, I've been reading a lot about the .NET TransactionScope class lately, and I was wondering, what's the preferred approach here? Would I gain anything (readibility, speed, reliability) by switching to using

using (TransactionScope _scope = new TransactionScope())
{
  using (SqlConnection _con = new SqlConnection(_connectionString))
  {
    ....
  }

  _scope.Complete();
}

What you would prefer, and why?

Marc

Sidonie

Answer 1

You won't immediately gain anything by switching your existing code to use TransactionScope. You should use it for future development because of the flexibility it provides. It will make it easier in the future to include things other than ADO.NET calls into a transaction.

BTW, in your posted example, the SqlCommand instances should be in using blocks.

Stupe

Okay, thanks John - and yes, you're right - this example doesn't have the SqlCommand in using() blocks (yet!) - this is work in progress :-) – Sidonie

re: SQLCommand inside using, is there any other reason besides garbage collection, e.g.: Dispose() for a SQLConnection calls the Close() method, so does Dispose() call any SQLCommand methods? – Kortneykoruna

If a class implements IDisposable, and if you create an instance of this class, then you should call Dispose on it. The simplest way to do this is with a using block. I find it's best to get into the habit of always implementing one. – Stupe

Answer 2

I prefer TransactionScope. It doesn't work perfectly in every scenario, but in the one you describe, it's the better solution.

My reasoning:

1. Enlistment in the Transaction is automatic
2. Transaction rollback in the event of an Exception is automatic

Together, the result is a little less code and a generally more robust design, since the system is handling some of the details for me; it's one less thing I have to remember to do.

In addition, transparent Transaction enrollment can be particularly useful when you have a number of nested methods in your DAL -- although you do have to take care to not accidentally have your transaction turn into a distributed one that requires the DTC, which can happen if you use multiple SqlConnections, even if they point to the same DB.

Nollie

Answer 3

Microsoft recommends using transaction scope:

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

The basic idea is that transaction scope will manage the "ambient transaction context" for you. You start by talking to one database, you have an sql transaction, then you talk to database number 2, and the transaction is elevated to a distributed transaction.

Transaction scope does work for you, so that you can concentrate on the functionality of the system, rather than the plumbing.

EDIT

When you use a transaction scope everything within that scope is covered by the transaction. You therefore, save a line of code, where you connect the command to the transaction. This is a possible source of error, for example if there were one chance in 1000 that this line had been forgoten, how many would you be missing.

EDIT 2

Agree with comment on Triynko below. However, we use Entity Framework, EF will automatically close and reopen a connection in order to enlist it in a transaction. It does not physically close the connection more like, it releases it to the connection pool and gets a new one, which can be the same one or can be a different one.

Coffle

OK,. thanks for that. But do I benefit in any way, if I refactor everything (it's not just this one sample) to use TransactionScope() instead of ADO.NET embedded transactions? Just asking if the effort is worth it - what do I gain by it? – Sidonie

"When you use a transaction scope everything within that scope is covered by the transaction." No, everything is not covered. Only commands issued on connections enlisted in the scope are affected by the scope. Connections are automatically enlisted in the scope if opened in the scope, otherwise already-opened connections need to be manually enlisted in the scope after its created by calling SqlConnection.EnlistTransaction. If you, for example, open your connection, then create the transaction scope... none of your commands will be involved in the transaction. – Jambalaya

@Triynko: Your comment to corresponding MSDN article is great! – Kanchenjunga

Answer 4

Just note using Transaction Scope sometimes we will much problem because many setting that we must do in Server like setting DTC, Firewall and etc. So I recommended using SqlTransaction is more save in implementation.

Tinfoil

Answer 5

Ok, maybe is too late for this... but Anyway, I will write it down for those interested...

Since I have a better picture now, after having a lot of difficulties with my current SqlTransaction based approach which I might change in favor of TransactionScope, as I see it... main advantage of TransactionScope is that it can be used very easily in the Business Layer.

Strick

Answer 6

Also late... You can easily have "nested" transactions in the business layer even if the database doesn't support nested transactions. .NET controls the nesting and ends up using one database transaction (at least in the case of SQL Server 2008+). This makes it much easier to reuse data access code outside of its original intent, as part of a larger transaction.

Prothallus