Alex Rivera | Logout

Refactoring ADO.NET - SqlTransaction vs. TransactionScope

Asked 2009-08-13T09:29:53.360
33

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

Edit
Report

1 Answer

9

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.

answered 2009-08-13T13:08:40.700

Your Answer