Getting OutputParameters When Using Batch Update

Topics: Data Access Application Block
Sep 14, 2007 at 12:22 AM
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 value 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 "..."):
{
CREATE PROCEDURE dbo.Entity1_Insert
@NullVarcharField varchar(50),
@NonNullVarcharField varchar(50),
...
@LastUpdate datetime OUTPUT,
@ID int OUTPUT
AS

SET NOCOUNT ON

SET @LastUpdate = GetDate()

INSERT INTO dbo.Entity1 (
LastUpdate,
NullVarcharField,
NonNullVarcharField,
...
) VALUES (
@LastUpdate,
@NullVarcharField,
@NonNullVarcharField,
...
)

SET @ID = SCOPE_IDENTITY()

--endregion

GO
}

Thanks in adavance -

Jeff Odell
Sep 17, 2007 at 2:40 PM
Hi,

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:


Choose Carefully

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.


Regards,
Fernando
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:

http://msdn2.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx

Specifically:

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.