Transaction Stored Procedure C#

Question

Hi All,
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.

Answer 1

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.