C# .net 中的交易

Transactions in .net

提问by Malik Daud Ahmad Khokhar

What are the best practices to do transactions in C# .Net 2.0. What are the classes that should be used? What are the pitfalls to look out for etc. All that commit and rollback stuff. I'm just starting a project where I might need to do some transactions while inserting data into the DB. Any responses or links for even basic stuff about transactions are welcome.

在 C# .Net 2.0 中进行事务的最佳实践是什么。应该使用哪些类？需要注意的陷阱是什么等等。所有提交和回滚的东西。我刚刚开始一个项目，我可能需要在将数据插入数据库时​​进行一些事务。欢迎任何关于交易的基本内容的回复或链接。

采纳答案by Marc Gravell

There are 2 main kinds of transactions; connection transactions and ambient transactions. A connection transaction (such as SqlTransaction) is tied directly to the db connection (such as SqlConnection), which means that you have to keep passing the connection around - OK in some cases, but doesn't allow "create/use/release" usage, and doesn't allow cross-db work. An example (formatted for space):

有两种主要的交易；连接事务和环境事务。连接事务（例如 SqlTransaction）直接绑定到 db 连接（例如 SqlConnection），这意味着您必须不断传递连接 - 在某些情况下可以，但不允许“创建/使用/释放”用法，并且不允许跨数据库工作。一个例子（格式化为空格）：

using (IDbTransaction tran = conn.BeginTransaction()) {
    try {
        // your code
        tran.Commit();
    }  catch {
        tran.Rollback();
        throw;
    }
}

Not too messy, but limited to our connection "conn". If we want to call out to different methods, we now need to pass "conn" around.

不太凌乱，但仅限于我们的连接“conn”。如果我们想调用不同的方法，我们现在需要传递“conn”。

The alternative is an ambient transaction; new in .NET 2.0, the TransactionScopeobject (System.Transactions.dll) allows use over a range of operations (suitable providers will automatically enlist in the ambient transaction). This makes it easy to retro-fit into existing (non-transactional) code, and to talk to multiple providers (although DTC will get involved if you talk to more than one).

另一种选择是环境交易；.NET 2.0 中的新增功能，TransactionScope对象 (System.Transactions.dll) 允许在一系列操作中使用（合适的提供程序将自动加入环境事务）。这使得改造现有（非事务性）代码并与多个提供者交谈变得容易（尽管如果您与多个提供者交谈，DTC 将参与其中）。

For example:

例如：

using(TransactionScope tran = new TransactionScope()) {
    CallAMethodThatDoesSomeWork();
    CallAMethodThatDoesSomeMoreWork();
    tran.Complete();
}

Note here that the two methods can handle their own connections (open/use/close/dispose), yet they will silently become part of the ambient transaction without us having to pass anything in.

请注意，这两个方法可以处理它们自己的连接（打开/使用/关闭/处置），但它们将默默地成为环境事务的一部分，而无需我们传入任何内容。

If your code errors, Dispose() will be called without Complete(), so it will be rolled back. The expected nesting etc is supported, although you can't roll-back an inner transaction yet complete the outer transaction: if anybody is unhappy, the transaction is aborted.

如果您的代码出错，Dispose() 将在没有 Complete() 的情况下被调用，因此它将被回滚。支持预期的嵌套等，尽管您不能回滚内部事务但完成外部事务：如果有人不满意，事务将被中止。

The other advantage of TransactionScope is that it isn't tied just to databases; any transaction-aware provider can use it. WCF, for example. Or there are even some TransactionScope-compatible object models around (i.e. .NET classes with rollback capability - perhaps easier than a memento, although I've never used this approach myself).

TransactionScope 的另一个优点是它不仅仅与数据库相关联；任何事务感知提供者都可以使用它。例如，WCF。或者甚至有一些与 TransactionScope 兼容的对象模型（即具有回滚功能的 .NET 类 - 也许比纪念品更容易，尽管我自己从未使用过这种方法）。

All in all, a very, very useful object.

总而言之，一个非常非常有用的对象。

Some caveats:

一些注意事项：

• On SQL Server 2000, a TransactionScope will go to DTC immediately; this is fixed in SQL Server 2005 and above, it can use the LTM (much less overhead) until you talk to 2 sources etc, when it is elevated to DTC.
• There is a glitchthat means you might need to tweak your connection string

• 在 SQL Server 2000 上，TransactionScope 将立即转到 DTC；这在 SQL Server 2005 及更高版本中已修复，当它提升到 DTC 时，它可以使用 LTM（开销更少），直到您与 2 个源等交谈。
• 有一个小故障，这意味着您可能需要调整您的连接字符串

回答by Joachim Kerschbaumer

if you just need it for db-related stuff, some OR Mappers (e.g. NHibernate) support transactinos out of the box per default.

如果您只需要它用于与数据库相关的东西，一些 OR 映射器（例如 NHibernate）默认支持开箱即用的 transactinos。

回答by Brody

It also depends on what you need. For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code. That is the easiest way but it does have complexity and you have to be careful to commit properly (and rollback).

这也取决于你需要什么。对于基本的 SQL 事务，您可以尝试通过在代码中使用 BEGIN TRANS 和 COMMIT TRANS 来执行 TSQL 事务。这是最简单的方法，但它确实具有复杂性，您必须小心正确提交（和回滚）。

I would use something like

我会使用类似的东西

SQLTransaction trans = null;
using(trans = new SqlTransaction)
{
    ...
    Do SQL stuff here passing my trans into my various SQL executers
    ...
    trans.Commit  // May not be quite right
}

Any failure will pop you right out of the usingand the transaction will always commit or rollback (depending on what you tell it to do). The biggest problem we faced was making sure it always committed. The using ensures the scope of the transaction is limited.

任何失败都会让您立即退出，using并且事务将始终提交或回滚（取决于您告诉它做什么）。我们面临的最大问题是确保它始终承诺。使用确保事务的范围是有限的。

回答by Charles Graham

You could also wrap the transaction up into it's own stored procedure and handle it that way instead of doing transactions in C# itself.

您还可以将事务包装到它自己的存储过程中并以这种方式处理它，而不是在 C# 本身中执行事务。

回答by Ali Gholizadeh

protected void Button1_Click(object sender, EventArgs e)
   {


       using (SqlConnection connection1 = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"))
       {
           connection1.Open();

           // Start a local transaction.
           SqlTransaction sqlTran = connection1.BeginTransaction();

           // Enlist a command in the current transaction.
           SqlCommand command = connection1.CreateCommand();
           command.Transaction = sqlTran;

           try
           {
               // Execute two separate commands.
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('a','b','c')";
               command.ExecuteNonQuery();
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('x','y','z')";
               command.ExecuteNonQuery();

               // Commit the transaction.
               sqlTran.Commit();
               Label3.Text = "Both records were written to database.";
           }
           catch (Exception ex)
           {
               // Handle the exception if the transaction fails to commit.
               Label4.Text = ex.Message;


               try
               {
                   // Attempt to roll back the transaction.
                   sqlTran.Rollback();
               }
               catch (Exception exRollback)
               {
                   // Throws an InvalidOperationException if the connection 
                   // is closed or the transaction has already been rolled 
                   // back on the server.
                   Label5.Text = exRollback.Message;

               }
           }
       }


   }