Nested Transactions

Hello! Sorry if I choose wrong forum for this post.
I have next scenario:

Transaction1

Transaction2

Commit Transaction2
Transaction3

Commit Transaction3

Commit Transaction1 I wanna implement it in C# code (.NET 1.1, MS SQL 2000):

IDbConnection connection = new OleDbConnection(connectionString);

IDbTransaction transaction = null;

connection.Open();

/* NOTE: I can't use something like this:

* transaction outter = connection.BeginTransaction();

* transacrion inner = connection.BeginTransaction();

* // Here I'm getting an error: OleDB doesn't support parallel transactions,

* // though I wanna create nested one.

*/

// So, I decided to turn implicit transactions mode on in hope it should help:

IDbCommand bt = connection.CreateCommand();

bt.CommandText = " SET IMPLICIT_TRANSACTIONS ON; BEGIN TRANSACTION;";

bt.ExecuteNonQuery();

transaction = connection.BeginTransaction();

IDbCommand command = connection.CreateCommand();

command.Transaction = transaction;

command.CommandType = CommandType.Text;

command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description');";

command.ExecuteNonQuery();

command.CommandText = "SELECT @.@.TRANCOUNT;";

int transCount = (int)command.ExecuteScalar(); // It's equal to 2 here, seems to be OK.

transaction.Commit();

// Let's start the second "nested" transaction

IDbTransaction transaction1 = connection.BeginTransaction();

IDbCommand command1 = connection.CreateCommand();

command1.Transaction = transaction1;

command1.CommandType = CommandType.Text;

command1.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description');";

command1.ExecuteNonQuery();

command1.CommandText = " SELECT @.@.TRANCOUNT; ";

transCount = (int)command1.ExecuteScalar(); // WOW! Now it's already equal to 1 here.

transaction1.Commit();

// Well, here I wanna close outter transaction, but... I'll get exception: There is nothing to commit here

bt = connection.CreateCommand();

bt.CommandText = "Commit TRANSACTION";

bt.ExecuteNonQuery();

Well, I know that SQL Server has no support for nested transactions. Nesting of transactions only increments @.@.TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. And I can't use the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept.

Please help me: How can I implement required operations?

You could try using a ServicedComponent and using COM+ functionalities to perform that type of transactions. I don't know if it will work well with OleDB - you just have to try it.|||

Thank you Miguelb for reply. I found solution much simpler :). If somebody find it helpful this is it:

public static void InitiateTransactionsChain(string connectionString)
{
using (IDbConnection connection = new OleDbConnection(connectionString))
{
IDbCommand mostOutter = null;
connection.Open();
try
{
connection.Open();

mostOutter = connection.CreateCommand();
mostOutter.CommandText = "SET IMPLICIT_TRANSACTIONS ON;"; // There is no BEGIN TRANSACTION here
mostOutter.ExecuteNonQuery();
ExecuteNestedTrans(connection, 100, "help me"); // see below
ExecuteNestedTrans(connection, 101, "hope it is OK"); // see below
// If any exception occurs previous transactions will be rolled back!
// ExecuteNestedTrans(connection, 102, null); // see below
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "Commit TRANSACTION";
mostOutter.ExecuteNonQuery();
}
catch (Exception ex)
{
// if something goes wrong, we can easy roll back everything :
if (mostOutter == null)
return;
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "ROLLBACK TRANSACTION";
mostOutter.ExecuteScalar(); // That's all...
}
}
}
// Here is ExecuteNestedTrans:
public static void ExecuteNestedTrans(IDbConnection connection, int value, string description)
{
IDbCommand fakeTransaction = null;
// Let's cheat here: Increase the counter (@.@.TRANCOUNT):
fakeTransaction = connection.CreateCommand();
fakeTransaction.CommandText = "BEGIN TRANSACTION;";
fakeTransaction.ExecuteNonQuery();

IDbTransaction innerTransaction = null;
try
{
innerTransaction = connection.BeginTransaction();
IDbCommand command = connection.CreateCommand();
// Somewhere here transcount somehow will be decremented by 1
// Strange, isn't it?
command.Transaction = innerTransaction;
command.CommandType = CommandType.Text;
command.CommandText = String.Format("Insert into Region (RegionID, RegionDescription) VALUES ({0}, '{1}');", value, description);
command.ExecuteNonQuery();
innerTransaction.Commit();
}
catch (Exception exInner)
{
if (innerTransaction != null)
{
innerTransaction.Rollback();
throw exInner;
}
}
// NOTE: There is no need to commit fakeTransaction.
// It will be commited by something somewhere deep in .NET
// That is why code below is commented
//fakeTransaction = connection.CreateCommand();
//fakeTransaction.CommandText = "COMMIT TRANSACTION;";
//fakeTransaction.ExecuteNonQuery();
if (description == null)
throw new Exception("He-he!");
}

That's all :). Thanks for the time You spent for me.