Asynchronously fetching data from the database

Question

The following C# code is written to fetch the Categories data from SQL Server Database using Asynchronous Task for HttpGet. The Dapper library is used as ORM.

I need help to identify whether the Async Task implementation is correct or any better way to implement.

CategoriesController.cs

public class CategoriesController : Controller 
{
  [HttpGet]
  public async Task<IActionResult> FindList(CategorySearchModel searchModel)
  {
    var results = await new CategoryQueryService().FindList();

    return GetHandler(results);
  }

  IActionResult GetHandler(object results)
  {
      return new OkObjectResult(new { results });
  }
}

CategoryQueryService.cs

public class CategoryQueryService : BaseQueryDataStoreAsync<CategoryQueryModel>
{
  public override Task<IEnumerable<CategoryQueryModel>> FindList()
  {
    const string dbConnectionString = "-- db connection string here --";
    const string sql = "SELECT CategoryId, CategoryName FROM Category ORDER BY CategoryName ASC";

    return QueryAsync(dbConnectionString, sql);
  }
}

CategoryQueryModel.cs

public class CategoryQueryModel {
  public int CategoryId { get; set; }
  public int CategoryName { get; set; }
}

BaseQueryDataStoreAsync.cs

using Dapper;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading.Tasks;

public abstract class BaseQueryDataStoreAsync<T> where T : class
{
  public abstract Task<IEnumerable<T>> FindList();

  public async Task<IEnumerable<T>> QueryAsync(string dbConnectionString, string sql)
  {
    using (var connection = new SqlConnection(dbConnectionString))
    {
      return await connection.QueryAsync<T>(sql, conditions: null, transaction: null, commandTimeout: null, commandType: null);
    }
  }
}

Answer 1

Your async/await usage doesn't look fine because the chain it's incomplete. FindList should be async and named FindListAsync and it should await the result from QueryAsync.

There are also a few other things that botter me:

• since BaseQueryDataStoreAsync is an abstract class then QueryAsync should probably be protected and not public - otherwise someone can use it for any query
• the CategoryQueryService should be injected
• the GetHandler doesn't look very useful
• making the dbConnectionString a const is a terrible idea - you could/should use appSettings.json to store it and IConfiguration or IOptions to get it
• setting QueryAsync's default parameters to null where they are null anyway (source) is pretty pointless - instead just use the overload with a single parameter.