Asynchronous is always better, yeah nah
For a trivial controller like the one below the difference between synchronous and asynchronous calls is most probably negligible, the asynchronous versions may even be slightly slower. ASP.NET Core web API applications should be designed to process many requests concurrently.
The Dapper library has the following asynchronous methods
- ExecuteAsync
- QueryAsync
- QueryFirstAsync
- QueryFirstOrDefaultAsync
- QuerySingleAsync
- QuerySingleOrDefaultAsync
- QueryMultipleAsync
These asynchronous methods enable a small pool of threads to process thousands of concurrent requests by not waiting on blocking database calls. Rather than waiting on a long-running synchronous database call to complete, the thread can work on another request.
namespace devMobile.WebAPIDapper.Lists.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class StockItemsAsyncController : ControllerBase
{
private readonly string connectionString;
private readonly ILogger<StockItemsAsyncController> logger;
public StockItemsAsyncController(IConfiguration configuration, ILogger<StockItemsAsyncController> logger)
{
this.connectionString = configuration.GetSection("ConnectionStrings").GetSection("WideWorldImportersDatabase").Value;
this.logger = logger;
}
[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemListDtoV1>>> Get()
{
IEnumerable<Model.StockItemListDtoV1> response = null;
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QueryAsync<Model.StockItemListDtoV1>(sql: @"SELECT [StockItemID] as ""ID"", [StockItemName] as ""Name"", [RecommendedRetailPrice], [TaxRate] FROM [Warehouse].[StockItems]", commandType: CommandType.Text);
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving list of StockItems");
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
}
}
This sample controller method returns a small number of records (approximate 230) in one request so performance is unlikely to be a consideration. A controller method which returns many (1000s or even 10000s) records could cause performance and scalability issues. In a future post I will add pagination and then do some stress testing of the application to compare the different implementations.