Transaction management using TransactionScope ()

Question

I need to create a simple dotnet application which is going to call a stored procedure inside a loop (the stored procedure accepts few parameters and add them to a table). The requirement is that either all the rows get inserted or non.

To ensure this I've used:

using (TransactionScope scope = new TransactionScope())
{
    foreach (EditedRule editedRules in request.EditedRules)
    {
            ...stored procedure call
    }
}

I've never used TransactionScope before, can someone please let me know if this code will work and will all my rows be rolled back.

I would also appreciate if there is a better approach to this.

Answer 1

Assuming that your stored procedure does not create and commit its own transaction, this code will work, with one change: your code needs to code scope.Complete() before the end of the using block; otherwise, the transaction is going to roll back.

using (TransactionScope scope = new TransactionScope()) {
    foreach (EditedRule editedRules in request.EditedRules) {
         ...stored procedure call
    }
    scope.Complete(); // <<== Add this line
}

The idea behind this construct is that the call of Complete will happen only if the block exits normally, i.e. there's no exception in processing the loop. If an exception is thrown, scope would detect it, and cause the transaction to roll back.

Answer 2

The only thing I would call out about your code is to make sure to do a scope.Complete() in order to commit the transaction:

using (TransactionScope scope = new TransactionScope())
{
   foreach (EditedRule editedRules in request.EditedRules)
   {
       // stored proc
   }

   scope.Complete();
}

if any exceptions occur during the using block, the transaction would be rolled back.