Fast transactions with System.Transactions and Microsoft SQL Server 2000

The simplest way to use transactions today with Microsoft SQL Server 2000, using .Net Framework 2.0, is as follows:

       static void Main(string[] args)
            using (TransactionScope ts = new TransactionScope())
                SqlConnection sqlConnection = new SqlConnection(“connectionString”);
                SqlCommand sqlCommand = new SqlCommand(“INSERT INTO …”, sqlConnection);


If you really care about speed, you will notice that the performance decreases compared to when transactions are not used. If you do a little bit of investigation, you will notice that in fact a MSDTC distributed transaction is created and used when the code is executed (you can see this using Component Services snap-in). But why?

If you run the same code against Microsoft SQL Server 2005, the performance doesn’t decrease when compared to a similar code that doesn’t use transactions. Good, but you might not have SQL Server 2005 (yet). So, what can you do?

Let’s first explain the “why”. In order to take advantage of lightweight transaction manager (or LTM) that comes with System.Transactions, your durable resource manager, or database in this case, needs to support a mechanism called “promotable transactions”. I talked about how this can be accomplished at Since currently only SQL Server 2005 supports promotable transactions, when you use System.Transactions with SQL Server 2000, the lightweight transaction needs to be transformed into a MSDTC transaction, because this is the distributed transaction type that SQL Server 2000 understands. Having an MSDTC transaction involved, means there is some additional cost, and that is why you are seeing the perf hit. Just to make sure we are on sync on this, the perf hit is only when you compare it to the scenario that is not using transactions. If you compare System.Transactions with EnterpriseServices/COM+ scenarios using transactions, the perf is improved with System.Transactions.

And now, let’s go over on what can you do, if you want to use System.Transactions with no perf hit, in a scenario where you only talk to a database server (Microsoft SQL Server 2000) and you might also involve volatile transacted resources, like a transacted hashtable. The solution is to use an “adapter” that enlists with the System.Transactions transaction using PSPE ( and coordinates the connection to the SQL Server using a SQL “local transaction”. The code will have to look like this:

        static void Main(string[] args)
            using (TransactionScope ts = new TransactionScope())
                SqlConnection sqlConnection = new SqlConnection(“connectionString;Enlist=false“);               
DatabaseTransactionAdapter dbAdapter = new DatabaseTransactionAdapter(sqlConnection);
                SqlCommand sqlCommand = new SqlCommand(“INSERT INTO …”, sqlConnection);
sqlCommand.Transaction = (SqlTransaction)dbAdapter.Transaction;


In addition to the changes/additions in bold, you should also observe that “sqlConnection.Close();” was removed.

Now let’s dive into the details of the adapter. First, since the adapter will handle the connection to the SQL Server, you will have to specify in the connection string “Enlist=false”, thus telling to the SQL client to not enlist in the transaction, because this will determine the creation of a MSDTC transaction for reasons mentioned above. And you also must not close the connection, because the connection should stay open until the transaction is completed, which happens after the “using” statement ends. The adapter will take ownership of the connection lifetime and close it when it is done with it.

When Begin is called on the adapter, the adapter will enlist with the System.Transactions transaction, in other words, Transaction.Current, using EnlistPromotableSinglePhase. Later, LTM will call Initialize on the enlistment interface, and that is the time when the bridge to the SQL Server is established; the adapter will start an internal SQL transaction on the connection provided in the constructor using SqlConnection.BeginTransaction().

An additional step that might look unnecessary, at least for me, is that you need to set the internal SQL transaction from the connection to the sqlCommand.Transaction property. Manually. Why SqlCommand can’t get that automatically from the SqlConnection object, I don’t know. Maybe an expert in SQL objects can jump in and explain. And that is why the adapter needs to publish the SQL transaction in a property.

When the transaction completes, after exiting the using statement, LTM will notify the adapter through the IPromotableSinglePhaseNotification interface to commit or abort the transaction. Consequenlty the adapter will commit or abort the internal SQL transaction.

John Doty from my team, created a set of classes that does exactly this (the adapter works for both resource managers using IDbConnection and the MSMQ resource manager). They are available for download at The installer will expand a TransactionAdapter.cs in C:\Documents and Settings\<currentUser>\My Documents\MSDN\Private Transaction Adapter. All you have to do is link the file to your project and use the adapter as described above.

Comments (19)

  1. Sami says:

    Hello Florin,

    Im trying to get the PDC dlinq samples work with TransactionScope object, the idea is to build a case study showing the whole Indigo+tx flow+dlinq thing. But it seems that the bits provided with dlink are only targeting Sql 2005 Express Edition. When I run this code with Sql 2000, MsDTC always rollback the current transaction with no message.

    // AFTER

    ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p);

    ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p);

    Display the correct row data, but the table is not consistent with this message.

    Any idea?

  2. Sami says:

    Ok I found the problem. PDC Dlinq samples don’t work because they lack of ts.Complete() statement.

  3. Vikas Jindal says:

    Really Fantastic

  4. Tomas Carlsson says:


    Thank you fore a good artickle.

    I would like to make a distributed transaction over two databases (and be able to role back if anything goes wrong).

    Is this possible with SQL Server 2000 and this pattern? I don’t like to "mess up" my code with EnterproceServices and COM+.

    I have tried to modify your code-example with two connections but I get an exeption that says "Cannot enlist in a distributed transaction", se my code below. What am I doing wrong?

    Thank you for helping me 😉


    string connStrAar1Reg1 = GemFunktioner.AnslutningAarRegion(AAR_1);

    connStrAar1Reg1 += "Enlist=false";

    m_connAar1 = new SqlConnection(connStrAar1Reg1);

    m_dbAdapterAar1 = new DatabaseTransactionAdapter(m_connAar1);


    string connStrAar2Reg1 = GemFunktioner.AnslutningAarRegion(AAR_2);

    connStrAar2Reg1 += "Enlist=false";

    m_connAar2 = new SqlConnection(connStrAar2Reg1);

    m_dbAdapterAar2 = new DatabaseTransactionAdapter(m_connAar2);



    m_dbAdapterAar2.Begin(); // Here I get the exeption "Cannot enlist in a distributed transaction"

  5. florinlazar says:

    To: Tomas Carlsson

    Tomas, when you have 2 database connections, you don’t have to use this adapter, since you can’t avoid using the DTC transaction in this scenario. Simply use the normal pattern for TransactionScope and you will be all set.

  6. Rob Steele says:

    Will this pattern work with Oracle 9.x or 10.x? Are you aware of any issues? I am utilizing the ODP.Net provided by Oracle.

  7. florinlazar says:

    To: Rob Steele

    If Oracle supports internal transactions, you should be able to change the adapter to work with ODP.Net.

  8. I tried John Doty’s sample TransactionAdapter with mixed results:

    – where I had transactions that mixed TransactionScopeOption.Required with TransactionScopeOption.Suppress operations, the adapter doesn’t support transactions operating in TransactionScopeOption.Suppress mode. I changed all of these instances to use TransactionScopeOption.Required and that seemed to keep it happy.

    – next, I started getting errors when running transactions that involved the work of multiple data components, where each data component would (a) connect to the database and then (b) do some work.

    In this last case, it was trying to enlist the current transaction more than once (and hence failed). Also, I could see from the SQL 2000 trace that when each data component created a connection a NEW connection was being created rather than some resource dispenser detectng that a transaction was active and re-using the current transaction.

    My sole reasons for wanting to use John’s example was to eliminate the need for the MSDTC on this single-database SQL 2000 application (as use of the MSDTC violates my client’s corporate security policies, and it is not great for performance reasons either).

    Looks like I’ll need to hunt around for another solution (unless anyone else has resolved this problem already ??).

  9. Joe Egan says:

    …perfect solution as I migrate to CSLA .NET 2.0 but stick with SQL Server 2000 for a while.  Thanks!

  10. Javed says:

    I am having difficulty getting the DataBaseTransactionAdapter to work. It was easy enough to use. Everything seems to go smoothly but when the routine exits, I get an exception “Transaction was aborted”. I do indeed call ts.Complete right after .ExecuteNonQuery. The method executes without problem, but back in the calling routine the Try…Catch block shows up with the about exception.

  11. Avi_H says:

    Could you please post a sample for it ?


  12. Fyodor Sheremetyev says:

    To: Richard Purchas
    Have you found any other solution?

  13. Florin,

    The reason you have to set the transaction manually is because the SqlConnection object has been explicitly excluded from it using the “EnList=false” parameter.

  14. Liviu Uba says:

    I struggle with Transactions and Dataset designer generated datasets for a while, and I have only headaches…

    DatabaseTransactionalAdapter is just awesome.

    I have written a small wrapper for the DataTableAdapters generated by Dataset Designer:

    Adapter with implicit conversion to T where T si DataTableAdapter. the reason: setting of the transaction:

    DatabaseTransactionAdapter dta = new DatabaseTransactionAdapter(conn);

    PersonTableAdapter p1 = new Adapter(dta.Transaction);


  15. Liviu Uba says:

    Thanx. My previous post did not show up, but anyway I want to thank you, because based on your adapter and custom code generation that extends the dataset designer code I can write now following and it rocks!! :

    Data data = new Data();
    using (Db.ReadCommitted)
    data.Document.GetDataById(5456454, 1);
    data.Document.DataRows(0).FreeTxt = Guid.NewGuid();

  16. Manoj says:

    I tried to use the TransactionAdapter with the following test code. The code fails at the adapter.Begin() inside the TxnInner method below. However, if I use TransactionScopeOption RequiresNew for inner transaction the code works winhout problem.

    using System;
    using System.Data;
    using System.Transactions;
    using System.Data.SqlClient;

    namespace Test
    public class TxnTest
    const string connectionString = “…….;enlist=false”;
    const string sql = “select * from authors”;

    private static void TxnInner()
    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
    SqlConnection connection = new SqlConnection(connectionString);
    SqlCommand command = new SqlCommand(sql, connection);

    DatabaseTransactionAdapter adapter = new DatabaseTransactionAdapter(connection);
    adapter.Begin();// Fails here if TransactionScopeOption is Required
    command.Transaction = (SqlTransaction)adapter.Transaction;



    public static void TxnOuter()
    using (TransactionScope scope = new TransactionScope())
    SqlConnection connection = new SqlConnection(connectionString);
    DatabaseTransactionAdapter adapter = new DatabaseTransactionAdapter(connection);

    SqlCommand command = new SqlCommand(sql, connection);

    command.Transaction = (SqlTransaction)adapter.Transaction;



    public static void DoTest()

  17. florinlazar says:

    To: Richard Purchas

    For reusing the connection, you might also take a look at the ConnectionScope class posted at

  18. Muralidhar says:

    I hav a diff problem. Am using 1.1 with sql server 2000 and Auto enlisting. Transactions are implemented thru Enterprise components.

    At regular intervals I am getting "An error occurred while enlisting in a distributed transaction" error. And when I run the same activity agains it succeds without any error..

    Can u show some thoght into this.. Thanks

  19. florinlazar says:

    To: Muralidhar

    For this sort of issues, I recommend posting at the Transactions Forum at

    Or if it is critical for your business, you should contact Microsoft Support.