none
problem Using Batch Update with Output Parameters RRS feed

  • Question

  • 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

     

    Friday, September 14, 2007 12:26 AM