I've recently been using typed DataSets to talk to a database for the first time. Strange, I know, but I learned how to do database access using raw ADO.NET back in the 1.1 days, and never really bothered with the nice visual DataSet designers or the new TableAdapter helpers.

Anyway, I have a project here with a DataSet containing four tables (and hence four TableAdapter classes). When the client program sends the changes in the DataSet back to the server, I want to update the tables, but I want to do it in a transaction so that if any one thing fails, no changes are written to the database.

Turns out that this is non-trivial. Support for transactions is not built in to the generated TableAdapter classes. Luckily it's possible to implement it yourself using .NET 2.0's partial class feature.

Sahil has written a bit about how to do this if you just have the one TableAdapter. In his post, he talks about creating a BeginTransaction method on that TableAdapter. That works well, but if you have more than one, you need some way to share a transaction across all your adapters.

So here's my solution. Instead of a BeginTransaction method, I have implemented a Transaction property on my TableAdapters, like this:

partial class CategoriesTableAdapter 
{ 
    public SqlTransaction Transaction 
    { 
        get 
        { 
            return _adapter.SelectCommand.Transaction; 
        } 
        set 
        { 
            if (_adapter == null) InitAdapter(); 
            Connection = value.Connection; 
            _adapter.InsertCommand.Transaction = value; 
            _adapter.UpdateCommand.Transaction = value; 
            _adapter.DeleteCommand.Transaction = value;
        } 
    } 
}

As you can see, the property assigns the given transaction to the Transaction property on all its commands. This means I can do this in my Update method:

CategoriesTableAdapter catsTA = new CategoriesTableAdapter(); 
CategoryCodesTableAdapter codesTA = new CategoryCodesTableAdapter(); 
AllocationsTableAdapter allocsTA = new AllocationsTableAdapter(); 
CostsTableAdapter costsTA = new CostsTableAdapter(); 

catsTA.Connection.Open(); 
try 
{ 
    SqlTransaction txn = catsTA.Connection.BeginTransaction(); 
    try 
    { 
        catsTA.Transaction = txn;
        codesTA.Transaction = txn; 
        allocsTA.Transaction = txn; 
        costsTA.Transaction = txn; 

        // update each table, commit transaction or 
        // rollback if there's a problem

What do you think? It certainly seems to work. There's probably more to it ... like I should probably check that there's not already a Transaction set on the _adapter commands etc. It's doing the job for me, though, so I hope it helps someone else out there.