Dapper for NET Core: Insert into a table and return id of inserted row

Question

I have the following method in my repository. As of now, i believe the int returned is simply one indicating whether or not the operation was successful. I want the int to be the id (which is the single column of the table) to be returned after a successful execution. How do i accomplish this?

    public async Task<int> AddNewGroup()
    {
        using(_connection)
        {
            _connection.Open();
            var id = await _connection.ExecuteAsync("INSERT INTO groups").Single();
        }
    }

Answer 1

You can run a query which has 2 parts, first is your INSERT part and second is a SELECT part. In the SELECT part, you can return(select) whatever column value you want.

For example, If your group table has a primary key column called GroupId and you have set that column for Identity value generation(automatic value generation), you can call the SCOPE_IDENTITY() to get the generated value.

We will use the QueryAsync method.

public async Task<int> AddNewGroup()
{
   using(_connection)
   {
      _connection.Open();
      var q = @"INSERT INTO Groups(Name,Description) VALUES
                 (@name, @desc); SELECT CAST(SCOPE_IDENTITY() as int)"
      var result = await _connection.QueryAsync<int>(q, 
                                          new { @name="some name", @desc="some desc"});
      return result.Single();
   }
}

Answer 2

You don't have to create by hand the insert query, you can use Dapper.Contrib github which helps you to manage CRUD operations.

Using Dapper.Contrib you can do something like:

 public async Task<int> AddNewGroup(Group entity)
 {
        using (_connection)
        {
             _connection.Open();
             var id = await _connection.InsertAsync(entity);
         }
 }

Answer 3

If you're using SQL Azure / SQL Server, you need to return the inserted value from the query using something like

INSERT INTO groups OUTPUT inserted.id VALUES (...)

and then instead using ExecuteAsync use ExecuteScalarAsync

Reference to the OUTPUT clause here: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017