Saving Records & Caputring Return Value (Enterprise Library)

Stored Procedure should look like this

CREATE PROCEDURE [dbo].[Purchases_Insert]
    (@SupplierID     bigint,
     @ReferenceNo     NVARCHAR(20),
     @PurchaseDate     DATETIME,
     @CurrentPurchaseID bigint output)

AS

INSERT INTO [tblPurchases]
     ([SupplierID],
      [PurchaseNo],
      [ReferenceNo],
      [PurchaseDate])
 
VALUES
    (@SupplierID,
     @PurchaseNo,
     @ReferenceNo,
     @PurchaseDate)

SET @CurrentPurchaseID=SCOPE_IDENTITY()



Data Access Layer should look like this.

 Public Sub InsertPurchase()

        Try
           Dim DB As Database = DatabaseFactory.CreateDatabase(CWB_DBCONNECTION_STRING)
            Dim DBC As DbCommand = db.GetStoredProcCommand(PURCHASES_INSERT)
            db.AddInParameter(DBC, "@SupplierID", DbType.Int64, Me.SupplierID)
            db.AddInParameter(DBC, "@ReferenceNo", DbType.String, Me.RefBillNo)
            db.AddInParameter(DBC, "@PurchaseDate", DbType.DateTime, Me.PurchaseDate)
            db.AddOutParameter(DBC,"@CurrentPurchaseID", DbType.Int64, 0)       

            db.ExecuteNonQuery(DBC)

            Me.CurrentPurchaseID = Convert.ToInt64(db.GetParameterValue(DBC, "@CurrentPurchaseID").ToString())
        Catch ex As Exception

            Throw
        End Try

    End Sub

 

CurrentPurchaseID  has new value of identity column.