Long story short, TransactionScope is too complicated right now to actually use in normal development. So I wanted to stick with the traditional transaction support.

First of all, you need to make sure all of your TableAdapters use the same Connection object. TableAdapters are not an actual class in ADO.Net, they are code generated classes, kind of like typed DataSets, but unlike typed DataSets they dont inherit from some common TableAdapter class, they just inherit from Component. However, they do expose a Connection property, so I use that to hook up all my TableAdapters to the same Connection object. Something like this:

void InitTableAdapters( SqlConnection conn )
{
  taReportSetGroupAccountNotAssigned.Connection = conn;
  taReportSetGroupAccount.Connection = conn;
  taReportSetGroup.Connection = conn;
  taReportSet.Connection = conn;
}

Now, you can encapsuluate your database operations in a transaction as follows:

SqlConnection conn = new SqlConnection( connStr );

InitTableAdapters( conn );

conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
  // manipulate data

  tran.Commit();
}
catch()
{
  tran.Rollback();
}
finally
{
  conn.Close();
}

However, there is still a problem with this. The SqlCommands inside the TableAdapter need to use the transaction of the Connection object in the Table Adapter, but the generated code does not do that for you. And you don't want to start messing with the generated code, or your changes will be wiped out next time it is regenerated. What to do?

First I tried subclassing the generated TabeAdapter, as I saw there was a protected CommandCollection property. However, it turned out that this collection only contains the Select Command, and I wanted the Insert, Update and Delete commands to participate in the transaction. These are part of the SqlDataAdapter member of the TableAdapter, which is private. Duh!

Well, partial classes come to the rescue! The TableAdapter class is generated as a partial class, which allows you to extend it by adding in additional methods in your own partial class. One gotcha is that you need to use the same namespace for your partial class as for the generated partial class, and that namespace is actually not the same as that of the typed dataset. It has .XXXTableAdapters added on to the end of it, where XXX is the name of your typed dataset. For instance, if your dataset namespace is Falafel.Data, and its name is FalafelDataset, then your TableAdapter namespace will be Falafel.Data.FalafelDatasetTableAdapters.

Armed with this knowledge, we can extend the partial class of each TableAdapter as follows:

namespace Falafel.Data.FalafelDataSetTableAdapters
{
  public partial class ReportSetGroupAccountTableAdapter
  {
    public void JoinTransaction( SqlTransaction tran )
    {  
      TransactionHelper.JoinTransaction(_adapter, tran);
    }
  }
  public partial class ReportSetGroupTableAdapter
  {
    public void JoinTransaction( SqlTransaction tran )
    { 
      TransactionHelper.JoinTransaction(_adapter, tran);
    }
  }
}

Here I am using my own helper class:

public class TransactionHelper
{
  public static void JoinTransaction( SqlAdapter adapter, SqlTransaction tran)
  {
      CheckTransaction(tran, adapter.InsertCommand);
      CheckTransaction(tran, adapter.DeleteCommand);
      CheckTransaction(tran, adapter.UpdateCommand);
      CheckTransaction(tran, adapter.SelectCommand);
  }
  public static void CheckTransaction( SqlAdapter adapter, SqlCommand cmd)
  {
    if (cmd != null)
      cmd.Transaction = tran;
  }
}

Now that I have injected a public JoinTransaction method into each TableAdapter, I can get them to enlist in the transaction by surreptitiously calling the trojan horse helper:

void EnlistTableAdaptersInTransaction( SqlTransaction tran )
{
  taReportSetGroupAccountNotAssigned.JoinTransaction( tran );
  taReportSetGroupAccount.JoinTransaction( tran );
  taReportSetGroup.JoinTransaction( tran );
  taReportSet.JoinTransaction( tran );   
}

Adding a call to this in my update logic, this is the final sequence of events:

SqlConnection conn = new SqlConnection( connStr );
InitTableAdapters( conn );
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
  EnlistTableAdaptersInTransaction( tran );
  // manipulate data
  tran.Commit();
}
catch()
{
  tran.Rollback();
}
finally
{
  conn.Close();
}

It's not the most elegant or compact code, but it gets the job done! I hope this will save you some time and allow you to productively use TableAdapters in transactions until the TransactionScope class becomes more development environment friendly!