How to Moq database for unit testing ? [Dapper with stored procedure]

Alvin Bernardo 46 Reputation points

Hi I have web API, for data access we choose Dapper that call stored procedure. Now I am wondering how I am gonna unit test the database operation. Should I create an actual test database with connection string to proceed testing or Is their any way I can moq the database

A set of technologies in the .NET Framework for building web applications and XML web services.
4,308 questions
0 comments No comments
{count} votes

Accepted answer
  1. Chao Deng-MSFT 796 Reputation points

    Hi @Alvin Bernardo ,

    You can prepare an in-memory database so that you do not have to use a real SQL server during the test. Install the following 2 packages:


    With this, we can continue to create the database context and model.
    Create a new class called Register.cs

    public class Register  
            public int Id { get; set; }  
            public string Name { get; set; }  
            [Range(40, 60)]  
            public int Age { get; set; }  

    Then create the Database Context class

    public class AppDbContext : DbContext  
            public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)  
            public DbSet<Register> Register { get; set; }  

    The Register.cs class has been added to the properties of the database context. So now you can perform Entity Framework Core operations on it. Next, add the database context as a service in the ConfigureServices() method of the Startup.cs class, as shown below:

    public void ConfigureServices(IServiceCollection services)  
        services.AddDbContext<AppDbContext>(optionsBuilder => optionsBuilder.UseInMemoryDatabase("InMemoryDb"));  

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best Regards,


    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Bruce ( 59,546 Reputation points

    You designed your code wrong if you need to moq dapper calls. You should have deigned a mockable data layer. Also a lot mocking required for testing is also a red flag.

    0 comments No comments

  2. Duane Arnold 3,216 Reputation points

    IMO, you should be doing integration testing (do it for real) against MS SQL Server test database using a test harness and unit test framework doing integration testing against a Data Access Layer.

    0 comments No comments