Unit Testing Dapper with Inline Queries

c# dapper unit-testing

Question

I know there are several question similar to mine.

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
}

Popular Answer

Here is our approach:

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

    public interface IDatabaseConnectionFactory
    {
        IDbConnection GetConnection();
    }
    
  2. 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");
        }
    }
    
  3. 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);
    }
    
  4. 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);
                }
            }
        }
    }
    


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why