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

Question

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

asp.net-core dapper

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();
        }
    }

Mike Hawkins

Popular Answer

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);
         }
 }

Popa Andrei

View more on Stack Overflow

Shyju · Accepted Answer · 6/2/2018 12:14:24 AM

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();
   }
}

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

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator