UpdateBatchSize and SqlTransaction

[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

 

[Luke Zhang [MSFT]]

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.)

 

[Miha Markic [MVP C#]]

Sounds to me like you are not attaching transaction to at least one of the
adapter's commands.

 

[sahon]

Ok. Why with default UpdateBatchSize=1 works?

 

[Miha Markic [MVP C#]]

Ok. Why with default UpdateBatchSize=1 works?

Missed that line, sorry :-S
Now, are you doing this update within explicit transaction?

 

[sahon]

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.

 

[Miha Markic [MVP C#]]

Are you doing only inserts? What about Update and DeleteCommand?

 

[sahon]

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 ....

 

[Kevin Yu [MSFT]]

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."

 

[sahon]

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 ....

 

[Kevin Yu [MSFT]]

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."

 

[Janardan Singh]

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