UpdateBatchSize and SqlTransaction

  • Thread starter Thread starter sahon
  • Start date Start date
S

sahon

Hello.

I tried to use new UpdateBatchSize of SqlDataAdapter with SqlTransaction,
unfortunately every time I have exception during Update of SqlDataAdapter:

System.InvalidOperationException: ExecuteNonQuery 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.
at
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

Insert SQL Command of DataAdapter is:
INSERT INTO [MyTable] ([sValue]) VALUES (@sValue);SELECT
@iID=IDENT_CURRENT('MyTable')
@iID is output parameter that mapped to iID column of DataTable
and UpdatedRowSource of Insert Command =
System.Data.UpdateRowSource.OutputParameters;
In the table there are only New records, so Update and Delete command will
not work.

With default UpdateBatchSize =1 everything works.

Could you advise me something to make it working or is it a bug?

Regards, Sahon
 
Hello Sahon,

can you post more code about how you use the SqlDataAdapter and
SqlTransaction, this may better understand what happened.

Thanks,

Luke Zhang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Sounds to me like you are not attaching transaction to at least one of the
adapter's commands.
 
myComponent.cn.Open();
SqlTransaction tr = myComponent.cn.BeginTransaction();
myComponent.daMyTable.UpdateBatchSize = 0;
myComponent.daMyTable.InsertCommand.Transaction = tr;
myComponent.daMyTable.Update(myComponent.myDataset.MyTable);
tr.Commit();

I used default setting, but also tried all parameters to create
transaction - some do not supported, some have the save behaviour - causes
exception described above.
 
in code I attached there are only records that have been added...

but I also tried

1. myComponent.daMyTable.Update(myComponent.myDataset.MyTable.Select("", "",
DataViewRowState.Added));

2.
myComponent.daMyTable.InsertCommand.Transaction = tr;
myComponent.daMyTable.UpdateCommand.Transaction = tr;
myComponent.daMyTable.DeleteCommand.Transaction = tr;
myComponent.daMyTable.Update(myComponent.myDataset.MyTable);

nothing works

I think the problem is that Insert command contains inside 2 SQL Commands:
INSERT INTO [MyTable] ([sValue]) VALUES (@sValue);SELECT
@iID=IDENT_CURRENT('MyTable')

seems for second command ADO.NET internally creates another call to SQL
Server but this call doesn't use transaction.
using insert without 2 commands makes no sence for me, I can use bulk copy
without any troubles ....
 
Hi Sahon,

Thanks for providing us with the code. You're getting the exception because
you didn't pass the transaction to all the SelectCommand of the
DataAdapter. I added the following and it works fine.

myComponent.daMyTable.UpdateBatchSize = 10;
myComponent.daMyTable.InsertCommand.Transaction = tr;
myComponent.daMyTable.DeleteCommand.Transaction = tr;
myComponent.daMyTable.UpdateCommand.Transaction = tr;
myComponent.daMyTable.SelectCommand.Transaction = tr;

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Yes, it works.

But it's rather strange behavior, because select command doesn't work during
update (I've checked it in SQL profiler), so it's only container for
transaction ....
 
Hi Sahon,

Based on my understanding, it's a by design behavior in the DataAdapter's
code. It checks the integrality for each command. Because according to the
SQL profiler's trace, the exception is not returned from SQL execution.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
SqlTransaction sqltr;
//Bigin Transaction here for all table
sqltr = conn.BeginTransaction();
try
{
sqlda1.UpdateBatchSize = 500;
sqlda1.InsertCommand.Transaction = sqltr;
sqlda1.SelectCommand.Transaction = sqltr;
sqlda1.Update(datatble);
sqltr.Commit();
}

try it
It will work
 
Back
Top