25

I know there are several question similar to mine.

but I don't think the above questions have a clear answer that fits my requirements.

I am developing a new WebAPI project and splitting it between WebAPI and DataAccess technology. I do not have a problem testing the WebAPI controller since I can mock the data access class.

But for the DataAccess class, that's a different story. Since I'm using Dapper with inline queries in it, I'm a bit confused about how to test it using a Unit Test. I've asked some of my friends, and they prefer to do an Integration test instead of a Unit Test.

Is it possible to unit test the DataAccess class that uses Dapper and inline queries?

Let's say I have a class like this (this is a generic repository class since a lot of the codes have similar queries differentiated 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
}
Improve this question

4 Answers 4

Reset to default
38

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);
            }
        }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Just one thing, the factory is not necessary to get an abstraction of IDbConnection (it is already an interface), but to be able to build new connections inside the repo. If you don't need that (and probably you won't need to create more than one connection in the context of a Web API request) you can directly pass the IDbConnection to the repo.
@IgnacioCalvo We do need more than one connection at the same time, e.g. when doing async queries which can run in parallel.
connectionFactoryMock where can i find this instance?
We used Moq library, so var connectionFactoryMock = new Mock<IDatabaseConnectionFactory>();
9

I would like add another perspective on this problem and a solution that takes a different approach to solving it.

Dapper can be considered as a dependency on the repository class as it is an external codebase that we have no control over. Therefore testing it is not really in the realm of responsibility for Unit Testing (More in line with integration testing as you mentioned).

With that said, we cannot really mock Dapper directly because it is really just an extension method set on the IDbConnection interface. We could mock all of the System.Data code until we get down to the IDbCommand where Dapper really does its work. That however would be a lot of work, and in most cases not worth the effort.

We instead can create a simple IDapperCommandExecutor mock-able interface:


public interface IDapperCommandExecutor
{
    IDbConnection Connection { get; }

    T Query<T>(string sql, object? parameters = null);

    // Add other Dapper Methods as required...
}

This interface then can simply be implemented with Dapper:


public class DapperCommandExecutor : IDapperCommandExecutor
{
    public DapperCommandExecutor(IDbConnection connection)
    {
        Connection = connection;
    }

    IDbConnection Connection { get; }

    T Query<T>(string sql, object? parameters = null) 
        => Connection.QueryAsync<T>(sql, parameters);

    // Add other Dapper Methods as required...
}

Then all you would have to do is change the following:

var queryResult = sqlConn.Query<T>(query.RawSql, query.Parameters);

to

var queryResult = commandExecutor.Query<T>(query.RawSql, query.Parameters);

Then in your testing, you can create a mocked Command Executor


public class MockCommandExecutor : Mock<IDapperCommandExecutor>
{

    public MockCommandExecutor()
    {
        // Add mock code here...
    }

}

In summary, we do not need to test the Dapper library, it can, for unit testing, be mocked in. This mocked Dapper Command Executor will reduce the additional dependency requirement for an in-memory database and can reduce the complexity of your tests.

Improve this answer

Comments

7

I adapted what @Mikhail did because I had issues when adding the OrmLite packages.

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

I've created a DbColumnAttribute so we can specify a specific column name for a classes property.

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

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

I added some IDbConnection extensions for the CreateTableIfNotExists and InsertAll methods.

This is very rough so I've not mapped types correctly

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's are not passed to the TryParse method.
        if (s.Length > 1 && s.StartsWith("0"))
            return false;

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

You can still use it the same way as @Mikhail mentions in Step 3.

Improve this answer

Comments

0 
Add this new code
// File: Program.cs
var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Add Cloud Foundry VCAP support
builder.Configuration.AddCloudFoundry();

builder.Services.AddScoped<IDataRepository, DataRepository>();
builder.Services.AddScoped<IDataService, DataService>();
builder.Services.AddSingleton<IKafkaProducer, KafkaProducer>();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseAuthorization();
app.MapControllers();
app.Run();


// File: Interfaces/IDataRepository.cs
public interface IDataRepository
{
    Task<string> GetClosedDataCommaSeparatedAsync();
}

// File: Interfaces/IDataService.cs
public interface IDataService
{
    Task<string> ReturnAsync();
}

// File: Interfaces/IKafkaProducer.cs
public interface IKafkaProducer
{
    Task SendAsync(string message);
}

// File: Repositories/DataRepository.cs
public class DataRepository : IDataRepository
{
    public Task<string> GetClosedDataCommaSeparatedAsync()
    {
        // Simulated closed case data
        var mockData = new List<string>
        {
            "101,John Smith,Closed,North",
            "102,Jane Doe,Closed,East",
            "103,Bob Brown,Closed,West"
        };

        // Combine with pipe delimiter
        return Task.FromResult(string.Join("|", mockData));
    }
}

// File: Services/DataService.cs
public class DataService : IDataService
{
    private readonly IDataRepository _repo;

    public DataService(IDataRepository repo)
    {
        _repo = repo;
    }

    public async Task<string> ReturnAsync()
    {
        return await _repo.GetClosedDataCommaSeparatedAsync();
    }
}

// File: Services/KafkaProducer.cs
using Confluent.Kafka;

public class KafkaProducer : IKafkaProducer
{
    private readonly IProducer<Null, string> _producer;
    private readonly HashSet<string> _sentMessages = new();

    public KafkaProducer(IConfiguration config)
    {
        var kafkaSection = config.GetSection("VCAP_SERVICES:streaming:0:credentials");

        var producerConfig = new ProducerConfig
        {
            BootstrapServers = kafkaSection["bootstrap_servers"],
            SecurityProtocol = SecurityProtocol.Ssl,
            SslCaLocation = kafkaSection["ssl_ca_location"] ?? "certs/ca.pem",
            SslCertificateLocation = kafkaSection["ssl_cert_location"] ?? "certs/client-cert.pem",
            SslKeyLocation = kafkaSection["ssl_key_location"] ?? "certs/client-key.pem",
            SslKeyPassword = kafkaSection["ssl_key_password"]
        };

        _producer = new ProducerBuilder<Null, string>(producerConfig).Build();
    }

    public async Task SendAsync(string message)
    {
        if (_sentMessages.Contains(message)) return;

        await _producer.ProduceAsync("closed-data-topic", new Message<Null, string> { Value = message });
        _sentMessages.Add(message);
    }
}

// File: Controllers/KafkaController.cs
using Microsoft.AspNetCore.Mvc;

[ApiController]
[Route("[controller]")]
public class KafkaController : ControllerBase
{
    private readonly IDataService _service;
    private readonly IKafkaProducer _producer;

    public KafkaController(IDataService service, IKafkaProducer producer)
    {
        _service = service;
        _producer = producer;
    }

    [HttpGet("send-closed")]
    public async Task<IActionResult> SendClosedData()
    {
        var data = await _service.ReturnAsync();
        await _producer.SendAsync(data);
        return Ok(new
        {
            KafkaMessage = data,
            Message = "Sent to Kafka"
        });
    }
}

// File: appsettings.Development.json
{
  "VCAP_SERVICES": {
    "streaming": [
      {
        "credentials": {
          "bootstrap_servers": "localhost:9093",
          "ssl_ca_location": "certs/ca.pem",
          "ssl_cert_location": "certs/client-cert.pem",
          "ssl_key_location": "certs/client-key.pem",
          "ssl_key_password": "your-key-password"
        }
      }
    ]
  }
}

// File: certs/README.txt
// Place the following PEM files in the certs directory:
// - ca.pem:       CA certificate
// - client-cert.pem:  Client certificate
// - client-key.pem:   Client private key

// File: SampleKafkaPayload.txt
// Sample Kafka Payload:
// 101,John Smith,Closed,North|102,Jane Doe,Closed,East|103,Bob Brown,Closed,West

End new code



       public Task<string> GetClosedDataCommaSeparatedAsync()
{
    // Simulated closed case data
    var mockData = new List<string>
    {
        "101,John Smith,Closed,North",
        "102,Jane Doe,Closed,East",
        "103,Bob Brown,Closed,West"
    };

    // Combine with pipe delimiter
    return Task.FromResult(string.Join("|", mockData));-//

——

{
  "VCAP_SERVICES": {
    "streaming": [
      {
        "credentials": {
          "bootstrap_servers": "kafka.example.com:9092",
          "user": "my-kafka-user",
          "password": "my-kafka-password",
          "sasl_mechanism": "PLAIN",
          "security_protocol": "SaslSsl"
        }
      }
    ]
  }
}

—/

var kafkaSection = builder.Configuration.GetSection("VCAP_SERVICES:streaming:0:credentials");

var bootstrapServers = kafkaSection["bootstrap_servers"];
var saslUsername = kafkaSection["user"];
var saslPassword = kafkaSection["password"];
var securityProtocol = kafkaSection["security_protocol"] ?? "SaslSsl";
var saslMechanism = kafkaSection["sasl_mechanism"] ?? "PLAIN";

// Optional logging (for development only)
if (app.Environment.IsDevelopment())
{
    Console.WriteLine("Kafka Configuration:");
    Console.WriteLine($"BootstrapServers: {bootstrapServers}");
    Console.WriteLine($"Username: {saslUsername}");
    Console.WriteLine($"SecurityProtocol: {securityProtocol}");
    Console.WriteLine($"SaslMechanism: {saslMechanism}");
}

var producerConfig = new ProducerConfig
{
    BootstrapServers = bootstrapServers,
    SaslUsername = saslUsername,
    SaslPassword = saslPassword,
    SecurityProtocol = Enum.TryParse<SecurityProtocol>(securityProtocol, out var protocol) ? protocol : SecurityProtocol.SaslSsl,
    SaslMechanism = Enum.TryParse<SaslMechanism>(saslMechanism, out var mechanism) ? mechanism : SaslMechanism.Plain
};
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.