Dapper Unit Testing with Inline Queries

Question

I know that there are several questions similar to mine.

• Dapper: Unit Testing SQL Queries
• Testing Dapper Queries

but I do not think that both of the above questions have a clear answer that meets my requirement.

Now I am developing a new WebAPI project and sharing between the WebAPI project and DataAccess technology. I have no problem testing the controller for WebAPI, as I can mock the data access class.

But there are different stories for the DataAccess class, since I use Dapper with built-in queries in it, I'm a little confused how I can test it using Unit Test. I asked some of my friends and they prefer to do an integration test instead of Unit Test.

I want to know if the unit test is able to use the DataAccess class in it to use Dapper and Inline queries.

Let's say I have a class like this (this is a common repository class, since many codes have similar queries, differing in 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 the above code is similar to

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
}

Answer 1

Here is our approach:

• First of all, you need to have an abstraction on top IDbConnectionto mock it:

  public interface IDatabaseConnectionFactory
  {
      IDbConnection GetConnection();
  }
  

• Your repository will receive a connection from this factory and execute a request Dapperon 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");
      }
  }
  

• In your test, an in-memory database will be created with some sample strings 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);
  }
  

• , ; OrmLite SQLite:

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

Answer 2

, @Mikhail, OrmLite.

internal class InMemoryDatabase
{
    private readonly IDbConnection _connection;

    public InMemoryDatabase()
    {
        _connection = new SQLiteConnection("Data Source=:memory:");
    }

    public IDbConnection OpenConnection()
    {
        if (_connection.State != ConnectionState.Open)
            _connection.Open();
        return _connection;
    }

    public void Insert<T>(string tableName, IEnumerable<T> items)
    {
        var con = OpenConnection();

        con.CreateTableIfNotExists<T>(tableName);
        con.InsertAll(tableName, items);
    }
}

DbColumnAttribute, .

public sealed class DbColumnAttribute : Attribute
{
    public string Name { get; set; }

    public DbColumnAttribute(string name)
    {
        Name = name;
    }
}

IDbConnection CreateTableIfNotExists InsertAll.

,

internal static class DbConnectionExtensions
{
    public static void CreateTableIfNotExists<T>(this IDbConnection connection, string tableName)
    {
        var columns = GetColumnsForType<T>();
        var fields = string.Join(", ", columns.Select(x => $"[{x.Item1}] TEXT"));
        var sql = $"CREATE TABLE IF NOT EXISTS [{tableName}] ({fields})";

        ExecuteNonQuery(sql, connection);
    }

    public static void Insert<T>(this IDbConnection connection, string tableName, T item)
    {
        var properties = typeof(T)
            .GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .ToDictionary(x => x.Name, y => y.GetValue(item, null));
        var fields = string.Join(", ", properties.Select(x => $"[{x.Key}]"));
        var values = string.Join(", ", properties.Select(x => EnsureSqlSafe(x.Value)));
        var sql = $"INSERT INTO [{tableName}] ({fields}) VALUES ({values})";

        ExecuteNonQuery(sql, connection);
    }

    public static void InsertAll<T>(this IDbConnection connection, string tableName, IEnumerable<T> items)
    {
        foreach (var item in items)
            Insert(connection, tableName, item);
    }

    private static IEnumerable<Tuple<string, Type>> GetColumnsForType<T>()
    {
        return from pinfo in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
            let attribute = pinfo.GetCustomAttribute<DbColumnAttribute>()
            let columnName = attribute?.Name ?? pinfo.Name
            select new Tuple<string, Type>(columnName, pinfo.PropertyType);
    }

    private static void ExecuteNonQuery(string commandText, IDbConnection connection)
    {
        using (var com = connection.CreateCommand())
        {
            com.CommandText = commandText;
            com.ExecuteNonQuery();
        }
    }

    private static string EnsureSqlSafe(object value)
    {
        return IsNumber(value)
            ? $"{value}"
            : $"'{value}'";
    }

    private static bool IsNumber(object value)
    {
        var s = value as string ?? "";

        // Make sure strings with padded 0 are not passed to the TryParse method.
        if (s.Length > 1 && s.StartsWith("0"))
            return false;

        return long.TryParse(s, out long l);
    }
}

, @Mikhail 3.