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 BeginTransaction() inside
another BeginTransaction() in a c# code on same connection object it
throws exception as "SQLConnection doesnot support parallel
transaction".

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

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

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

// Commit outer transaction and close the connection
transaction.Commit();
connection.Close();

 

[Marc Gravell]

This is acting as a save-point, yes? In which case, you could perhaps use
outerTransaction.Save, probably with a fixed name each time so that you can
also use outerTransaction.Rollback(theName) - 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 TransactionScope
(System.Transactions 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.Commit() executes multiple database commands):

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