ExecuteReader requires the command to have a transaction...

2007-07-26 10:22  i1248    [permalink]

I had a real nice data-application, that bundled some changes in XML with the schema and a diffgram using datasets, but as an added check I wanted to have the whole thing run in a transaction.

I thought this would do the trick:

SqlConnection

db = new SqlConnection(myConnectionString);
db.Open();

SqlTransaction

tr = db.BeginTransaction();
try
{
  ...
  using (SqlDataAdapter da = new SqlDataAdapter(myQuery, db))
  {
    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    ...
  }
  ...
  tr.Commit();
}
catch
{
  tr.Rollback();
  throw;
}
db.Close();

No such luck! The command builder failed in doing its magic:

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.

So I had to search my way through anything I could find, using any [[Google]]s and [[Reflector]]s of this world I came across, just to discover that it's all fixed by putting the transaction on the DataAdapter's SelectCommand! The CommandBuilder copies that one nicely to all the other commands it confabulates.

SqlConnection

db = new SqlConnection(myConnectionString);
db.Open();

SqlTransaction

tr = db.BeginTransaction();
try
{
  ...
  using (SqlDataAdapter da = new SqlDataAdapter(myQuery, db))
  {
    da.SelectCommand.Transaction = tr;
    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    ...
  }
  ...
  tr.Commit();
}
catch
{
  tr.Rollback();
  throw;
}
db.Close();

→