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

Alvin Bernardo 46 Reputation points
2021-09-06T05:29:37.393+00:00

Hi I have asp.net 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

ASP.NET Core
ASP.NET Core
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
    2021-09-06T09:16:41.987+00:00

    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:

    Microsoft.EntityFrameworkCore  
    Microsoft.EntityFrameworkCore.InMemory  
    

    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; }  
       
            [Required]  
            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"));  
        services.AddControllersWithViews();  
    }  
    

    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,

    ChaoDeng

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 59,546 Reputation points
    2021-09-06T15:52:32.95+00:00

    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
    2021-09-09T08:48:31.783+00:00

    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