How to mock Dapper using SQL Connection

user_11855535 0
Viewed 3529

I am trying to write the unit test cases for the repositories. I am struggling to mock the dapper using SQL connection.

My method:

public IEnumerable<TestModal> TestMethod(int id)
{
    var spName = "sp_Test";
    var spParams = new
        {
            ID= id
        };

    using (var connection = new SqlConnection(_configuration.GetConnectionString("TestDB")))
    {
        connection.Open();
        return connection.Query<TestModal>(spName, spParams, commandType: CommandType.StoredProcedure);
    }
}

I am trying to write the unit test case for the above method.

[TestFixture]
public class TestClassTests
{
    private Mock<IConfiguration> _mockConfiguration;
    private Mock<SqlConnection> _mockSqlCOnnection;
    private ITestRepository _testRepository;
    
    [SetUp]
    public void Setup()
    {
        _mockConfiguration = new Mock<IConfiguration>();
        _mockSqlCOnnection = new Mock<SqlConnection>();            
        _testRepository= new TestRepository(_mockConfiguration.Object);
    }
   
    [Test]
    public void TestMethodTest()
    {           
        _mockSqlCOnnection.SetupDapper(x => x.Query<TestModal>(It.IsAny<string>(), null, null, true, null, null)).Returns(fakeData);

        var result = _testRepository.TestMethod(10);

        Assert.IsNotNull(result);
        Assert.AreEqual(result.Count(), fakeData.Count());
    }
}
dapper moq unit-testing sql-server
1 Answers

You should not be mocking your database for unit testing.

Databases are very complex things, meaning your tests will provide little to no value if you're not using an actual database, no matter how well you mock it.

Depending on how complex your repository logic is you might want to use either a lighter database provider, like SQLite, LocalDb, etc. or an actual database like a local SQLServer.

Obviously you'd get the most accurate results if you use the same database as your production environment.

For examples on how unit testing works with SQLite you can have a look at Microsoft's article about unit testing with SQLite and InMemory databases. The article uses EF Core, but you should be able to extrapolate to use the same approach with Dapper.

For more information on unit-testing with real databases, you can read Jimmy Bogard's article on unit-testing with Respawn.

user_25425670
Related