Return SCOPE IDENTITY from a stored procedure to your custom DNN module

Your SQL stored procedure:

INSERT INTO Invoices

(InvoiceNumber, InvoiceDate, SupplierID, ModuleID, CreatedOnDate, CreatedByUserID, PortalID)

VALUES

(@InvoiceNumber, @InvoiceDate, @SupplierID, @ModuleId, GETDATE(), @CreatedByUserID, @PortalID)

SELECT SCOPE_IDENTITY()

Next, all the methods in your abstract data provider class and concrete data provider class should be written as functions that return an integer which is the SCOPE IDENTITY that the stored procedure returns.

//SQLDataProvider

public override int FBInvoice_Insert(string invoiceNumber, DateTime invoiceDate, int supplierID, int createdByUserID, int moduleId, int portalId)

 {
return Convert.ToInt32(SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("FBInvoice_Insert"), invoiceNumber, invoiceDate, supplierID, createdByUserID, moduleId, portalId));
 }

// DataProvider.cs

public abstract int FBInvoice_Insert(string invoiceNumber, DateTime invoiceDate, int supplierID, int createdByUserID, int moduleId, int portalId);

// Controller.cs

public int FBInvoice_Insert(FBFoodInventoryInfo info)
{
 if (info.InvoiceNumber != string.Empty)
 {
  return Convert.ToInt32(DataProvider.Instance().FBInvoice_Insert(info.InvoiceNumber, info.InvoiceDate, info.SupplierID, info.CreatedByUserID, info.ModuleId, info.PortalId));
 }
 else
 {
  return 0;
 }
}

// Get the ID of the newly inserted record

int MyNewID = Null.NullInteger;
MyNewID = controller.FBInvoice_Insert(item);