Okay, so I'm using table adapters to update my database. I have about
20+ tables that get updated in a single transaction. Here is
essentially how it is setup:

MyDataSet dsChanges = _theDataSet.GetChanges() as MyDataSet;
Transaction oldAmbient = Transaction.Current;
CommittableTransaction trans = new CommittableTransaction();
Transaction.Current = trans;
SqlConnection connection = new SqlConnection(GetConnectionString());

try
{
   connection.Open();
   connection.EnlistTransaction(trans);
   success = UpdateAllMyTables(connection, dsChanges);
   if(success) trans.Commit();
   else trans.Rollback();
}
catch
{
   trans.Rollback();
}
finally
{
   if (connection.State == ConnectionState.Open)
   {
      connection.Close();
   }
   connection.Dispose();
   Transaction.Current = oldAmbient;
   trans = null;
}

My problem is that while debugging UpdateAllMyTables (i.e. stepping
through all the inserts/updates) I would inevitably get a FOREIGN KEY
violation. If I didn't step through my code, no problem, everything
completed as expected. I immediately suspected some kind of timeout
issue, so I played around with some stuff. What I have arrived at is
the problem is my transaction. If I leave the transaction out of the
whole thing, I could be stepping through my inserts, walk away from my
PC, go to the bathroom, get a drink, come back, and continue stepping
through with no problem. However, when I leave the code in that
creates my transaction and enlists in it, well then I seem to be time
limited. I don't really have the time nailed down, but appears to be
somewhere between 30-45 seconds. So basically at some point my
transaction is giving up, rolling back everything it has done up to
that point, then I step through the next insert (which depends on data
inserted into another table by the LAST insert) and falls flat on its
face (FK violation).

Now I don't *expect* that my application will ever have an actual
transaction that lasts that long, but I don't want to assume that and
walk away from this problem. I want to be able to solve it, and set it
up as some user-configurable value in case in the future I have a
customer that actually has a system so large they actually hit what
ever the default limit is.

So I'm not really seeing a way to change the timeout on the
transaction (or maybe I'm just missing something and looking at this
wrong). Anyone have a clue? I'd really appreciate it.

Chris