Automate SQL Stored Procedure Unit Testing for Code First Approach Part -1

Currently I am working on a project that uses ef core and for most of the server side aggregate  operations, I am using stored procedures. Using the stored procedures along with ef core I can able to optimize the performance and reduce the response time. But when it comes to testing, its quite difficult to test the logic of the stored procedure with unit testing.  Application wise we can mock the stored procedure results and unit test the logic.But we cant test the logic inside the stored procedures. Because most of the unit testing frameworks using the in-memory database. So to overcome these, I have decided to use the SQL Server Database Project to the solution. Since I am following the ef code first approach this project will not help me with the database deployment but this will simplify the process of creating the unit test cases for the stored procedures. So I am going to  cover the following in this article to show how I have done the unit testing.

Steps to Automate SQL Stored Procedure Unit Testing  for Code First Approach

1. Create SQL Server Database Project
2. Add unit testing project
3. Create simple unit test to test stored procedure
4. Create pre and post test conditions
5. Build and run test cases in azure devops

Create SQL Server Database Project
To create SQL Server Database project we need to have SQL Server Data Tools (SSDT)  installed in our visual studio instance. If not you can alter the installation and add SSDT to existing instance. For Installation guide please follow https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15



To create SQL Server Database Project

1. Right click on the solution 
2. Select Add -> New Project
3. From the project templates select the  SQL Server Database Project
   
   
   

After That You can give whatever the name for your project.After adding the project we need to import the database. There are three options available to import database.

1. Data-tier Application
2. Database
3. Script

Hear I am selecting the Database. This will request to connect an existing database. Once I connect to the database that will scaffold the data base objects to the project.Once the scaffolding is completed we can see the stored procedures under dbo folder.

So we are done with the initial database project setup. Next we will see how we can create a unit test for a particular stored procedure. To create unit test for a stored procedure

1. Right click on the stored procedure
2. Select create unit tests option

This will open a window and ask to select the unit test project and a file name for the unit test. If you dont have a unit test project under your solution then you need to select a create new project option and need to give a name for the project.

Also you can specify the unit test class name. This will create a new unit test project for us. This is one time step. Next time you dont need to create a project and an existing project will appear in the dropdown. Image below will show how our unit project will look like.

app.config will consist of the database connection string that we want to test against.SqlDatabaseSetup.cs file will use this configuration settings and initialize the test assembly.GetAccountReceivableSummary.cs is the actual test class. Next we will see how to setup the test case.

When you right click on this file and select open that will open the designer file. That will look like this.

By default you can see the actual execution command of the stored procedure. But wait,What if I want to test this against my dev/production DB without affecting the existing data and how can I do that?

The solution would be using the pre-test, post-test scripts. Each test will consist of their own pre and post test conditions. There you can arrange the data. Here what I am going to do is, For testing purpose I am going to create some sample data and then after test execution I am going to delete all the records.So i can even run these against any DB with existing data.

To specify pre-test conditions at the top dropdown we need to select the pre-test option and need to specify our data arrangement tSQL query.

My sample pre-test arrangement will look like this.

Here I am inserting some sample data for my test. Same way we can specify post-test arrangement.Next we will focus on how we can specify the test conditions/asserts. In the bottom of the test you can see the test conditions.

using the + symbol you can specify n number of conditions. There are plenty of test condition types.

Here I am going to check the result values.So I am going for the scalar value option. There you can specify whats the value you are expecting in the property window.My sample conditions.

If you want to edit an expected value then you need to right click on the test condition and need to select the property. This will open the properties window. There you can edit the value.

Finally will execute the test and see the results. To execute the test in the test explorer you can select the test and run that.

We got the expected result and the test passed.

In the next section we will see how to add more test cases to one test class and how to add this to azure devops pipeline and automate the build and test.