Alica's dev blog
Integration tests with ASP.NET Core and SQL Server - Part 1: Intro

Recently I’ve been working on a commonplace task: creating a WebApi using ASP.NET Core and SQL Server.


As a new developer, I really wanted to do everything properly from the start, including testing.

I focused on integration tests, as the business logic was not very complicated and called the database operations directly (through EF Core). Controllers just called those handling methods. Therefore there were no layers in between which I could test independently, only with some unit tests. I would say that those integration tests were actually going to be my unit tests, but they needed a database too!

To my surprise, I couldn’t find any complete guide on how to set up the testing process:

  • create the database,
  • seed and change its contents during the tests,
  • make HTTP requests to the controllers.

I ended up figuring all of these components one by one and then putting them together to cover the whole process. There were some limiting factors, such as:

  • setup of the project which was decided beforehand (e.g. we store the database definition as a Database project),
  • my company’s restrictions (e.g. it’s not possible to use Docker)

that I had to incorporate to my solution. Therefore it’s not the best solution for setting up integration tests in general, but it’s (hopefully) the best one given the existing limitations.

What We Need To Do

  1. Create a testing database from a Database project (it comes in SQL Server Data Tools for Visual Studio). Ideally, the only input would be the connection string and we would get a database with the same structure as specified in the project. If the database already exists, then let’s just update it. See Part 2: Create the database to learn more about that.

  2. Seed, reset and otherwise update data in the database as the tests need. It may be running custom SQL or wiping out all the data. Part 3: Seed and change the database talks about that.

  3. Call controller actions from the tests and check their results. There are numerous arguments why (or why not) to test the controllers, so let’s just assume that we want to do it. It would be great if we could do that right in the tests, without running the API somewhere and the tests sending HTTP requests to that API from somewhere else. There is a post about it: Part 4: Make HTTP requests to the API.

  4. Put it all together so that it’s pretty and elegant and straightforward and everything. We will do that in Part 5: Put it all together.

Source Code

You can view the complete source code on Github.

What’s next

Read the next posts in the series:

Last modified on 2020-10-09