Reputation: 1083
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.
Upvotes: 2
Views: 6405
Reputation: 1064134
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.
Upvotes: 4