Dapper: Unit Testing SQL Queries

user_1115117 0
Viewed 14903

I am starting with Dapper, the micro-ORM, and i use the Dapper Rainbow. I want to test the queries and the data retrieved by them.

I mean, for example, i have the UserService with the method GetAll(), and i want to test that the sql query is retrieving all the users from some List (not from the database because i want the tests to be fast). Do you know how can i do that?

My service class (and the method i want to test):

public static class UserService{
    public static IEnumerable<User> GetAll(){
        return DB.Users.All();
    }
}

Do you have any advice about unit testing queries and data retrieving?

Thanks

dapper tdd unit-testing .net c#
2 Answers

With Dapper, your SQL is likely in string literals, perhaps mixed with C# conditionals, syntax not validated, DB references possibly wrong. Your instinct to test is a good one. However, running your code against your real DB is the only way of telling if it outputs a valid query. So the test you need here is an integration test. This is not hard and you can use your unit test framework to do it, but since the test must hit the real DB, you may not want to run it everywhere you run unit tests, not on your build server for instance.

Then, since Dapper is extension methods to ADO, to unit test the code that consumes your query you'll need to wrap it in the repository pattern. Dapper Wrapper seems to be the tool here.

If all this seems unnecessarily difficult, please try QueryFirst (disclaimer: I wrote it). You write your SQL in a real sql window, connected to your DB, sql validated as you type. Your query is integration tested against your DB every time you save the file, without you lifting a finger. Then, if the query runs, QueryFirst generates the wrapper code to let you use it, including an interface so you can easily mock the real query when unit testing the consuming code. That has to be a step forward, no?

user_15853450
Related