.NET Answers

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.

Other Related Items:

Lock LacesTying your shoes is a thing of the past with the unique loop system and high-impact lightweight plastic cordlock. No more tying, retying, double knots... Read More >

Melissa and Doug Sunny Patch Bella Butterfly NetLearn, discover, and play with the Melissa and Doug Sunny Patch Bella Butterfly Net! This adorable bug catching toy features a butterfly-shaped mesh n... Read More >

Skinceuticals Serum 15 AOX+, 1-Ounce BottlePlease note: Due to packaging updates, the image shown may temporarily appear different f

Related Post

• Swapping the DAL Abstractly
• One Form with Multiple Tables
• Silverlight – Navigating Data
• SQL – Transactions
• Unable to find connection…for object web.config

Bookmark to:

Hide Sites

4 Responses to “Transaction Tracking Typed Datasets Using SqlTransaction”