SQL Server Unit Tests

This is a quick start for getting SQL Server unit tests going inside Visual Studio. This assumes that you already have a database and that you already have a development process and an installation process for your database. If you are starting completely from scratch then you may want to just follow Microsoft’s Walkthrough for Creating and Running SQL Server Unit tests.

Steps to create

  1. Open your solution add a new ‘Unit Test Project’
  2. Add a new Sql Server Unit Test file by right clicking the new unit test project and selecting ‘Add New Item…’
  3. You will be prompted to setup your SQL Server connection. Setup a connection to a new DB.
  4. Double click on SqlServerUnitTest1.cs to bring up database test designer.
    Database Test Designer
  5. The database test designer take a little bit getting used to. The top navigation bar has 3 areas. The first area is a drop down to select a test, the second area (circled in red in the image above) lets you pick pre-test, test, or post-test, and the 3rd area is for test management (add/remove/rename).
  6. In the database test designer on the top bar click the green plus sign to create a new test.
  7. Use the 2nd drop down in the top bar (circled in red) to select a pre-test. In the main window pane write whatever SQL is needed to setup your test. Note of caution: The pre-tests and post-tests also allow for test conditions. If you accidently add a condition to a pre-test your test may fail.
  8. Again using the 2nd drop down in the top bar (circled in red) select the test. Here you will write SQL that you want to test. For example this would be where you would call a stored procedure that you wish to test.
  9. The test will need test conditions. The bottom of the designer are test conditions. There are a limited set of built in test conditions. To create your own follow the Microsoft article on Custom Test Conditions. Just select a condition and click the green plus to add it. To edit what it tests use the properties window.

At this point you will have a SQL server test that you can run. There should have been a file, SqlDatabaseSetup.cs, that was automatically created that you can edit to automatically install your DB. You can also put any setup that you need to do, i.e. dropping the DB and creating test data inside this file.

Leave a Reply