How to set the command timeout for a DataAdapter.Update when I create an instance of a DataAdapter using a SELECT query?

Question

I have a 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 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 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 expires, so I want to increase the timeout, but, as the code comments indicate, I cannot set da.InsertCommand.CommandTimeout, because db.InsertCommand is null, and I cannot set da. UpdateCommand.CommandTimeout since db.UpdateCommand is null.

Answer 1

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

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

This should not happen until da.Update (db, "Steve").