Return newly inserted row id from stored procedure using LINQ toSQL

Question

I have a stored procedure as following:

ALTER PROCEDURE [dbo].[Addworkshop]
    @title varchar(100),
    @time varchar(10),
    @date date,
    @mandatory int,
    @sid int
AS
BEGIN
    SET NOCOUNT ON;

    insert into Workshop(title, date, time, mandatory, sid)
    values(@title, @date, @time, @mandatory, @sid)

    declare @wid int
    set @wid = scope_identity()
    return @wid
END

And I call it from my action method in ASP.NET MVC5 C#:

public JsonResult addWorkshop(Workshop workshop)
{
        TLCWREntities context = new TLCWREntities();
        var wid = db.Addworkshop(workshop.title, workshop.time, workshop.date, workshop.mandatory, workshop.sid);
        return Json(wid);
}

This method is called using JSON ajax:

 $.ajax({

     type: 'POST',
     url: '@Url.Action("addWorkshop")', // we are calling json method

     dataType: 'json',

     data: $.toDictionary(wsData),


      success: function (wid) {
                        console.log("success add workshop wid: "+wid);

                    },
      error: function (ex) {
                        console.log("err add workshop");

                    }
                });

The data is successfully inserted to the table and when I execute the stored procedure within SQL Server Management Studio it returns the correct newly inserted row id. However, when I call the stored procedure using JSON ajax and action method it always returns -1.

I am working on Visual Studio 2013 MVC5 C# and SQL Server Studio 2008

Have a look at this: http://stackoverflow.com/questions/32140774/getting-data-from-stored-procedure-with-entity-framework/ — Salah Akbari, Jan 31 '16 at 07:55
for your question @iuliu.net wid's value is the id created during the insert operation. but for some reason the SP returns -1 when called from within ASP.NET — Alsmayer, Jan 31 '16 at 09:10
@user2946329 I had a look at the link and I am doing exactly the same thing as the answer. the only difference is that I am not returning an entity form the stored procedure. Instead, I am just returning an integer — Alsmayer, Jan 31 '16 at 09:12
@Alsmayer...So you should choose the return value of the procedure to be `Scalar`. Then it should works. — Salah Akbari, Jan 31 '16 at 09:24

score 4 · Accepted Answer · answered Jan 31 '16 at 11:16

with the help of @user2946329 and a few searches on Google, I managed to get the answer. 1. I altered my stored procedure like this:

insert into Workshop(title, date, time, mandatory, sid)
values(@title, @date, @time, @mandatory, @sid)
declare @wid int
set @wid = SCOPE_IDENTITY()
select @wid AS wid

I returned the last inserted row id using select statement instead of return

in my Entity Framework Model file I updated model from database then from Model browser I navigated to Function Imports, right click on my function then Edit. I set the return value as Scalar
I right click on my model that is affected by the function and select Stored Procedure Mapping

I set the Result column binding and bind the return id with the id of my modelview

Thanks all for helping out

score 0 · Answer 2 · answered Jan 31 '16 at 08:01

0

It looks to me as you created the stored procedure for the purposes of returning an ID after insertion, case in which I strongly recomend the following:

public JsonResult addWorkshop(Workshop workshop)
{
    TLCWREntities context = new TLCWREntities();
    db.Workshops.Add(workshop);
    db.SaveChanges(); //when this executes, `workshop` gets its `id` field populated
    return Json(workshop.sid);
}

answered Jan 31 '16 at 08:01

iuliu.net

6,666
6
46
69

tried this answer which looks logical to me but it throws this error during run time `Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.` I tried to google it but had no luck. is there another way around? – Alsmayer Jan 31 '16 at 09:13
Is your `sid` column auto-incrementing(identity)? – iuliu.net Jan 31 '16 at 12:37

score 0 · Answer 3 · answered Jan 31 '16 at 08:08

0

try this

int workShopId = 0;
using (TLCWREntities context = new TLCWREntities ())
{
    context.Workshop.InsertOnSubmit(workshop);
    context.SubmitChanges();
    workShopId = workshop.ID;//ID is column from you workshop table
}

answered Jan 31 '16 at 08:08

Ajay

6,418
18
79
130

Return newly inserted row id from stored procedure using LINQ toSQL

3 Answers3