Implementing Nested Transaction in C#

[Mana]

Hi,

I want to implement nested transactions in C#.
When I write BEGIN TRANSACTION inside another BEGIN TRANSACTION in an
SQL Script it works fine. But when I call BeginTransactio n() inside
another BeginTransactio n() in a c# code on same connection object it
throws exception as "SQLConnect ion doesnot support parallel
transaction".

Following is the code snippet that i have written.
----------------------------------------------------------------------------------------
// Create and open a connection
SQLConnection connection = new SqlConnection(" Initial
Catalog=mydatab ase; Data Source=mymachin e;Integrated Security=SSPI;" );
connection.Open ();

// Begin Outer Transaction
SQLTransaction transaction = connection.Begi nTransaction();

SQLTransaction innerTransactio n = null;
for (int i = 0; i < messageCount; ++i)
{
innerTransactio n = connection.Begi nTransaction();
// Do some stuff here eg execute update query
innerTransactio n.Commit();
}

// Commit outer transaction and close the connection
transaction.Com mit();
connection.Clos e();
----------------------------------------------------------------------------------------

Can anybody tell me how to implement nested transactions C#.

Mana

[Marc Gravell]

This is acting as a save-point, yes? In which case, you could perhaps use
outerTransactio n.Save, probably with a fixed name each time so that you can
also use outerTransactio n.Rollback(theN ame) - but unless you are actually
using save-point rollback functionality, committing the inner transaction
doesn't do a whole lot anyway... so I'm guessing that in reality this is
from more complex code where the inner transaction is actually in a
sub-method that must work both inside and outside of an existing
transaction?

Well, if you are using 2.0, then how about using a TransactionScop e
(System.Transac tions assembly)? This deals with nesting for you even across
nested methods, and also supports distributed transactions. Note that unless
you use Sql-Server 2005 this will automatically create a DTC transaction
even for calls to a single database [on 2005 it uses the LTM to start with a
basic transaction and then promote to DTC if necessary].

In the following, the Commit() methods can be called either on individual
objects or the list, and should work happily with full transaction support
(e.g. where SomeObject.Comm it() executes multiple database commands):

class SomeObjectList : List<SomeObject >{
public void Commit() {
using (TransactionSco pe tran = new TransactionScop e()) {
foreach (SomeObject obj in this) {
obj.Commit();
}
tran.Complete() ;
}
}
}
class SomeObject {
public void Commit() {
using (TransactionSco pe tran = new TransactionScop e()) {
// obtain connection from pool and do some work
tran.Complete() ;
}
}
}