sql server - 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

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.