事务存储过程 C# [英] Transaction Stored Procedure C#

问题描述

我遇到了一个小问题.请指导我.我正在用 C#(控制台应用程序)编码.我在我的代码中调用了 2 个不同的存储过程.基本上这两个存储过程访问同一个表.第一个 SP 有一个选择查询和一个更新查询.第二个 SP 有一个更新查询.

I am having a slight issue. Please guide me. I am coding in C#(Console App). I have called 2 different stored procedure in my code. Basically both these stored procedures access the same table. First SP has a select query and an update query. Second SP has a single update query.

现在我想在事务模式下调用这些SP(要么全部成功，要么第二个SP失败回滚第一个SP).我在 C# 代码中使用了TransactionScope"，但似乎无法正常工作.即当我停止控制台应用程序时，有时我会看到第一个 SP 被执行而第二个失败.

Now I want to call these SP in a transaction mode(Either all succeeds or is second SP fails rollback first SP). I have used "TransactionScope" within my C# code but is doesnt seem to work fine. ie when I stop the Console App sometimes I see that the first SP is executed and the second one fails.

有人可以建议我吗.

问候，
贾斯汀塞缪尔.

Regards,
Justin Samuel.

推荐答案

如果您使用 TransactionScope，它应该工作正常，但范围必须 包围连接:

If you are using TransactionScope, it should work fine, but the scope must surround the connection(s):

using(TransactionScope tran = new TransactionScope()) {
    using(SqlConnection conn = new SqlConnection(cs)) {
      // either multiple commands on one connection
      using(SqlCommand cmd = conn.CreateCommand()) {
        // etc
      }
      using(SqlCommand cmd = conn.CreateCommand()) {
        // etc
      }
    }
    using(SqlConnection conn = new SqlConnection(cs)) {
      // or a separate connection
      using(SqlCommand cmd = conn.CreateCommand()) {
        // etc
      }
    }
    tran.Complete();
}

是 一种边缘情况，其中 TransactionScope 可能失败 导致后面的命令在没有事务的情况下运行.

There is an edge case where a TransactionScope can fail causing the later command to run without a transaction.

或者，对于单个连接，使用 SqlTransaction，但记住将事务(从连接)关联到每个命令.

Alternatively, for a single connection use SqlTransaction, but remember to associate the transaction (from the connection) to each command.

这篇关于事务存储过程 C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！