Unit Test Dapper with DapperParameters

The Dapper Micro ORM (written by StackExchange) is a really fast and slick alternative to the bulky EntityFramework.  I’ve been using it in numerous projects the past year.  The one thing I’ve lamented is the inability to unit test Dapper since it is written using extension methods on IDbConnection.  Recently I found DapperWrapper, which gives you the ability to unit test the Execute and Query methods.  It lacked, however, a wrapper around the Dapper DynamicParameters class.  So if you use this at all (which I do since I use a lot of stored procedures), you are out of luck.

Until now.

I wrote a wrapper called DapperParameters.  It is on NuGet here.  The source for the example shown in this article is on GitHub.

Usage

Here’s a typical usage of the interface IDapperParameters:

public int ExampleMethod(string valueParm)
{
    using (var cnn = DependencyInjector.GetDependency<IDbExecutor>())
    {
        var p = DependencyInjector.GetDependency<IDapperParameters>();
        p.Add("Parm1", valueParm, dbType: DbType.String, direction: ParameterDirection.Input, size: 8);
        p.AddOutputParameter("Output1", DbType.Boolean);
        p.Add("ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

        cnn.Execute("MySproc", param: p.DynamicParameters, commandType: CommandType.StoredProcedure);

        var returnVal = p.Get<int>("ReturnValue");
        if (p.Get<bool>("Output1"))
            return returnVal;

        return 0;
    }
}

• DependencyInjector is a static class I use to store the container for the SimpleInjector dependency injection package
  • I’m assuming you have set up dependency injection for your project
  • Use DapperParameters as the implementation class of IDapperParameters (when not unit testing)
• IDbExecutor is the interface created by DapperWrapper that allows us to test Execute and Query
• IDapperParameters is the parameters interface I wrote
• In this case, I am adding an input parameter “Parm1”, an output parameter “Output1” and a return value parameter “ReturnValue”
• Note in the Execute that param is p.DynamicParameters
• Use Get to retrieve the Output and ReturnValue values

IDapperParameters

The interface IDapperParameters gives you everything that DynamicParameters does and adds some convenience methods for good measure.

public interface IDapperParameters
{
    DynamicParameters DynamicParameters { get; }
    void CreateParmsWithTemplate(object template);
    void Add(string name, object value = null, DbType? dbType = default(DbType?), ParameterDirection? direction = default(ParameterDirection?), int? size = default(int?));
    void AddDynamicParams(dynamic param);
    T Get<T>(string name);
    IEnumerable<string> ParameterNames { get; }
    void AddInputParameter(string name, object value, DbType? dbType = null, int? size = null);
    void AddInputParameter<T>(string name, T value, DbType? dbType = null, int? size = null);
    void AddOutputParameter(string name, DbType? dbType = null, int? size = null);
    void AddReturnValue(string name, DbType? dbType = null, int? size = null);
}

Notes:

• Always pass DynamicParameters to param of Execute or Query method
• By default, the implementation creates DynamicParameters without a template
• There is a method called CreateParmsWithTemplate that allows you to create DynamicParameters with a template
• AddInputParameter has a type template so that unit tests can pass

Unit Test Example

To unit test the above example using Moq, do the following:

Setup

private DbAccess _dbAccess;
private Mock<IDapperParameters> _parmsMock;
private Mock<IDbExecutor> _execMock;
private DynamicParameters _dynParms;

[SetUp]
public void Setup()
{
    var container = new Container();
    DependencyInjector.Initialize(container, true);

    _parmsMock = new Mock<IDapperParameters>();
    _execMock = new Mock<IDbExecutor>();

    container.Register(() => { return _parmsMock.Object; });
    container.Register(() => { return _execMock.Object; });

    _dynParms = new DynamicParameters();

    _dbAccess = new DbAccess();
}

 Test

[Test]
public void ExampleMethod_Success_Test()
{
    // Arrange
    string valueParm = "Hello";
    int returnValue = 1;
    bool outputValue = true;

    // Parameter setups
    // This is a bit long so do what I did in my projects and create
    // helper methods to do this automatically for you.
    _parmsMock.Setup(m => m.Add(It.Is<string>(name => name == "Parm1"),
        It.Is<string>(val => val == valueParm),
        It.Is<DbType?>(dt => dt == DbType.String),
        It.Is<ParameterDirection?>(pd => pd == ParameterDirection.Input),
        It.Is<int?>(size => size == 8))).Verifiable();
    _parmsMock.Setup(m => m.AddOutputParameter(It.Is<string>(name => name == "Output1"),
        It.Is<DbType?>(dt => dt == DbType.Boolean),
        It.Is<int?>(size => size == null))).Verifiable();
    _parmsMock.Setup(m => m.Add(It.Is<string>(name => name == "ReturnValue"),
        It.Is<object>(val => val == null),
        It.Is<DbType?>(dt => dt == DbType.Int32),
        It.Is<ParameterDirection?>(pd => pd == ParameterDirection.ReturnValue),
        It.Is<int?>(size => size == null))).Verifiable();
    _parmsMock.SetupGet(m => m.DynamicParameters).Returns(_dynParms).Verifiable();
    _parmsMock.Setup(m => m.Get<int>(It.Is<string>(name => name == "ReturnValue")))
        .Returns(returnValue).Verifiable();
    _parmsMock.Setup(m => m.Get<bool>(It.Is<string>(name => name == "Output1")))
        .Returns(outputValue).Verifiable();

    // Execute setups
    _execMock.Setup(m => m.Execute(It.Is<string>(sql => sql == "MySproc"),
        It.Is<object>(parm => parm == _dynParms),
        It.IsAny<IDbTransaction>(), It.IsAny<int?>(),
        It.Is<CommandType?>(ct => ct == CommandType.StoredProcedure))).Returns(0).Verifiable();

    // Act
    var actual = _dbAccess.ExampleMethod(valueParm);

    // Assert
    _parmsMock.Verify();
    _execMock.Verify();
    Assert.That(actual, Is.EqualTo(returnValue));
}

Conclusion

The story for unit testing Dapper is getting better all the time.  With the addition of DapperParameters, we can now unit test stored procedure calls easily.