What This Article Contains?
This article explains how to use SQL unit testing feature to make the application more robust. Although we can use sample AdventureWorks db provided by Microsoft, it is too big for this article and we will be creating a very simple database to avoid confusion and cover all the topics. DB Scripts are attached wherever required, just copy and execute in your version of SQL Server.
What This Article Doesn’t Contain?
It doesn’t contain implementation steps with Visual Studio 2015 or before, although same steps may work for VS 2015 or earlier versions.
What is SQL Unit Testing?
SQL Unit testing refers to standalone unit testing of data, schema, stored procedures, functions, views and triggers of database. Like Code unit testing through NUnit or Visual Studio test cases, UI testing with Coded UI, SQL testing also plays an important role in making an application robust and error-free.
SQL Unit testing is now available with community editions of VS 2015 and VS 2017, which is one more reason to use this. Earlier, this feature was available only with paid version of VS. It is available since the release of VS 2010 professional + edition.
Software Requirements
Visual Studio Community 2017, SQL Server 2012, and SQL Server Data Tools (SSDT). SSDT is a free tool and can be downloaded without providing any sign-in credentials.
Note that, SSDT corresponding to Visual Studio version should be installed, i.e., if you are using VS 2015, install SSDT version available for VS 2015. If you are using VS 2013, install SSDT which supports VS 2013. The older version of installed SSDT will not work with new version of Visual Studio.
It is recommended to use SQL Server 2012 + and VS 2012 +.
2. Create a Database and Add Tables
Create a database with name "DBUnitTesting". And then, execute the attached script. If you want to keep different name of database, modify script and replace "DBUnitTesting" with a new name.
Following is the Database Diagram of table structure and relationships. Three tables are created: Company, Department, and SubDepartment. Here, CompanyId is used in Department table as a Foreign key reference, and DepartmentId is used in SubDepartment table as a Foreign key reference. SubDepartmentLog table is used for audit purposes. Audit tables shouldn’t contain any foreign key link.
Create Tables Script
Use "CreateTables.txt" from attached "Scripts" zip folder.
3. Create a Project in Visual Studio 2017
Go to File-> New->Project-> SQL Server-> SQL Server Database Project. This option will be displayed if corresponding SSDT is installed properly on the system. Enter any name, say "SQLUnitTestingDB".
Right click and select "Import" -> Database. Provide valid credentials and click "Start".
In few seconds, schema will be imported to the code. Click "Finish".
Add new project -> class (.NET Framework), name it "TestCases". Add the following references:
- Add reference of "
SQLUnitTestingDB" project created earlier, because it contains schema detail. - Add Nuget package -
Microsoft.VisualStudio.QualityTools.UnitTestFramework.Updated
Right-click, Add New Item, select SQL Server, and then select "SQL Server Unit Test". Since, we will test Company table first, name it "CompanyUnitTest".
Why We Created a Separate Class?
We can also add a SQL Server Unit Test in "SQLUnitTestingDB" project but it is a good practice to separate unit test cases from the project which contains schema information. This helps in better maintenance of projects in the long-run when several test cases are added.
3. Create First Unit Test
Go to "CompanyUnitTest.cs" and rename:
- "
SqlTest1" to "RowCountCompany" - "
SqlTest1Data" to "RowCountCompanyData"
The proper naming helps in better maintenance of project. "RowCountCompany" will reflect in test explorer.
Double click "CompanyUnitTest.cs", designer will open. There are three options: Pre-Test, Test, Post-Test options visible in designer.
Pre-test: It is generally used, but not limited to, to set certain conditions required before actual test is executed.
Test: The main command or operation.
Post-Test: It is generally used, but not limited to, if we need to clean up table data after performing the operation.
It is not mandatory to use all three options. For now, we will use Test option. Double click "CompanyUnitTest" and designer will open. On top left, modified name of test case "RowCountCompany" is visible.
Now, consider, as per business rule, database should have four companies. Please note, unit test should not be written according to actual data present in database but in accordance with business rules specified in business requirement specification document. This is very – very important, otherwise it will fail the objective of writing unit test cases. Our objective is to compare business requirement document with actual data present and to measure the deviation. This is a very common mistake made by developers, when they start writing unit test cases for the first time, so avoid it.
Click on "click here to create" link and add the SQL query. Delete "Data Checksum" condition and add scalar value using "+" sign. As soon as it is added, property box is opened, fill 4 in "Expected Value" Column.
Save and open Test Explorer through Test->Windows->Test Explorer. Right-click and click "Run Selected Test". Since data is correct in database, it will pass.
Now, suppose, there is incorrect data in database. There are more companies added in database, but not specified in business requirement specification document.
Run test again in test explorer. It will fail and specify the reason also. This is how we know that our data is incorrect and must be fixed.
4. Unit Test Options
SQL unit test provides multiple options such as:
- Data Checksum: This ensures that data in table has not changed.
Click on "Press to configure" in properties tab. Click "Select Connection" and select table. Click Retrieve and data is visible. Click ok.
Run test case in test explorer. It will pass. During development, suppose, "Toboc" is erroneously modified to "Tobo" in Row 4. Run test again and it will fail. So, Data checksum is ensuring data integrity. Correct "Tobo" to "Toboc" and the test will pass again.
- Expected Schema: This checks the schema, not the data as "Data checksum" was doing. Even if data is modified but schema is intact, this test case will pass.
Select "Press to configure" option and Click "Select Connection", select table. Click Retrieve and data is visible. Click ok.
To demo this, again update "Toboc" to "Tobo" and run all the cases. We can see that "CompanyExpectedSchemaTest" has passed while "CompanyDataChecksumTest" has failed.
Add new column in company table, thus modifying the schema.
Run test again and it will fail. This option ensures schema is maintained.
- Row count: Set "
RowCount" as 4 in properties. Run test.
- Execution Time: It is used to check performance of query or stored procedure (discussed later in the document). Select "Execution time" as option and keep 00:00:00.0010000. We can see error because execution time taken is more than expected.
- Empty Result Set: This is used to verify if empty result set is received from database. If greater than 0 rows are returned, test fails.
- Not Empty Result Set: This is used to verify if empty result set is received from database. If no rows are returned, test fails. E.g. In case of error in stored procedure or query.
Both "Empty Result Set" and "Not Empty Result Set "are verifying the result set, but in a different way. Property "ResultSet" is read only in both the cases and can’t be modified.
- Inconclusive: This is a default test condition, whenever new test case is added. This test condition is included to indicate that test verification has not been implemented. Delete this test condition from your test after you have added other test conditions.
5. Understanding How Code Behind Works
Now, since we have a good understanding about the number of options provided in SQL unit testing mechanism, we will see how it works in code behind, i.e., in class.
SQL Designer makes it easy for us to write and visualize test cases but in class, it behaves the same as the code unit test cases. Refer to "CompanyRowCount" SQL unit test case. It has two files: resx and cs. Resx files contains the query details while cs class contains the expected output.
Resx File
Cs File
Debugging Test Cases
Modify condition of expected total row count to 3. Select a test case from Test Explorer. Right- click and select "Debug Selected Tests". As conditions don’t match, error is thrown with details. The behavior is same as Assert in code unit test.
Disabling Test Cases
If we set Enabled: False, test case won’t be tested during execution and is shown as passed. This option is used when test cases are written but work on corresponding functionality is still on but not completed. Later, as the project progresses, we can enable and execute all the test cases.
6. Verifying Stored Procedures, Functions, Views and Triggers
Stored Procedure
Create a stored procedure in SQL Server which inserts a new row in department table.
Stored Procedure Script
Use "StoredProcedure.txt" from attached "Scripts" zip folder.
Add new test case "DepartmentAdd".
- Select "Pre-test" and count number of records in department table and set expected value as
6.
- Select "Test" and use stored procedure to insert data in
Department table.
- Select "Post Test". Verify count again and then delete the data. Generally, post-test is used to clean-up the data used for testing purposes. In case we don’t delete the inserted row, next time test case will fail because row count is
7 and pre-test case says row count should be 6.
So, it is important that while testing stored procedures, etc., we should clean the temporary data inserted and keep it ready for next time. Our purpose of verification of Stored Procedure is achieved.
Function: Create a function "GetSearchTermCount". The function brings the count of records based on search term (performs like operation).
Function Script
Use "Function.txt" from attached "Scripts" zip folder.
Create a new test case and execute function. As "Software Development" and "Management" consists of "ent", 2 records are fetched.
Views
Create a View "FetchDetails".
View Script
Use "View.txt" from attached "Scripts" zip folder.
Create a new test case and execute view. Verify row count as ‘9’.
Triggers
Create a trigger to update "SubDepartmentLog" table if "SubDepartment" table is updated.
Trigger Script
Use "Trigger.txt" from attached "Scripts" zip folder.
Create a new test case and update department table. Count rows in "SubDepartmentLog" table. A new value is inserted because trigger is executed once record in "SubDepartment" is updated. And hence, row count returned is 1.
