Get identity after inserting new record!

2 posts, 0 answers
  1. Prestoz
    Prestoz avatar
    1 posts
    Member since:
    Jun 2010

    Posted 22 Nov 2012 Link to this post

    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
            SET NOCOUNT ON;
            INSERT  INTO dbo.Contacts
            VALUES  ( @FirstName, @LastName, @Title )
            SET @ContactID = SCOPE_IDENTITY()
            RETURN @ContactID

    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!
  2. Hristo Marinov
    Hristo Marinov avatar
    13 posts

    Posted 27 Nov 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  3. DevCraft banner
Back to Top