Transaction Tracking Typed Datasets Using SqlTransaction


I recently had the need to implement transaction tracking between two separate databases on two separate servers.

Unfortunately, I could not be sure that DTC was implemented on either server, so using transaction tracking with the TransactionScope wasn’t an option.

I also wanted to be able to wire this in easily to the existing 3-tiered framework we are currently using so that what I ended up with could easily be implemented by other developers in a similar situation.

I’ll admit that transaction tracking is not something I normally have to do in my work.  So there was a bit of research involved.  Most of what I found was centered around older versions of .NET.

What I did eventually find was the following:

  • You create a SqlTransaction object from the connection object that will connect to the database.
  • The command objects in the table adapter share connections.  So I don’t have to worry about assigning the connection object to the various commands. But…
  • The transaction object needs to be assigned to any command objects that will be used as part of the transaction for that connection.
  • The command objects are not publicly accessible in table adapters, making it difficult to access them.
  • If you want the connection object to stay open so you have control over the transaction, you need to open it prior to calling a method that uses it in the table adapter.
  • Someone once came up with a TableAdapterHelper class that takes care of most of these problems, but it was written for an older version of .NET.  I’m using .NET 3.5 on this project so there was a bit of adaptation I needed to do.

So I took the main thought from the TableAdapterHelper class and implemented my own version.

First I decided I didn’t need all the various methods that were in the original code.  You may need to modify what I’m about to show you for your own purposes, but I just wanted to call one method that would start the transaction.

I also found that the original code didn’t allow me to attach the transaction to the Update, Delete, and Insert objects, so I needed to add additional code to address that issue.

What follows is the entirety of the class:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

public class TableAdapterHelper
    /// <summary>
    /// Enlists the table adapter in a transaction.
    /// </summary>
    public static System.Data.SqlClient.SqlTransaction
        BeginTransaction(object tableAdapter)
        System.Data.SqlClient.SqlConnection connection =
        SqlTransaction transaction =
        // get the table adapter's type
        Type type = tableAdapter.GetType();

        // set the transaction on each command
        // in the adapter
        PropertyInfo commandsProperty =
            BindingFlags.NonPublic |
        SqlCommand[] commands =
            .GetValue(tableAdapter, null);
        foreach (SqlCommand _command in commands)
             _command.Transaction = transaction;

        PropertyInfo adapterPI =
                BindingFlags.NonPublic |
        if (adapterPI != null)
            object innerAdapter =
                adapterPI.GetValue(tableAdapter, null);
            if (((SqlDataAdapter)innerAdapter)
                .UpdateCommand != null)
                     .UpdateCommand.Transaction =
                .InsertCommand != null)
                     .InsertCommand.Transaction =
            if (((SqlDataAdapter)innerAdapter)
                .DeleteCommand != null)
                    .DeleteCommand.Transaction =

        return transaction;

    private static SqlConnection
        GetConnection(object tableAdapter)
        Type type =
        PropertyInfo connectionProperty =
            BindingFlags.NonPublic |
        SqlConnection connection =
                .GetValue(tableAdapter, null);
        return connection;
And a simple example of how to use it:
SqlTransaction transaction =
catch (Exception e)
    throw e;

My actual code has two transactions and two adapters because I’m doing the delete to two separate databases and I need to keep everything in sync.

Related Post

4 Responses to “Transaction Tracking Typed Datasets Using SqlTransaction”

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail