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();
----------------------------------------------------------------------------------------

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

Mana

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();
            }
        }
    }

OSQL UserName and PWd
Nice exceptions.
Writing update back to MSAccess table?
How do I copy datatable from one dataset to another.
Username & Password as parameters
Caching Help Needed
Problems when writing a SMTP client and talk with the hotmail
How to shutdown open network ports?
unsafe bug ?
How to Convert SHDocVw.WebBrowser to System.Windows.Forms.WebBrowser