Dapper "async" and "await using"

Question

I have been building my data access layer and performance is key for the project I am working on.

I have chosen to use Dapper and dotnet core 3. I want to make use of the latest and greatest async features in dotnet core and Dapper to allow for smarter resource allocation under heavy load, however there is not much documentation out there.

All the dapper wrappers seem to be using non async and most examples I find for SqlConnection use non async methods.

I was wondering if the use of all async and await is worth the overhead.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Outible.API.Model;
using Outible.DataAccess.Interfaces;

namespace Outible.DataAccess.Models
{
    public class Db : IDb
    {
        private readonly IOptionsMonitor<ConnectionStrings> _connectionStrings;
        private readonly ILogger<Db> _logger;

        public Db(IOptionsMonitor<ConnectionStrings> connectionStrings, ILogger<Db> logger)
        {
            _connectionStrings = connectionStrings;
            _logger = logger;
        }

        private async Task<T> CommandAsync<T>(Func<IDbConnection, IDbTransaction, int, Task<T>> command)
        {
            var connection = new SqlConnection(_connectionStrings.CurrentValue.MainDatabase);
            await connection.OpenAsync().ConfigureAwait(false);
            await using var transaction = await connection.BeginTransactionAsync();
            try
            {
                var result = await command(connection, transaction, 300).ConfigureAwait(false);

                await transaction.CommitAsync().ConfigureAwait(false);

                return result;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync().ConfigureAwait(false);
                _logger.LogError(ex, "Rolled back transaction");
                throw;
            }
        }

        public Task<int> ExecuteAsync(string sql, object parameters)
        {
            return CommandAsync((conn, trn, timeout) => conn.ExecuteAsync(sql, parameters, trn, timeout));
        }

        public Task<T> GetAsync<T>(string sql, object parameters)
        {
            return CommandAsync((conn, trn, timeout) => conn.QuerySingleOrDefaultAsync<T>(sql, parameters, trn, timeout));
        }

        public Task<IEnumerable<T>> SelectAsync<T>(string sql, object parameters)
        {
            return CommandAsync((conn, trn, timeout) => conn.QueryAsync<T>(sql, parameters, trn, timeout));
        }
    }
}

Answer 1

I see you have already applied using to the transaction.

But given that a new SqlConnection is created for each command, I would suggest wrapping it in a using statement as well.

private async Task<T> CommandAsync<T>(Func<IDbConnection, IDbTransaction, int, Task<T>> command)
{
    using(var connection = new SqlConnection(_connectionStrings.CurrentValue.MainDatabase))
    {
        await connection.OpenAsync().ConfigureAwait(false);
        await using var transaction = await connection.BeginTransactionAsync();
        try
        {
            T result = await command(connection, transaction, 300).ConfigureAwait(false);

            await transaction.CommitAsync().ConfigureAwait(false);

            return result;
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync().ConfigureAwait(false);
            _logger.LogError(ex, "Rolled back transaction");
            throw;
        }
    }
}