如何转换为 ADO.NET 事务而不是 SQL Server 事务？

发布于 2024-08-20 01:28:43 字数 942 浏览 4 评论 0原文

现在，我有使用预期方法在 SQL Server 上启动事务的代码：

ExecuteNonQuery(connection, "BEGIN TRANSACTION");
try
{
   DoABunchOnStuff(connection);
   DoSomeMoreStuff(connection);
   JustAFewMoreThings(connection);

   ExecuteNonQuery(connection, "COMMIT TRANSACTION");
} 
catch (Exception)
{  
   ExecuteNonQuery(connection, "ROLLBACK TRANSACTION");
   throw;
}

现在我正在考虑研究使用 ADO.NET 提供的事务抽象的想法的可能性：

DbTransaction trans = connection.BeginTransaction();
try
{
   DoABunchOnStuff(connection);
   DoSomeMoreStuff(connection);
   JustAFewMoreThings(connection);

   trans.Commit();
} 
catch (Exception)
{  
   trans.Rollback();
   throw;
}

这个从基于 SQL Server 的简单转换的问题事务，到ADO.NET事务，错误是：

ExecuteNonQuery 需要命令进行交易时分配给命令的连接是在待处理的本地事务中。这命令的事务属性尚未初始化。

我是否正确地假设，如果我想使用 ADO.NET 事务，我必须完全破坏基础结构，将 DbTransaction 对象传递给在事务内运行或可能运行的每个方法？

原文

Right now i have code that initiates transactions on SQL Server using the intended method:

ExecuteNonQuery(connection, "BEGIN TRANSACTION");
try
{
   DoABunchOnStuff(connection);
   DoSomeMoreStuff(connection);
   JustAFewMoreThings(connection);

   ExecuteNonQuery(connection, "COMMIT TRANSACTION");
} 
catch (Exception)
{  
   ExecuteNonQuery(connection, "ROLLBACK TRANSACTION");
   throw;
}

Now i'm looking at thinking about the possibility of investigating the idea of using the transaction abstraction provided by ADO.NET:

DbTransaction trans = connection.BeginTransaction();
try
{
   DoABunchOnStuff(connection);
   DoSomeMoreStuff(connection);
   JustAFewMoreThings(connection);

   trans.Commit();
} 
catch (Exception)
{  
   trans.Rollback();
   throw;
}

Problem with this simple conversion from SQL Server based transactions, to ADO.NET transactions, is the error:

ExecuteNonQuery requires the command
to have a transaction when the
connection assigned to the command is
in a pending local transaction. The
Transaction property of the command
has not been initialized.

Am i correct in assuming that if i wanted to use ADO.NET transactions i would have to completely gut the infrastructure, passing along a DbTransaction object to every method that does, or may, operate inside a transaction?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

妖妓 2024-08-27 01:28:43

您是对的，但由于您显然一直保持连接打开，因此您可以将其替换为 TransactionScope 改为；只要只有一个开放连接，它就不会升级为 DTC。

示例：

using (TransactionScope tsc = new TransactionScope())
{
    DoABunchOnStuff(connection);
    DoSomeMoreStuff(connection);
    JustAFewMoreThings(connection);
    tsc.Complete();
}

有关使用 TransactionScope 的注意事项：

您必须确保在连接字符串中包含 Transaction Binding = Explicit Unbind。默认情况下，事务在隐式解除绑定模式下运行，这意味着如果事务超时，它们会切换到自动提交模式。您几乎从不想要默认行为，因为它可能会干扰事务的原子性并导致某些人所说的数据损坏（即使它不是真正的“损坏”）。只要在连接字符串中使用正确的参数，就无需担心这一点。
如果范围内有多个连接（包括链接服务器和OPENROWSET），
TransactionScope 将升级为 DTC（分布式事务）。尽管这看起来像是不受欢迎的行为，但您的代码不会以任何其他方式实现事务安全。在多个连接上执行手动 BEGIN TRAN 语句并将多个 ROLLBACK 语句放入异常处理程序不能确保整个事务的原子性。
事务范围被设计为嵌套的，并且会自动找出开始新事务和加入现有事务之间的区别。这比匹配 BEGIN TRAN 和 COMMIT/ROLLBACK 语句强大得多，因为后者依赖于连接本地事务计数，而前者实际上是......范围。使用 TransactionScope 类似于 SQL Server 中使用 SAVE TRAN、TRY/CATCH 进行结构化事务处理，并命名为 < code>ROLLBACK - 您无需担心如果下游进程或过程扰乱事务逻辑会发生什么，这在发送原始 BEGIN 和 ROLLBACK< 时是一个严重的风险/code> ADO.NET 上的语句。

You are correct, but since you are evidently keeping the connection open the whole time, you could replace this with a TransactionScope instead; it won't promote to DTC as long as there's only one open connection.

Example:

using (TransactionScope tsc = new TransactionScope())
{
    DoABunchOnStuff(connection);
    DoSomeMoreStuff(connection);
    JustAFewMoreThings(connection);
    tsc.Complete();
}

Notes about using the TransactionScope:

You must make sure to include Transaction Binding = Explicit Unbind in your connection string. By default transactions are run in implicit-unbind mode which means that they switch to auto-commit mode if the transaction times out. You almost never want the default behaviour, as it can interfere with the atomicity of your transactions and cause what some people refer to as data corruption (even though it's not actual "corruption"). As long as you use the correct parameters in your connection string, you don't need to worry about this.
TransactionScope will promote to DTC (distributed transaction) if there is more than one connection in scope, which includes linked servers and OPENROWSET. Although this might seem like undesirable behaviour, your code isn't going to be transactionally safe any other way. Executing manual BEGIN TRAN statements on multiple connections and putting multiple ROLLBACK statements in an exception handler does not ensure atomicity of the entire transaction.
Transaction Scopes are designed to be nested and will automatically figure out the difference between beginning a new transaction and enlisting in an existing one. This is a lot more powerful than matching up BEGIN TRAN and COMMIT/ROLLBACK statements, as the latter rely on a connection-local transaction count, whereas the former is actually... scoped. Using TransactionScope is similar to structured transaction handling in SQL Server using SAVE TRAN, TRY/CATCH, and named ROLLBACK - you do not need to worry about what happens if a downstream process or procedure flubs the transactional logic, which is a serious risk when sending raw BEGIN and ROLLBACK statements over ADO.NET.

回复收藏 0 原文

想你的星星会说话 2024-08-27 01:28:43

我的假设是否正确，如果我
我想使用 ADO.NET 事务
必须彻底清除
基础设施，通过
每个方法的 DbTransaction 对象
确实或可能在一个
交易？

是的，完全正确 - 您基本上需要将您创建的事务与应该在该事务的保护伞下执行的每个 SqlCommand 关联起来 - 所以您必须有类似的内容：

DbTransaction trans = connection.BeginTransaction();
try
{
   DoABunchOnStuff(connection, trans);
   DoSomeMoreStuff(connection, trans);
   JustAFewMoreThings(connection, trans);

   trans.Commit();
} 
catch (Exception)
{  
   trans.Rollback();
   throw;
}

在这些方法中包含以下内容：

public void DoABunchOnStuff(SqlConnection connection, SqlTransaction trans)
{
    using(SqlCommand cmd = new SqlCommand(--sql stmt--, connection, trans)
    {
       ........
    } 
}

Am i correct in assuming that if i
wanted to use ADO.NET transactions i
would have to completely gut the
infrastructure, passing along a
DbTransaction object to every method
that does, or may, operate inside a
transaction?

Yes, exactly - you basically need to associate the transaction you've created with each SqlCommand that ought to be executed under that transaction's umbrella - so you'd have to have something like:

DbTransaction trans = connection.BeginTransaction();
try
{
   DoABunchOnStuff(connection, trans);
   DoSomeMoreStuff(connection, trans);
   JustAFewMoreThings(connection, trans);

   trans.Commit();
} 
catch (Exception)
{  
   trans.Rollback();
   throw;
}

and inside those methods something along the lines of:

public void DoABunchOnStuff(SqlConnection connection, SqlTransaction trans)
{
    using(SqlCommand cmd = new SqlCommand(--sql stmt--, connection, trans)
    {
       ........
    } 
}

回复收藏 0 原文