How do I use Dapper in an asynchronous controller?

Question

This is my first time using async/await with the Dapper ORM. I keep getting the message that my controller lacks the await method. How can I correctly insert the await method in my code?

public async Task<ActionResult> index(int? page)
{

    if (page == null)
    {
        page = 1;
        ViewBag.page = page;
    }
    else
    {

        ViewBag.page = page;
    }
    string Connectionstring = ConfigurationManager.ConnectionStrings["mycontext"].ConnectionString;
    using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
    {

        sqlConnection.Open();
        var sqlM = @"SELECT * from threads order by activities desc";
        var resultM = sqlConnection.Query<thread>(sqlM).ToList();
        await Task.WhenAll(resultM);
        return View(new homepage { panel = resultM });
    }


}

I have tried await Task.WhenAll(resultM); but it didn't change anything.

Answer 1

You're getting the warning because you aren't using any asynchronous methods in the controller. As it is, there's no reason to mark it as async.

In order to actually make the method asynchronous, you need to use Dapper's async methods and await them. See Using Async Await keywords with Dapper for another example of this.

In your code, it would look like:

public async Task<ActionResult> index(int? page)
{

    if (page == null)
    {
        page = 1;
        ViewBag.page = page;
    }
    else
    {

        ViewBag.page = page;
    }

    string Connectionstring = ConfigurationManager.ConnectionStrings["mycontext"].ConnectionString;
    using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
    {
        await sqlConnection.OpenAsync();

        var sqlM = @"SELECT * from threads order by activities desc";
        var resultM = await sqlConnection.QueryAsync<thread>(sqlM);

        return View(new homepage { panel = resultM });
    }
}

If resultM must be a List<thread> instead of IEnumerable<T>, you can call ToList - but make sure it's after awaiting the asynchronous call.