Combining Transactions with Strongly Typed DataSets

I was thinking down the lines of using a transactionScope to achieve what I wanted but it just did not fit my needs. In my projects I create a presentation layer, a Business Object Layer and also a Data Access Layer which is a Strongly Typed DataSet.

In some procedures I need to make three different updates using different table adapters and if one fails say the last, I don't really want the other data to have worked. I want everything to have worked or nothing.

The solution I found to it was here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237800&SiteID=1

All I want to do here is give you a C# version of it which I have done and which also solved my problem. Here is the code:

public void AssignTransaction(ref System.Data.SqlClient.SqlTransaction transaction)
{
            Adapter.InsertCommand.Transaction = transaction;
            Adapter.InsertCommand.Connection = transaction.Connection;
            Adapter.UpdateCommand.Transaction = transaction;
            Adapter.UpdateCommand.Connection = transaction.Connection;
            Adapter.DeleteCommand.Transaction = transaction;
            Adapter.DeleteCommand.Connection = transaction.Connection;
}

We pass in the transaction by reference so all use the same copy of the object and in turn committing all the actions to one transaction.

One donwside I see to this is the following. Adapter is private, and also, every times your dataset changes your code disappears, as it recompiles its own code, thus losing yours. And going back to this, the way is simple.

Add a codefile to your project and add the relevant namespace which your TableAdapter resides inside. You then extend the partial class inside this code file to give you the extra functionality. e.g.

namespace DatasetTableAdapters
{
    public partial class TableAdapter
    {
        public void AssignTransaction(ref System.Data.SqlClient.SqlTransaction transaction)
        {
            Adapter.InsertCommand.Transaction = transaction;
            Adapter.InsertCommand.Connection = transaction.Connection;
            Adapter.UpdateCommand.Transaction = transaction;
            Adapter.UpdateCommand.Connection = transaction.Connection;
            Adapter.DeleteCommand.Transaction = transaction;
            Adapter.DeleteCommand.Connection = transaction.Connection;
        }
    }
}