重构 ADO.NET - SqlTransaction 与 TransactionScope

我继承"了一个小的 C# 方法,该方法创建一个 ADO.NET SqlCommand 对象并循环遍历要保存到数据库 (SQL Server 2005) 的项目列表.

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).

现在,使用的是传统的 SqlConnection/SqlCommand 方法,为了确保一切正常,将两个步骤(删除旧条目,然后插入新条目)包装到 ADO.NET SqlTransaction 中.

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;
       }
    }
}

现在,我最近阅读了很多关于 .NET TransactionScope 类的文章,我想知道这里的首选方法是什么?通过切换到使用,我会获得什么(可读性、速度、可靠性)

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?

马克

推荐答案

将现有代码切换为使用 TransactionScope 不会立即获得任何收益.您应该将它用于未来的开发,因为它提供了灵活性.将来可以更轻松地将 ADO.NET 调用以外的内容包含到事务中.

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.

顺便说一句,在您发布的示例中,SqlCommand 实例应该在 using 块中.

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

相关文章