Nested Transactions in ADO.NET

Question

First, is it possible to have n transactions levels over ADO.Net. Second, is this correct usage?

        var tx = cx.BeginTransaction();

        cx.Execute("insert into atable(id) values(123123)");

        var tx2=tx.BeginTransaction();

        cx.Execute("insert into atable(id) values(123127)");

        tx2.Commit();

        tx.Commit();

... etc.

Answer 1

You can nest transactions using TransactionScope - however, they will only get committed once the most outer one gets committed.

They will all be rolled back if any one of them will rollback.

In terms of usage - you should wrap the transaction creation in using statements to ensure proper disposal.

using(var tx1 = new TransactionScope())
{
   cx.Execute("insert into atable(id) values(123123)");

   using(var tx2 = new TransactionScope())
   {
        cx.Execute("insert into atable(id) values(123127)");
        tx2.Complete();
   }

   tx1.Complete()
}