I'm trying to migrate an existing code base to use the new Batch Update feature in Enterprise Library 3.0/3.1 Data Access Block. I've run in to a snag that is preventing me from leveraging this feature.
We insert rows from typed DataSets using DataAdapters. Our CRUD DbCommands call stored procedures. In the Insert stored procedure, our primary key, usually an autogenerated surragate key, is an output parameter. That way, when we insert new rows via a data
table, the primary key is replaced with the new value in Added rows in the DataSet. We also have an out parameter with a concurrency value in it to support optimisitc locking.
This works great until we change the code to use the new Update overload and set the batch size to something other than 1. As soon as we do that, the output parameter values are no longer returned to the rows in the DataSet. We've tried turning on the UpdateRowSource
http://msdn2.microsoft.com/en-us/library/system.data.updaterowsource(VS.80).aspx on the insert command to OutputParameters
http://msdn2.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx. This doesn't seem to work.
I'd hate to think our method of returning data set in the stored procedures will prevent us from using this feature but I can't seem to find a way around this. Can anyone shed light on this situation?
FYI out Insert stored procedures look like the following (columns omitted where you see "..."):
@LastUpdate datetime OUTPUT,
@ID int OUTPUT
SET NOCOUNT ON
SET @LastUpdate = GetDate()
INSERT INTO dbo.Entity1 (
) VALUES (
SET @ID = SCOPE_IDENTITY()
Thanks in adavance -
While I haven't faced this particular scenario, I've seen this article from John Papa (http://msdn.microsoft.com/msdnmag/issues/06/01/DataPoints/default.aspx)
indicating using batch updates and output parameters is not supported:
There is a time and place for everything, and batch updates are no exception. Batch updates do not support the use of return or output parameters, so you will want to avoid using batch updates if you need return or output parameters. Batch updates also affect
the RowUpdated event because they will cause the RowUpdated event to fire only once for each batch even though so many updates were made. Figure 6 shows some differences in the number of times the RowUpdated event handler would execute based upon the UpdateBatchSize
when 25 rows are updated.
Sep 17, 2007 at 10:58 PM
Edited Sep 17, 2007 at 11:27 PM
Thanks for the link. I;ve observed that the output paramters don't seem to work. However, the MSDN documentation seems to say otherwise:
When batch updates are enabled, the UpdatedRowSource property value of the DataAdapter's UpdateCommand, InsertCommand, and DeleteCommand should be
set to None or OutputParameters. When performing a batch update, the command's UpdatedRowSource property value of FirstReturnedRecord or Both is invalid.
The ability to set UpdatedRowSource to OutputParameters led me to hope I could.