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

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.