Click here to Skip to main content
Click here to Skip to main content

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

, 16 Nov 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
An article describing a simple way to provide transaction support to an application designed using the DataSet wizard, and avoiding the DTC requirement with using TransactionScope 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):
    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

  • 16th November, 2006: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Neil Barnwell
Web Developer
United Kingdom United Kingdom
I started out as a Visual Basic 6 hobbyist, before moving into Java after I decided (rightly or wrongly) that it would be a better move than C++. Eventually though, I settled on .NET and am now a C# 2.0 developer for a logistics firm in the UK, working with a mixture of desktop and web environments.

Comments and Discussions

 
GeneralTransaction for SELECT PinmemberJohannesAbt5-Mar-08 7:32 
GeneralBug Pinmemberdojohansen5-Mar-07 4:54 
GeneralRe: Bug Pinmemberdojohansen5-Mar-07 4:57 
GeneralReply to Norm PinmemberShipcreak22-Jan-07 2:13 
GeneralRe: Reply to Norm Pinmemberliscio11-May-07 5:56 
GeneralRe: Reply to Norm Pinmemberjcrussell8-Oct-07 15:59 
General...or you can do this PinmemberStefan Knoll23-Nov-06 5:03 
GeneralRe: ...or you can do this Pinmemberdojohansen5-Mar-07 5:03 
GeneralAnother way... Pinmemberpatgrape16-Nov-06 16:59 
GeneralRe: Another way... Pinmemberfhtino11-Oct-07 21:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 16 Nov 2006
Article Copyright 2006 by Neil Barnwell
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid