Using Dapper Dot Net get last inserted id

Question

I am using Microsoft Visual Studio Express 2012 for Web to develop the MVC5 application. I want to get last inserted ID by using Dapper dot net. I got solutions here but they are working on SQL Server 2012 (Management Studio) but when I run the query in code then returning value is wrong. Here are pieces of code that I have tried already to get last inserted ID

SqlConnection _db = new SqlConnection("String");

        string query_1 = @"DECLARE @InsertedRows AS TABLE (user_id int);Insert into users (stuff) OUTPUT Inserted.user_id INTO @InsertedRows values(@stuff); SELECT user_id FROM @InsertedRows";
        string query_2 = @"Insert into users (stuff) OUTPUT Inserted.user_id values(@stuff);";
        string query_3 = @"Insert into users (stuff) values(@stuff); SELECT SCOPE_IDENTITY()";

        1. var id = _db.Query<int>(query_x, new { stuff = @stuff });
        2. var id = _db.Execute(query_x, new { stuff = @stuff });

but the value of ID in both cases is 1 but not actual result. but when I run the same query on SQL management Studio then all things okay. Please help to fix this problem.

`OUTPUT Inserted` **FYI, this may be slower than using SCOPE_IDENTITY and was fixed in update #5 to SQL Server 2008 R2 Service Pack 1** — Kiquenet, Feb 15 '17 at 12:53

score 0 · Answer 1 · answered Aug 17 '15 at 08:27

I think the problem is that you are using SCOPE_IDENTITY() (Docu at https://msdn.microsoft.com/en-us/library/ms190315.aspx: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch)

You probably want the result from IDENT_CURRENT() (Docu at https://msdn.microsoft.com/en-us/library/ms175098.aspx: Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.)

score 0 · Accepted Answer · answered Sep 18 '15 at 09:39

Okay I am going to answer my own asked question I got answer after some other search in Dapper we have a Extension Method .Single() that will return the value of Selectd value newly added i.e.

var id = _db.Query<int>(query_x, new { stuff = @stuff }).Single();

in my case it will return the value of newly added primary key value. I just add the .Single() function to get newly added value.

Using Dapper Dot Net get last inserted id

2 Answers2