[Solved] Get identity after inserting new record!

[Prestoz]

I have a stored procedure for inserting new record, like that:

CREATE PROCEDURE [dbo].[sp_InsertRecord]
    (
      @FirstName NVARCHAR(50) ,
      @LastName NVARCHAR(50) ,
      @Title NVARCHAR(50) ,
      @ContactID INT OUTPUT

    )
AS
    BEGIN
        SET NOCOUNT ON;
        INSERT  INTO dbo.Contacts
        VALUES  ( @FirstName, @LastName, @Title )

        SET @ContactID = SCOPE_IDENTITY()
        RETURN @ContactID
    END
GO

In my project, if I use:

dbContext.Sp_InsertRecord(contact.FirstName, contact.LastName, contact.Title, 1)

How can I get the identity that is returned by this stored procedure?

Please help me in this case. Thanks!

[Hristo Marinov]

Hello Prestoz,

After you create the OpenAccess Domain Model you have to map and configure your stored procedure.

In your case you have two options. The first is to change the stored procedure to not have ContactId  as a parameter and to return Scope_Identity(). In this case in the Domain Method Editor you should choose the result type to be Scalar – System.Decimal and you can use it like this:

contact.ContactId = dbContext.Sp_InsertRecord(contact.FirstName, contact.LastName, contact.Title);

Here contact.ContactId should be decimal, because that is the type of Scope_Identity().

The other option is to set in the Domain Method Editor the return type to None, and invoking the method would look like this:

dbContext.Sp_InsertRecord(contact.FirstName, contact.LastName, contact.Title, ref contact.ContactId)

where contact.ContactId should be nullable int, because in the context of your stored procedure ContactId can be null.

By default, for a stored procedure with this signature, the Domain Method Editor will generate the method mentioned as second approach. In case that is what you have already done, you just need to pass a variable by reference instead of the constant 1.

Here you can find additional information about configuring Domain Methods calling Stored Procedures and Functions using the Domain Model Editor. 

Let us know if you have any additional issues.

All the best,
Hristo Marinov
the Telerik team