Get id from stored procedure insert with Dapper in .NET Core

Question

I'm using Dapper and I have written a stored procedure that inserts a row and returns the identity value.

With Dapper, I want to capture this identity value, but the result is returned as a dynamic type and I do not know how access this id.

My stored procedure:

CREATE PROCEDURE USER_INSERT
    (@name  varchar(30),
     @job   varchar(30))    
AS    
BEGIN    
    INSERT INTO User (name, job) 
    VALUES (@name, @job)

    SELECT SCOPE_IDENTITY()
END 

C# code:

public async Task<int> InsertTest()
{
    using (var connection = new SqlConnection("myconnectionstring"))
    {
        string SP_INSERT = "USER_INSERT";

        var parameters = new { name = "Jon", job = "developer" };

        var insert =  await connection.QueryAsync(SP_INSERT, parameters, commandType: CommandType.StoredProcedure);

        var resultInsert = insert.SingleOrDefault();
    }

    return 0; //only for test
}

Screenshot of the result of the query, how to access to my id field?

Answer 1

Use ExecuteScalar for this.

int identity = await connection.ExecuteScalar<int>(SP_INSERT, parameters, commandType: CommandType.StoredProcedure);

Using Query or QueryFirst or any variation of Query will work, but isn't semantically correct. You're not querying a database; you're executing a stored procedure.