Entity Framework: How to put multiple stored procedures in a transaction?

Question

I did a lot search already but couldn't find a straight anwser.

I have two stored procedures and they both were function imported to the DBContext object

1. InsertA()
2. InsertB()

I want to put them in a transaction. (i.e. if InsertB() failed, rolled back InsertA())

How do I do that? Can I just declare a TransactionScope object and wrap around the two stored procedures?

Thanks

Answer 1

You need to enlist your operations in a transaction scope, as follows:

using(TransactionScope tranScope = new TransactionScope()) 
{
  InsertA();
  InsertB();

  tranScope.Complete();
}

On error, the transaction scope will automatically be rolled back. Of course, you still need to handle exceptions and do whatever your exception handling design dictates (log, etc). But unless you manually call Complete(), the transaction is rolled back when the using scope ends.

The transaction scope will not be promoted to a distributed transaction unless you open other database connections in the same transaction scope (see here).

This is important to know because otherwise you would need to configure MSDTC on all your servers involved in this operation (web, middle tier eventually, sql server). So, as long as the transaction isn't promoted to a distributed one, you'll be fine.

Note: In order to fine-tune your transaction options, such as timeouts and isolation levels, have a look at this TransactionScope constructor. Default isolation level is serializable.

Additional sample: here.