Custom C# Dapper ORM wrapper

Question

I am I C#.NET developer for a few years now... Last couple of months I have started to study design patterns and enterprise software architecture principles so that I will be able to use those principles in my code... Well here is actual example what I need. Right now I am developing ASP.net web API REST services. My data is stored in MySQL relational database. In data access layer I would like to use Dapper micro ORM, so I would like to create some kind of my own ORM wrapper methods... If I decide in the future to change to some other ORM that I won't need to rewrite my whole DAL layer code... Here is the code how I am planning to do this: What do you think about my approach? (my goal here is not to be technology (dapper) dependent...)

public abstract class BaseORMCommandSettings //SQL command base class
{
    public string CommandText { get; private set; }
    public object Parameters { get; private set; }
    public IDbTransaction Transaction { get; private set; }
    public int? CommandTimeout { get; private set; }
    public CommandType? CommandType { get; private set; }
    public CancellationToken CancellationToken { get; private set; }

    public BaseORMCommandSettings(string commandText, object parameters = 
    null, 
    IDbTransaction transaction = null, int? commandTimeout = null,
                         CommandType? commandType = null, CancellationToken 
    cancellationToken = default(CancellationToken))
    {
        this.CommandText = commandText;
        this.Parameters = parameters;
        this.Transaction = transaction;
        this.CommandTimeout = commandTimeout;
        this.CommandType = commandType;
        this.CancellationToken = cancellationToken;
    }
}

public class DapperCommandSettings : BaseORMCommandSettings//dapper cmd impl
{
    public CommandFlags Flags { get; private set; }

    public DapperCommandSettings(string commandText, object parameters = 
    null, IDbTransaction transaction = null, int? commandTimeout = null,
                         CommandType? commandType = null, CancellationToken 
    cancellationToken = default(CancellationToken), CommandFlags flags = 
    CommandFlags.Buffered)
     :base(commandText, parameters, transaction, commandTimeout, 
    commandType, cancellationToken)
    {
        this.Flags = flags;
    }
}

public interface ICustomORM //base interface, for now have only generic Read 
                        list method
{
    IEnumerable<T> Read<T>(BaseORMCommandSettings cmd);
}

public class DapperORM : ICustomORM //my own dapper ORM wrapper implentation
{
    private readonly IDbConnection con;

    public DapperORM(IDbConnection con)
    {
        this.con = con;
    }

    public IEnumerable<T> Read<T>(BaseORMCommandSettings cmd)
    {
        var cmdDapper = cmd as DapperCommandSettings;
        var dapperCmd = new CommandDefinition(cmdDapper.CommandText, 
        cmdDapper.Parameters, cmdDapper.Transaction,
                                          cmdDapper.CommandTimeout, 
        cmdDapper.CommandType, cmdDapper.Flags, 
                                          cmdDapper.CancellationToken);

        return con.Query<T>(dapperCmd);
    }
}

Answer 1

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 and if, in the future, you find a new ORM which uses the IDbConnection like Dapper does, you can re-use it.

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

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.