Transaction management using TransactionScope ()

Question

Transaction management using TransactionScope ()

I need to create a simple dotnet application that will call a stored procedure inside a loop (the stored procedure takes multiple parameters and adds them to the table). The requirement is that either all rows have been inserted or not.

For this I used:

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

I've never used it before TransactionScope

, can someone please tell me if this code will work and all my lines will be thrown back.

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

+3

c # .net sql-server asp.net

Amar Jul 20. 15 at 13:10

source to share

3 answers

The only thing I would like to mention about your code is to do this scope.Complete()

to commit a transaction:

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

   scope.Complete();
}

if any exceptions occur while using the block, the transaction will be rolled back.

+2

Kritner Jul 20. 15 at 13:16

source to share

I think this is what you are looking for:

using (var transaction = conn.BeginTransaction()) {
    try 
    {
       transaction.Commit();
    }  
    catch 
    {
        transaction.Rollback();
        throw;
    }
}

0

Stephen brickner Jul 20. 15 at 13:21

source to share

dasblinkenlight · Accepted Answer · 2015-07-20T13:15:25+0000

Assuming your stored procedure does not create or commit its own transaction, this code will work with one change: your code needs to be encoded scope.Complete()

to the end of the block using

; otherwise, the transaction will be rolled back.

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

The idea behind this construction is that the call Complete

will occur only if the block fails, i.e. there is no exception when processing the loop. If an exception is thrown, scope

it will detect it and cause the transaction to roll back.

Transaction management using TransactionScope ()

More articles: