Skip to main content
Clarify the SqlHelper abstraction and added some final thoughts
Source Link

this helper is now responsible for only handling connections and transactions and if, in the future, you find a new ORM which uses the IDbConnection like Dapper does, you can re-use it.

Last but certainly not least is that this is only possible when using Inversion of Control

I think that this is the maximum amount of abstractions that is possible in a DAL. Other ORM's like EF and NHibernate differ so much from Dapper that you always have to re-write your repositories.

this helper is now responsible for only handling connections and transactions.

Last but certainly not least is that this is only possible when using Inversion of Control

this helper is now responsible for only handling connections and transactions and if, in the future, you find a new ORM which uses the IDbConnection like Dapper does, you can re-use it.

Last but certainly not least is that this is only possible when using Inversion of Control

I think that this is the maximum amount of abstractions that is possible in a DAL. Other ORM's like EF and NHibernate differ so much from Dapper that you always have to re-write your repositories.

Source Link

To be clear I think Adriano makes a very good point but still I wanted to provide some feedback:

I think you should separate piece of functionality in different classes.

You could abstract the handling of transactions and connections away in e.g

public class SqlHelper
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    private readonly IExecutionPolicy _executionPolicy;

    public SqlHelper(IDbConnectionFactory dbConnectionFactory, IExecutionPolicy executionPolicy)
    {
        if (dbConnectionFactory == null)
        {
            throw new ArgumentNullException(nameof(dbConnectionFactory));
        }
        if (executionPolicy == null)
        {
            throw new ArgumentNullException(nameof(executionPolicy));
        }
        _dbConnectionFactory = dbConnectionFactory;
        _executionPolicy = executionPolicy;
    }

    public TResult DoInTransaction<TResult>(Func<IDbTransaction, TResult> task,
        IsolationLevel isolation = IsolationLevel.Unspecified)
    {
        using (var conn = CreateConnection())
        {
            conn.Open();
            using (var transaction = conn.BeginTransaction(isolation))
            {
                try
                {
                    var result = task(transaction);
                    transaction.Commit();
                    return result;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
}

this helper is now responsible for only handling connections and transactions.

Next thing you could do is make use of the "repository pattern" in my opinion this should not be a generic interface with CRUD operations, but It could be that.

using Dapper;

public class DapperAccountTable : IAccountTable
{
    private readonly SqlHelper _sqlHelper;

    public DapperAccountTable(SqlHelper sqlHelper)
    {
        _sqlHelper = sqlHelper;
    }

    public AccountData FindAccountRecordByEmail(Username username)
    {
        if (username == null)
        {
            throw new ArgumentNullException(nameof(username));
        }
        return _sqlHelper.DoWithConnection((conn) =>
        {
            var account =
                conn.Query<AccountData>("SELECT * FROM Account WHERE Email = @Email AND Deleted = 0",
                    new {Email = username.ToUnicodeDbString()}).SingleOrDefault();
            if (account != null)
            {
                account.AdvisorId = QueryPartnerId(conn, account);
            }
            return account;
        });
    }

This repository is now the abstraction you are looking for. If you now want to move a way from dapper and use e.g. NHibernate you could create a new implementation of the IAccountTable interface and let this be injected instead of the DapperAccountTable.

Last but certainly not least is that this is only possible when using Inversion of Control