Dapper: Unit Testing SQL Queries

I would suggest reading up on dependency injection and the repository pattern. If you take the approach that you have in the code above you will have a hard time mocking out the dependencies because the class and method is static.

Here is a better approach.

public interface IUserRepository
{
   IEnumerable<User> GetAll()
}

public class UserRepository : IUserRepository
{
  public IEnumerable<User> GetAll()
  { 
    return DB.Users.All();
  }
}

public class UserService
{
    IUserRepository _userRepository;
    public UserService(IUserRepository userRepository)
    {
      _userRepository = userRepository
    }

    public Enumerable<User> GetAll(){
        return _userRepository.GetAll();
    }
}

Now for testing you can mock out your repository. I use a mocking framework called NSubstitute which in my mind is a lot simpler than the others mentioned above, but that is a personal preference. To start with here is how you could write your test without any mocking framework.

public class FakeUserRepository : IUserRepository
{
  public IEnumerable<User> GetAll()
  { 
    return new List<User> { new User {FirstName='Bob', LastName='Smith'}, };
  }
}

And in your test

[Test]
public void GetAll_ShouldReturnAllFromFake()
{
   // Arrrange
   var userService = new UserService(new FakeUserRepository())
   // Act
   var result = userService.GetAll();
   // Assert
   var user = result[0];
   Assert.AreEqual("Bob", user.FirstName);
   Assert.AreEqual("Smith", user.LastName);   
}

This example is a little contrived as it doesn't really make sense to test that you can get data back from a fake repository. How you would use this in reality is if you had some business logic in your service that say got back a user and then check that they were older than a certain age or something. e.g. an IsLegalDrivingAge method on UserService.