Click here to Skip to main content
15,167,809 members
Articles / Programming Languages / C#
Posted 16 Nov 2006


24 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
3.82/5 (5 votes)
16 Nov 2006CPOL2 min read
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.


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.


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



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

using (TransactionScope transaction = new TransactionScope())


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

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");
                this._transaction = transaction;
                Adapter.UpdateCommand.Transaction = _transaction;
                Adapter.InsertCommand.Transaction = _transaction;
                Adapter.DeleteCommand.Transaction = _transaction;

And now you're set!


  • 16th November, 2006: Initial post


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


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 Pin
JohannesAbt5-Mar-08 7:32
MemberJohannesAbt5-Mar-08 7:32 
GeneralBug Pin
dojohansen5-Mar-07 4:54
Memberdojohansen5-Mar-07 4:54 
GeneralRe: Bug Pin
dojohansen5-Mar-07 4:57
Memberdojohansen5-Mar-07 4:57 
GeneralReply to Norm Pin
Neil Barnwell22-Jan-07 2:13
MemberNeil Barnwell22-Jan-07 2:13 
GeneralRe: Reply to Norm Pin
liscio11-May-07 5:56
Memberliscio11-May-07 5:56 
GeneralRe: Reply to Norm Pin
jcrussell8-Oct-07 15:59
Memberjcrussell8-Oct-07 15:59 
General...or you can do this Pin
Stefan Knoll23-Nov-06 5:03
MemberStefan Knoll23-Nov-06 5:03 
GeneralRe: ...or you can do this Pin
dojohansen5-Mar-07 5:03
Memberdojohansen5-Mar-07 5:03 
GeneralAnother way... Pin
patgrape16-Nov-06 16:59
Memberpatgrape16-Nov-06 16:59 
GeneralRe: Another way... Pin
fhtino11-Oct-07 21:57
Memberfhtino11-Oct-07 21:57 

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

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