Yet Another UpdateDataSet Question...

Topics: Data Access Application Block
Nov 8, 2010 at 5:51 AM

Ive build a generic DataClass which uses the UpdateDataSet of the EnterpriseLibraryContainer.Current.GetInstance<Database>.

My proc:

        public void Update(DataSet dataSet)
        {
            try
            {
                // Create the insert command
                DbCommand insertCommand = DB.GetStoredProcCommandWithSourceColumns(string.Format("stp_{0}_Insert", tableName), sourceColumns);
                // Create the delete command
                DbCommand deleteCommand = DB.GetStoredProcCommandWithSourceColumns(string.Format("stp_{0}_Delete", tableName), sourceColumns[0]);
                // Create the update command
                DbCommand updateCommand = DB.GetStoredProcCommandWithSourceColumns(string.Format("stp_{0}_Update", tableName), sourceColumns);
                // Update the database with the changes performed to the table
                // Pass an insert-, delete- and update command, dataSet and table name
                DB.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);

            }
            catch (Exception ex)
for the Insert command, i want to retrieve the new Identity of the inserted Record, so my Stored Proc is following:
ALTER PROCEDURE dbo.stp_Project_Insert
(
         @ProjectID int OUTPUT,
	@Reference reference ,
	@DisplayWithTax bit ,
	@PaymentMethodID int ,
	@InvoiceDirect bit 
)
AS
BEGIN
  DECLARE @error int
  DECLARE @error_cd int

  BEGIN TRAN 

  INSERT Project
  (
    [Reference],
	[DisplayWithTax],
	[PaymentMethodID],
	[InvoiceDirect]
  )
  VALUES
  (
    @Reference,
	@DisplayWithTax,
	@PaymentMethodID,
	@InvoiceDirect 
  )

  SELECT 	@error = @@error 

  IF @error = 0
  BEGIN
    COMMIT TRAN
    SELECT [ProjectID],
	[Reference],
	[DisplayWithTax],
	[PaymentMethodID],
	[InvoiceDirect]
    FROM  Project
    WHERE 
                ProjectID = IDENT_CURRENT('Project')
  END
  ELSE -- @error <> 0
  BEGIN
    IF @error =  2627
    BEGIN
      ROLLBACK TRAN
	  RAISERROR ('50010', 16, 1)
    END
    ELSE
    BEGIN
      ROLLBACK TRAN
      RAISERROR (@error, 16, 1)
    END
  END
END
Sadly enought, after the insert my Identity Record is still "-1" instead the new Database value.
Can anyone point me in the right direction how tu retrieve the Inserted ID?
Kr,
Paul
Nov 8, 2010 at 7:04 AM

Seem to found it, in stead of OUTPUTing the firstparameter, simply null it:  

 @ProjectID int OUTPUT, becomes   @ProjectID int = null,

then the ID is returned

Paul