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

  • Swapping the DAL AbstractlySwapping the DAL Abstractly Last Thursday, we looked at how to replace the TableAdapter with our own TableAdapter as a way or eliminating our dependence on Microsoft SQL.  The problem with this implementation is that eve...
  • SQL – TransactionsSQL – Transactions Ruth ask, How do I do transaction tracking within a stored procedure? Hopefully, if you’ve been programming for any length of time against databases, you know that if you are updating mult...
  • Unable to find connection…for object web.configUnable to find connection…for object web.config I've seen this error a couple of different times.  Mostly from other people calling me with the problem.  So I still don't know what ultimately causes the problem.  But if you're having this probl...
  • One Form with Multiple TablesOne Form with Multiple Tables I received the following question a few days ago: “I am trying to use three tables from the same dataset in one form that I am creating in vs.net winforms my problem I guess is the setup of it as ...
  • DataSets, TableAdapters, and Transient Retry Logic For SqlAzureDataSets, TableAdapters, and Transient Retry Logic For SqlAzure The main project I’m working on these days is moving several web sites to Azure.  It is something I’ve wanted to be able to try for a while.  I’m working with several other agencies on this project...
  • Pingback: Dew Drop – July 21, 2010 | Alvin Ashcraft's Morning Dew

  • http://www.musclecarts.com Glenn Forrest

    Your’e a lifesaver. Ran into something very similar to this today and this post is exactly what the doctor ordered. Thanks!

    • http://www.denatasarim.com John Alden

      I agree… Thanks a lot Dave! This post saved my life :)

  • http://www.outofstress.com Iwrite

    I had faced a similar situation while trying to transact between two databases located on different servers, I couldn’t figure out the issue and so I dropped the project. Wish I had run into your post before.