Transaction Tracking Typed Datasets Using SqlTransaction

misc_vol2_009

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 =
            GetConnection(tableAdapter);
        connection.Open();
        SqlTransaction transaction =
            connection.BeginTransaction();
        // get the table adapter's type
        Type type = tableAdapter.GetType();

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

        PropertyInfo adapterPI =
            type.GetProperty("Adapter",
                BindingFlags.NonPublic |
                BindingFlags.Instance);
        if (adapterPI != null)
        {
            object innerAdapter =
                adapterPI.GetValue(tableAdapter, null);
            if (((SqlDataAdapter)innerAdapter)
                .UpdateCommand != null)
                ((SqlDataAdapter)innerAdapter)
                     .UpdateCommand.Transaction =
                     transaction;
            if(((SqlDataAdapter)innerAdapter)
                .InsertCommand != null)
                ((SqlDataAdapter)innerAdapter)
                     .InsertCommand.Transaction =
                     transaction;
            if (((SqlDataAdapter)innerAdapter)
                .DeleteCommand != null)
                ((SqlDataAdapter)innerAdapter)
                    .DeleteCommand.Transaction =
                    transaction;

        }
        return transaction;
    }

    private static SqlConnection
        GetConnection(object tableAdapter)
    {
        Type type =
            tableAdapter.GetType();
        PropertyInfo connectionProperty =
            type.GetProperty("Connection",
            BindingFlags.NonPublic |
            BindingFlags.Instance);
        SqlConnection connection =
            (SqlConnection)connectionProperty
                .GetValue(tableAdapter, null);
        return connection;
    }
}
And a simple example of how to use it:
SqlTransaction transaction =
    TableAdapterHelper.BeginTransaction(Adapter);
try
{
    Adapter.Delete(id);
}
catch (Exception e)
{
    transaction.Rollback();
    Adapter.Connection.Close();
    throw e;
}
transaction.Commit();
Adapter.Connection.Close();


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

Bear