Create SQL Server Database Unit Tests Using Visual Studio

Introduction

Writing database unit test cases are complimentary to the software development life cycle created by software developers. A developer can write unit tests cases to evaluate the success or failure of database design changes and check the results of stored procedures and functions. Unit test cases can ensure any changes applied on the database are as expected and do not introduce new errors. Unit tests, in addition, serve as documentation for users of the methods under test. Developers quickly can review unit tests to determine exactly how a particular database object should be consumed. By developing database unit tests, developers can create a collection of tests and run them during development to make sure all features work as expected.

Visual Studio’s database project type can perform unit testing against stored procedures to test expected output and behavior of your SQL scripts. An existing database SQL database can be imported into Visual Studio or new SQL script files defining schema, tables, and procedures can be written and imported into the solution. In this article, I will demonstrate how to set up unit tests using an existing database and by using new, custom SQL scripts.

Set Up Database and Create Scripts

Make sure you have a running database or create a new database before you import your SQL database in the Visual Studio editor. In this example, we will use a SQL Server database. Navigate to SQL Server Management Studio and open an existing database or create a new database. In Figure 1, I have shown the database used for demonstration.

SQL Database snapshot
Figure 1: SQL Database snapshot

Create a Visual Studio Database Project

Next, Open Visual Studio 2017 and create a new database project called MySQLTestDatabaseProject, as shown in Figure 2. All your database unit test scripts will be created in this project. The database project will contain all the various SQL objects associated with our database schema created in the previous step.

Visual Studio Database Project
Figure 2: Visual Studio Database Project

Import the Database in the Visual Studio Database Project

Next, you have to import the database schema inside the Visual Studio database project (see Figure 3). Right-click the database project, and, from the context menu, select to import a database. Enter the database server credentials (see Figure 4), server name, and select the database created earlier.

Import a SQL Database
Figure 3: Import a SQL Database

Add Database credentials during import
Figure 4: Add Database credentials during import

During import, select the import settings, as mentioned in Figure 5.

Select database connection source
Figure 5: Select database connection source

A summary will be displayed once the database schema has been imported. As per Figure 6, no issues or conflicts were encountered during the import.

Database imported successfully
Figure 6: Database imported successfully

Now, open the Solution Explorer from Visual Studio. You will see the database schema with all database objects are imported into the Visual Studio database project (see Figure 7).

Imported database inside Visual Studio
Figure 7: Imported database inside Visual Studio

Create Unit Test Scripts

We now are all set to create unit test scripts. Open the SQL Server Object Explorer pane from within Visual Studio. You will find that option in view menu, as you can see in Figure 8.

SQL server object explorer
Figure 8: SQL server object explorer

Next, navigate to the Projects folder and expand down to the stored procedure list (see Figure 9). In our example, only one stored procedure is listed.

SQL server database objects
Figure 9: SQL server database objects

Now, right-click the respective stored procedure and select the context menu and select Create Unit Tests option, as highlighted in Figure 10.

Create unit test case
Figure 10: Create unit test case

Next, select the C# project created earlier and enter a name for the new Unit Tests project (see Figure 11) and give the test class a name. Click OK.

Create New Visual Studio Test Project
Figure 11: Create New Visual Studio Test Project

In the next step, you have to associate a database for the tests to run against. Select the database name you have imported in the previous step from the drop-down, as shown in Figure 12, and click OK.

Select Data Connection
Figure 12: Select Data Connection

A code editor tab will open; from it, you can create your assertion tests against the stored procedure you have selected (see Figure 13). At the bottom, you will see the tests. By default, there is an inconclusive test. Delete this test by clicking the red X.

Code Editor with Test Script
Figure 13: Code Editor with Test Script

In the Test Type selection drop-down, you will find different options, as you can see from Figure 14. You have to create a pre-test condition to execute a test case before your actual unit test case runs.

Test Types
Figure 14: Test Types

Now, we will create a few tests for this procedure to determine a count of the number of rows returned, verify that no empty result set is returned, test a result set [row, column] value, and perform a checksum on the result set returned (see Figure 15). All these options are available in the Test condition drop-down. You have to select the appropriate condition, click the plus sign, right-click the assertion, and select properties.

Test Conditions
Figure 15: Test Conditions

After adding the test conditions, open the Test Explorer to run the test. You will find the Test explorer option in the Visual Studio Test menu, as shown in Figure 16.

Test Explorer
Figure 16: Test Explorer

Now, select the Test from Test explorer, right-click, and select Run Selected Tests (see Figure 17).

Run Test Case
Figure 17: Run Test Case

The test will fail or pass. Check the stored procedure logic or modify the test case depending on the error you are getting after execution as you see in Figure 18.

Test Case Execution Result
Figure 18: Test Case Execution Result

Conclusion

A developer should understand the importance of unit test cases. My suggestion to all database developers is to implement an advanced and cost-effective database unit testing framework that can help your team in early bug detection.

Thanks for reading my post. Happy Coding!

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read