Unit Testing Dapper with Inline Queries

c#, dapper, unit-testing

Solution

Here is our approach:

First of all, you need to have an abstraction on top of `IDbConnection` to be able to mock it:

public interface IDatabaseConnectionFactory
{
    IDbConnection GetConnection();
}

Your repository would get the connection from this factory and execute the `Dapper` query on it:

public class ProductRepository
{
    private readonly IDatabaseConnectionFactory connectionFactory;

    public ProductRepository(IDatabaseConnectionFactory connectionFactory)
    {
        this.connectionFactory = connectionFactory;
    }

    public Task<IEnumerable<Product>> GetAll()
    {
        return this.connectionFactory.GetConnection().QueryAsync<Product>(
            "select * from Product");
    }
}

Your test would create an in-memory database with some sample rows and check how the repository retrieves them:

[Test]
public async Task QueryTest()
{
    // Arrange
    var products = new List<Product>
    {
        new Product { ... },
        new Product { ... }
    };
    var db = new InMemoryDatabase();
    db.Insert(products);
    connectionFactoryMock.Setup(c => c.GetConnection()).Returns(db.OpenConnection());

    // Act
    var result = await new ProductRepository(connectionFactoryMock.Object).GetAll();

    // Assert
    result.ShouldBeEquivalentTo(products);
}

I guess there are multiple ways to implement such in-memory database; we used `OrmLite` on top of `SQLite` database:

public class InMemoryDatabase
{
    private readonly OrmLiteConnectionFactory dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteOrmLiteDialectProvider.Instance);

    public IDbConnection OpenConnection() => this.dbFactory.OpenDbConnection();

    public void Insert<T>(IEnumerable<T> items)
    {
        using (var db = this.OpenConnection())
        {
            db.CreateTableIfNotExists<T>();
            foreach (var item in items)
            {
                db.Insert(item);
            }
        }
    }
}

Problem

I know there are several question similar to mine. - Dapper: Unit Testing SQL Queries - Testing Dapper Queries butI don't think both of above question has clear answer that fit my requirement. Right now I develop a new WebAPI project and split between WebAPI project and DataAccess technology. I not have a problem test the Controller for WebAPI since I can mock the data access class. But for DataAccess class that's a different stories, since I'm using Dapper with inline queries in it, I'm a bit confuse how can I test it by using Unit Test. I've asked some of my friends and they prefer to do Integration test instead of Unit Test. What I want to know is, is it possible to unit test the DataAccess class that use Dapper and Inline queries in it. Let's say I have a class like this (this is a generic repository class, since a lot of the codes have similar queries differentiate by table name and field) ``` public abstract class Repository<T> : SyncTwoWayXI, IRepository<T> where T : IDatabaseTable { public virtual IResult<T> GetItem(String accountName, long id) { if (id <= 0) return null; SqlBuilder builder = new SqlBuilder(); var query = builder.AddTemplate("SELECT /**select**/ /**from**/ /**where**/"); builder.Select(string.Join(",", typeof(T).GetProperties().Where(p => p.CustomAttributes.All(a => a.AttributeType != typeof(SqlMapperExtensions.DapperIgnore))).Select(p => p.Name))); builder.From(typeof(T).Name); builder.Where("id = @id", new { id }); builder.Where("accountID = @accountID", new { accountID = accountName }); builder.Where("state != 'DELETED'"); var result = new Result<T>(); var queryResult = sqlConn.Query<T>(query.RawSql, query.Parameters); if (queryResult == null || !queryResult.Any()) { result.Message = "No Data Found"; return result; } result = new Result<T>(queryResult.ElementAt(0)); return result; } // Code for Create, Update and Delete } ``` And the implementation for above code is like ``` public class ProductIndex: IDatabaseTable { [SqlMapperExtensions.DapperKey] public Int64 id { get; set; } public string accountID { get; set; } public string userID { get; set; } public string deviceID { get; set; } public string deviceName { get; set; } public Int64 transactionID { get; set; } public string state { get; set; } public DateTime lastUpdated { get; set; } public string code { get; set; } public string description { get; set; } public float rate { get; set; } public string taxable { get; set; } public float cost { get; set; } public string category { get; set; } public int? type { get; set; } } public class ProductsRepository : Repository<ProductIndex> { // ..override Create, Update, Delete method } ```

Original source

Related problems