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

• BCdotWEB: Does this work? Because I doubt QueryAsync<T> returns an IEnumerable<T>.
• BCdotWEB: The site standard is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles.
• t3chb0t: @BCdotWEB it looks like it does, see here

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.

• Coder Absolute: FindListAsync is public because it is being directly called by the Controller, I will ask a separate question about that. Regarding the CategoryQueryService, I will read up. GetHandler is used by other endpoints. dbConnection is being injected through IConfiguration but for this example, I made a constant.