C#: Avoiding DTC with Typed DataSets and SQL Server 2000

Introduction

This article describes a means of enabling transaction support with typed datasets in C# 2.0 and SQL Server 2000, without resorting to System.Transactions.TransationScope and therefore Distributed Transaction Coordination.

Background

I created an application for a customer that needed to import data from a spreadsheet into our SQL Server 2000 database. I wanted this performed in a transaction, but found that the only method appeared to be to use TransactionScope, which would require DTC to be present, and would use more resources than necessary. I needed a simple way to enhance the DataSet code generated by the Visual Studio 2005 DataSet editor, so that I could enlist the generated data adapters in a transaction.

I found that because the generated adapters inherit System.ComponentModel.Component instead of DataAdapter, I didn't have access to the UpdteCommand, InsertCommand, or DeleteCommand in order to set the transaction property of each. I had to find another way.

The Problem in Code

You simply can't do the following:

using (SqlTransaction transaction = connection.BeginTransaction())
{
    // The following won’t compile because adapter1 and adapter2 
    // inherit from System.ComponentModel.Component, not DataAdapter
    adapter1.UpdateCommand.Transaction = transaction;
    adapter2.UpdateCommand.Transaction = transaction;
    // Also set transaction on insert and delete commands here

    adapter1.Update(table1);
    adapter2.Update(table2);

    transaction.Commit();
}

I could have used System.Transactions, which makes things much simpler:

using (TransactionScope transaction = new TransactionScope())
{
    adapter1.Update(table1);
    adapter2.Update(table2);

    transaction.Complete();
}

But since we're using SQL Server 2000, this will be automatically promoted to a Distributed Transaction, requiring the Distributed Transaction Coordinator service to be running on the server, and using up more resources in the process. SQL Server 2005 has support for working out whether a transaction should be promoted to a distributed transaction or not, so when we are using 2005, System.Transactions.TransactionScope should probably be the way to go since it’s simpler, and has no additional overhead.

The Solution in Code

I couldn't find a documented method other than System.Transactions to solve this, so I got round it by extending the VS-generated dataset so that each adapter for which I wanted transaction control had an EnlistTransaction method. This is simply adding a back door to the Command objects. I basically made the generated DataSet a partial class and added the new methods in the newly created file, so that regeneration by adding columns, etc. won’t break it.

My first example now becomes:

using (SqlTransaction transaction = connection.BeginTransaction())
{
       // These methods will update all relevant command objects’ transaction property
       adapter1.EnlistTransaction(transaction);
       adapter2.EnlistTransaction(transaction);
 
       adapter1.Update(table1);
       adapter2.Update(table2);
 
       transaction.Commit();
}

Points of Interest

Here are the actual steps to modifying the generated dataset. The trick is to make it a partial class, as follows:

1. Select your dataset in the Solution Explorer, and click View Code. This will automatically create the additional code file.
2. Create the partial classes for the dataadapters you wish to provide transaction support to.
3. Add the following code (replace names as necessary):
   C#

   public partial class [TableAdapterName]
   {
       public void EnlistTransaction(System.Data.SqlClient.SqlTransaction transaction)
       {
           System.Data.SqlClient.SqlTransaction _transaction;
           
           if (this._transaction != null)
           {
               throw new System.InvalidOperationException
   		("This adapter has already been enlisted in a transaction");
           }
           else
           {
               this._transaction = transaction;
               Adapter.UpdateCommand.Transaction = _transaction;
               Adapter.InsertCommand.Transaction = _transaction;
               Adapter.DeleteCommand.Transaction = _transaction;
           }
       }
   }

And now you're set!

History

• 16^th November, 2006: Initial post