Custom C# Dapper ORM wrapper

Question

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.

What do you think about my approach? Here is the code:

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);
    }
}

Thanks in advance for any kind of help.

Answer 1

Yup. Please don't do this. Dapper exists, and enjoys the success that it does, because it provides a terse, expressive way of doing ADO. It's not an ORM. If you wrap dapper, you lose the terse expressive interface, and you lose the point. ORM's (which dapper isn't) exist partly to provide DB portability. Starting to talk about ORM portability will cause folk to bang their heads against a wall, in despair! Just use Dapper and admire it.

Answer 2

Wrapping Dapper have some benefits:

• you dont have to write "using" statement for each SqlConnection in every DB access
• when you would like to use different ORM then just replacing methods in this wrapper will be enough

Enum IDataAccess

using System.Collections.Generic;
using System.Threading.Tasks;

namespace Server.Database
{
    public interface IDataAccess
    {
        Task<List<T>> Query<T>(string sql, object parameters = null);
        public Task Execute<T>(string sql, T parameters);
    }
}

Body of DataAccess wrapper around Dapper

public class DataAccess : IDataAccess
{
    private readonly string _connectionString;

    public DataAccess(string connectionString)
    {
        _connectionString = connectionString;
    }

    //if needed extend parameters to those that Dapper allows. Like: transaction, commandTimeout, commandType...
    public async Task<List<T>> Query<T>(string sql, object parameters = null)
    {
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            var rows = await connection.QueryAsync<T>(sql, parameters);
            return rows.ToList();
        }
    }

    public Task Execute<T>(string sql, T parameters)
    {
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            return connection.ExecuteAsync(sql, parameters);
        }
    }
}

I call these wrappers in my funcions like this:

namespace Server.Database.App
{
    public class Changelog
    {
        private readonly IDataAccess _dataAccess;

        public Changelog(IDataAccess dataAccess)
        {
            _dataAccess = dataAccess;
        }

        public List<ChangelogRecord> GetAllRecords()
        {
            var sql = "SELECT * FROM Changelog ORDER BY id DESC";
            return _dataAccess.Query<ChangelogRecord>(sql).Result;
        }
    }
}

and in Controller I call these functions:

namespace Server.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ChangelogController : ControllerBase
    {
        private readonly Changelog _changelog;

        public ChangelogController(Changelog changelog)
        {
            _changelog = changelog;
        }

        [HttpGet]
        [Route("RecordCount")]
        public int GetNumberOfRecords()
        {
            return _changelog.GetNumberOfRecords();
        }
    }
}

In order to link DataAccess you have to add into

server startup->ConfigureServices:

services.AddSingleton<IDataAccess>(new DataAccess(Configuration.GetConnectionString("yourConnectionStringName")));

Reference: https://www.youtube.com/watch?v=_JxC6EUxbDo

Answer 3

I suggest you to go for a OO desing and create an object that has only one method that returns a sequence of some object type. This way you can create a PAge object and you pass the parameters through a constructor so you can Create different type of pages: SqlPages, DapperPages, TestablePages and so on. This way you have a flexible way of working and you can desing your code and leave the infraestructure/database details implemented in the end. I would encapsulate details of dtabases inside object, dont let the details spread through your code:

/// <summary>
/// DTO
/// </summary>
public class MyDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
}

/// <summary>
/// Define a contract that get a sequence of something
/// </summary>
/// <typeparam name="T"></typeparam>
public interface IFetch<T>
{
    IEnumerable<T> Fetch();
}

/// <summary>
/// Define a pageTemplate
/// </summary>
/// <typeparam name="T"></typeparam>
public abstract class PageTemplate<T> : IFetch<T>
{
    protected readonly int pageSize;
    protected readonly int page;

    public PageTemplate(int page, int pageSize)
    {
        this.page = page;
        this.pageSize = pageSize;
    }
    public abstract IEnumerable<T> Fetch();
}

/// <summary>
/// Design a MyDto Page object, Here you are using the Template method
/// </summary>
public abstract class MyDtoPageTemplate : PageTemplate<MyDto>
{
    public MyDtoPageTemplate(int page, int pageSize) : base(page, pageSize) { }
}

/// <summary>
/// You can use ado.net for full performance or create a derivated class of MyDtoPageTemplate to use Dapper
/// </summary>
public sealed class SqlPage : MyDtoPageTemplate
{
    private readonly string _connectionString;
    public SqlPage(int page, int pageSize, string connectionString) : base(page, pageSize)
    {
        _connectionString = connectionString;
    }

    public override IEnumerable<MyDto> Fetch()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            //This can be injected from contructor or encapsulated here, use a Stored procedure, is fine
            string commandText = "Select Something";
            using (var command = new SqlCommand(commandText, connection))
            {
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows) yield break;
                    while (reader.Read())
                    {
                        yield return new MyDto()
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Value = reader.GetString(2)
                        };
                    }
                }
            }
        }
    }
}

/// <summary>
/// You can test and mock the fetcher
/// </summary>
public sealed class TestPage : IFetch<MyDto>
{
    public IEnumerable<MyDto> Fetch()
    {
        yield return new MyDto() { Id = 0, Name = string.Empty, Value = string.Empty };
        yield return new MyDto() { Id = 1, Name = string.Empty, Value = string.Empty };
    }
}

public class AppCode
{
    private readonly IFetch<MyDto> fetcher;
    /// <summary>
    /// From IoC, inject a fetcher object
    /// </summary>
    /// <param name="fetcher"></param>
    public AppCode(IFetch<MyDto> fetcher)
    {
        this.fetcher = fetcher;
    }
    public IEnumerable<MyDto> FetchDtos()
    {
        return fetcher.Fetch();
    }
}

public class CustomController
{
    private readonly string connectionString;

    public void RunSql()
    {
        var fetcher = new SqlPage(1, 10, connectionString);
        var appCode = new AppCode(fetcher);
        var dtos = appCode.FetchDtos();
    }

    public void RunTest()
    {
        var fetcher = new TestPage();
        var appCode = new AppCode(fetcher);
        var dtos = appCode.FetchDtos();
    }
}