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