yoy - “Why-O-Why”

» frame | home browse filter search | refresh | log on

  printed vrijdag 24 mei 2013 7:30:41 from www.yoy.be

ExecuteReader requires the command to have a transaction...

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 Googles and Reflectors 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();

location: Application Development > Platforms > Microsoft .Net > System.Data
created: 26/07/2007 10:22:44 « modified: 26/07/2007 10:25:37 (diff) weight: 0

children, tokens, references comments (2)

up down edit   del   created: 26/07/2007 10:22:48 « modified: 26/07/2007 10:22:48 weight: 0

see also -- Google Search Microsoft edit   del   created: 26/07/2007 10:23:59 « modified: 26/07/2007 10:24:05 weight: 0
Google
see also -- Lutz Roeder's Programming.NET C# VB CLR WinFX edit   del   created: 26/07/2007 10:24:48 « modified: 26/07/2007 10:24:48 weight: 0
Reflector

~31ms [anonymous@yoy.be]