2

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.

Justin Samuel
  • 1,063
  • 4
  • 16
  • 30
  • How are you using the transaction scope in your code (with using statement or try/catch)? – Goran Dec 01 '09 at 12:26
  • Yes Goran, I using "using" statement like: using (TransactionScope scope = new TransactionScope()) enclosed in a try/catch block. and the calls to the SPs are within this scope – Justin Samuel Dec 01 '09 at 12:36
  • I don't see how this goes wrong. How about adding a rollback call in your OnExit method? This should ensure rollback is called when you stop the console app and transaction isn't completed. – Goran Dec 01 '09 at 12:46
  • Does anybody have idea of what could be the issue? The scenario is like, when I open the third instance of the Console App it times out.I am getting the following exception: SqlException - "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Could this because of SP taking more time. The SP simply has a select of the Top ID and updates a column of this Top ID. I think I need not change the CommandTimeout as the SP is pretty straightforward. – Justin Samuel Dec 02 '09 at 06:28

1 Answers1

4

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.

Community
  • 1
  • 1
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Marc, I am bit confused. I am fetching one row at a time. Doin some processing using the two SPs. This individual row processing is in a TransactionScope. If I write SqlConnection within each Transaction it would mean separate connection in each transaction. Please suggest if I am wrong! – Justin Samuel Dec 01 '09 at 14:06
  • Finally, I had to create a separate connection within each transaction. Excellent Marc!!! Thanks Marc and Goran for the support. Cheers!!! – Justin Samuel Dec 01 '09 at 15:02
  • Sounds like you are blocking yourself due to serializable isolation level. – Marc Gravell Dec 01 '09 at 15:04
  • Marc, I working perfectly fine with two instance of Console opened. and the transaction works just smoothly. However when I open a third Console App, i just timesOut. Need to investigate this! :) – Justin Samuel Dec 01 '09 at 15:05