4
\$\begingroup\$

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));
        }
    }
}
\$\endgroup\$
  • 1
    \$\begingroup\$ Side note: all those ConfigureAwait(false) are a pain to write and read. I'd consider this an implementation detail (then I'd let the caller deal with this, if it needs to!) but if you really do not want to then you should change the synchronization context once and for all (better using an helper method for this) at the beginning of your method. \$\endgroup\$ – Adriano Repetti Nov 5 '19 at 10:40
  • \$\begingroup\$ good point @AdrianoRepetti, thanks \$\endgroup\$ – LiverpoolOwen Nov 5 '19 at 20:06
3
\$\begingroup\$

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;
        }
    }
}
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.