CODEWITHSUNDEEP
c#sql-serverstored-proceduresidentitydapper
Alonso Contreras
Alonso Contreras

Reputation: 675

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

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?

enter image description here

Upvotes: 2

Views: 5632

Answers (1)

Robert Harvey
Robert Harvey

Reputation: 180868

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.

Upvotes: 9

Related Questions