开发者

Transaction Stored Procedure C#

开发者 https://www.devze.com 2022-12-13 10:16 出处:网络
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.

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.

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.

Can anybody suggest me on this.

Regards,

Justin Samuel.


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

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

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

0

精彩评论

暂无评论...
验证码 换一张
取 消