Return id of record when insert or update or delete record

user_11196342 0
Viewed 2676

When I insert or update or delete an entity with Dapper, I need to return the id of entity.

I am using this code : 

var role = await roleConnection.ExecuteAsync("UPDATE Role SET IsDelete = @isDelete, RoleName = @roleName, SecurityStamp = @securityStamp WHERE Role.Id = @id SELECT SELECT CAST(SCOPE_IDENTITY() AS INT)"
                     , new
                     {
                         isDelete = request.IsDelete = false,
                         roleName = request.RoleName,
                         securityStamp = Guid.NewGuid(),
                         id = request.Id
                     });

but it does not show me anything.

How can I do this?

dapper asp.net-core asp.net c#
1 Answers

Well, in the case of UPDATE or DELETE, you already must have the id, otherwise you couldn't be calling any of these methods - correct?

In the case of an INSERT, if your table is in SQL Server and has an INT IDENTITY column, you can use something like this:

INSERT INTO dbo.YourTable (list-of-columns)
OUTPUT Inserted.Id   -- or whatever your IDENTITY column is called
VALUES (list-of-values)

and then from your C# code, use cmd.ExecuteScalar() (or ExecuteScalarAsync, if your prefer) to run this and get back a single, atomic value (the newly created id value):

var result = cmd.ExecuteScalar(....);

if (result != null)
{
    int newId = Convert.ToInt32(result);
}
user_133020
Related