Triggering a rollback inside transactionscope C#

Question

I'm currently working on a stored procedure that will perform a couple of inserts into a database table and I want to test and see if it can return the total rows affected in a way I'd like it to. And I'm calling this stored procedure from my C# .NET code inside a transactionscope.

My question however, is how I can trigger a rollback after the stored procedure is executed and the rows affected is displayed on the console?

I'm not allowed to share my code, but I can give a pseudo code of it as it's quite simple:

using(TransactionScope scope){
  //Run the procedure and save the return value in a variable
  int rowsAffected = MyStoredProcedure();

  //Print the value in the variable
  Console.WriteLine(rowsAffected);

  //Ideally, I want to perform the rollback here.
  scope.Complete();
}

Is it enough to simple throw some sort of Exception or is there a better way to trigger a rollback?

Possible duplicate of [How does TransactionScope roll back transactions?](https://stackoverflow.com/questions/494550/how-does-transactionscope-roll-back-transactions) — Justinas Marozas, Mar 06 '18 at 08:44
In this case, as stated in answer below, you can just omit `Complete`. If for some reason you need to rollback earlier than end of `using` block - just call `scope.Dispose()`. — Evk, Mar 06 '18 at 09:25
On a separate note, be aware that the default `TransactionScope` isolation level is serializable, which is prone to deadlocks. Unless you specifically need serializable, consider specifying `TransactionOptions.IsolationLevel.ReadCommitted`. — Dan Guzman, Mar 06 '18 at 11:26
@DanGuzman Yes, that I'm aware of and have implemented. I was thinking it would be easier if I kept the code simple and short for readability. — Alican Bircan, Mar 06 '18 at 12:10

score 3 · Accepted Answer · answered Mar 06 '18 at 08:46

It's not committed as long as you don't call the 'Complete'. Remove that and it will be rollbacked when you leave the using of the scope:

using(TransactionScope scope)
{
  //Run the procedure and save the return value in a variable
  int rowsAffected = MyStoredProcedure();

  //Print the value in the variable
  Console.WriteLine(rowsAffected);

  //Don't call Complete() and it will be rollbacked
  //scope.Complete();
}

score 1 · Answer 2 · answered Mar 06 '18 at 09:03

1

Since you are using stored procedure. Why don't you keep the transaction in the stored procedure itself. Then you no need to worry about handling rollback in c# code

answered Mar 06 '18 at 09:03

Techiemanu

786
7
24

It's more for testing purposes rather than rollback handling in the code. I don't want to drop my databasetable and then restore it again. – Alican Bircan Mar 06 '18 at 12:02

Triggering a rollback inside transactionscope C#

2 Answers2