Category: Database Unit Testing

In my previous research about database unit testing in SQL Server Data Tools I’ve found that data inserted into tables during test had not been deleted automatically. You should create a cleanup action manually.

• Choose an item Post-test in dropdown list shown below.

• Click the link Click here to create in the center of Test Designer.
• Write T-SQL code to delete test data.

I truncate table Clients and run my test. Test is OK and there’s no test data in Clients table. So I could run my test as many times as needed.

There’s a possibility to include test condition even in post-test phase. Wonderful, I’ll check that test data is deleted successfully.

• Add simple SELECT command to query rows with test guid.
• Add test condition to check no data is returned. This can be made with Row Count = 0 condition or Empty ResultSet condition. I choose the second.

I have a very simple database for experiments with SQL Server unit tests. ClientsDB database contains just one table Clients with no records:

I have created a database project ClientsDatabase and a SQL Server unit test project ClientsDBTestProject to test my stored procedure. The autogenerated test (slightly formatted for readability) is shown below.

To create a simple test we must do:

1. change assignments in first SELECT command and set test parameters such as client guid and name;
2. delete the last SELECT command because the stored procedure does not return any value (there’s no RETURN statement in SP);
3. insert a SELECT command before EXECUTE command to check that there’s no row with the test guid in Clients table;
4. add a SELECT command after EXECUTE command to check that row with the test guid has inserted into Clients table;
5. delete the autogenerated test condition inconclusiveCondition1;
6. add first test conditions with type Row Count. Set the property Row Count to 0. Also you should check that property ResultSet is 1;
7. add second test condition with type Row Count. Set the property Row Count to 1 and change ResultSet to 2.

Finally our unit test should look like this:

Now we are ready to start our unit test.

• Open Test Explorer (Test -> Windows -> Test Explorer)
• Right-click dbo_CreateClientTest, then Run Selected Tests or simply click Run All because we have only one unit test.
• If test has completed successfully, we see a green mark near the test name.