Saving Records & Caputring Return Value (Enterprise Library)





0/5 (0 vote)
Stored Procedure should look like thisCREATE PROCEDURE [dbo].[Purchases_Insert] (@SupplierID bigint, @ReferenceNo NVARCHAR(20),
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.
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.