How do I set the command timeout for DataAdapter.Update when I instantiate the DataAdapter using a SELECT query?

Question

I have code that looks like this:

var ds = new DataSet();
var fooIDToFoo = new Dictionary<string, Foo> {{"Foo1", foo1}, {"Food2", foo2}};
var sql = "SELECT * FROM Foo WHERE FooID IN ('Foo1', 'Foo2')";
var da = new SqlDataAdapter(sql, dbConnection);
da.SelectCommand.CommandTimeout = 60;
// Can't set da.InsertCommand.CommandTimeout because db.InsertCommand is null.
// Can't set da.UpdateCommand.CommandTimeout because db.UpdateCommand is null.
var cb = new SqlCommandBuilder(da);
var fooTable = "Foo";
da.Fill(ds, fooTable);
var fooTable = ds.Tables[fooTable];
var existingFooIDSet = new Set<string>(); // Modify any Foo that's in DB.
foreach (DataRow r in fooTable.Rows)
{
  var fooID = r["ID"];
  var foo = fooIDToFoo[fooID]
  r["ID"] = foo.ID;
  r["Bar"] = foo.Bar;
  existingFooIDSet.Add(fooID);
}
foreach (var foo in fooIDToFoo.Values) // Add any Foo that's not in DB.
{
  if (!existingFooIDSet.Contains(foo.FooID))
  {
    var foo = fooIDToFoo[fooID]
    var r = fooTable.NewRow();
    r["ID"] = fooID;
    r["Bar"] = foo.Bar;
    fooTable.Rows.Add(r);
  }
}
da.Update(ds, "Steve"); // Times out!

The last statement times out, so I want to increase the timeout, but, as the code comments indicate, I can't set da.InsertCommand.CommandTimeout because db.InsertCommand is null, and I can't set da.UpdateCommand.CommandTimeout because db.UpdateCommand is null.

Answer 1

The solution is to explicitly get the insert and update commands using the DbCommandBuilder, set their timeouts, then assign them to the DbDataAdapter's InsertCommand and UpdateCommand properties:

da.InsertCommand = cb.GetInsertCommand();
da.InsertCommand.CommandTimeout = timeout;
da.UpdateCommand = cb.GetUpdateCommand();
da.UpdateCommand.CommandTimeout = timeout;

This shouldn't occur until immediately before the da.Update(db, "Steve").