Click here to Skip to main content
15,614,665 members
Articles / Database Development / SQL Server
Posted 11 Nov 2014


29 bookmarked

Create SQL Server Database Unit Tests

Rate me:
Please Sign up or sign in to vote.
4.68/5 (9 votes)
11 Nov 2014CPOL7 min read
Create unit tests for your stored procedures\triggers\functions and integrate into TFS continuous integration cycle


Since Visual Studio 2005 (Team Edition for DB Pro), it has been possible to create SQL Server Database Tests (SSDT) projects. But with Visual Studio 2010+ professional or higher, this has become more mainstream with Visual Studios integration with SQL Server. SSDT introduces a new SQL Server Unit Test template and type.

In the past, you may have used a PONT (plain old NUnit test) to verify the validity of your stored procedure\function\triggers, by simply using the TestFixtureSetUp & TestFixtureTearDown annotation on the respective methods to add data to the database, execute the NUnit test and then clear down the database for the next NUnit test.

This approach has worked well for developers (over the years) as they were already comfortable developing NUnit\MSTest scripts in C#\VB.NET – just by adding ADO.NET commands to the unit tests, they then had the power to test the database schema also. Now, Visual Studio will automatically generate the plumbing code behind the scenes for you, allowing you to use the UI to create the respective assertion tests.

Database Scripts

Navigate to SQL Server Management Studio and open a new Query Analyser window, import and execute the SQL script FabricsDatabaseObjects.sql first and then import the data by executing the SQL script FabricsDatabaseData.sql (attached above).

Once you have executed the two scripts, you should have a database schema like the following, with one stored procedure and a new SQL server login (DatabaseTester).

Image 1

Create a Database Project

Before we can design any database unit tests, we first must create a database project to contain all the various SQL objects associated with our database schema. Open Visual Studio 2013 and create a new database project called FabricsDatabase.

Image 2

Import Existing Database Schema

If you already have a database schema designed, then right click on the database project, and from the context menu, select to import a database. Enter the database server credentials, server name and select the Fabrics database.

Image 3

Image 4Image 5

Once the database schema has been imported, a summary will be displayed, stating any issues\conflicts encountered.

Image 6

The Fabrics database schema will be imported into the Visual Studio database project.

Image 7

Create a Database Unit Test

Open the SQL Server Object Explorer pane, from within Visual Studio.

Image 8

Navigate to the Projects folder and expand down to the stored procedure we wish to create a unit test for (Top10_OrderTotalLessThan50 in our case).

Image 9

Right click on the respective stored procedure and select the context menu Create Unit Tests… option.

Image 10

Select C# as the project, enter a name for the new Unit Tests project and give the test class a name, click OK.

Image 11

For the completeness of the tests, you must associate a database for the tests to run against. Select the Fabrics database from the dropdown and click OK – ideally, this would be your CI or QA database server.

Image 12

A default pane will open, from which you can create your assertion tests against your stored procedure. At the bottom, you will see the tests, by default there is an inconclusive test. Delete this test, by clicking the red X (Tip: you may have to click it twice!)

Image 13

Remove the last Select statement, as this will skew up your tests:

Image 14

Test Conditions

There are 8 types of assertions in the Test Conditions dropdown.

Image 15

We will create a couple of tests for this procedure, test that determines:

  1. Count the number of rows returned
  2. Verify that no empty resultset is returned
  3. Test a resultset [row, column] value
  4. Perform a checksum on the resultset returned – to verify that the resultset is that what is expected.

Image 16

Verify Row Count

Select Row Count from the dropdown list and click on the plus sign, right click the assertion and select properties.

Image 17

The default settings for the row count test will verify that no rows are returned. We can run this test and see that it will fail.

Image 18

Open the Test Explorer, to run the test:

Image 19

If you right click the test dbo_Top10_OrderTotalLessThan50Test and select Run Selected Tests.

Image 20

The test will fail, because we have stated that we are expecting 1 row to be returned, but 10 are actually returned.

Image 21

To rectify the test, simply alter the properties settings by changing the Row Count to 10, save the changes and re-run the test and it should pass this time.

Image 22

Image 23

Add a Checksum test condition. This time, we will test all the data returned (resultset) in one go, by using a checksum value to compare against. Give the test a name and in the properties pane, select the Press to configure ellipse.

Image 24

This will display the configuration screen for the Checksum condition. Select the appropriate connection to use and then click on the Retrieve button to return that resultset in the bottom pane, and save. A checksum will be generated for this resultset – which the test will compare against to verify the assertion. This is a quick way to compare a whole resultset speedily.

Image 25

The checksum generated by the test:

Image 26

Let’s make the checksum test fail, by altering the data that will get returned, within the Client table edit the first-name Elton to Elton_Bert and re-run the tests.

Image 27

You will now get a failed checksum test, reporting to the checksum value. Thus, you can assume with confidence that the checksum is a simple way to verify a resultset. Alter the Client table and revert the first name changes, to correct the test.

Image 28

Finally, we will add a Scalar test, this is to test a value in a row\column within the resultset.

Image 29Once you have added and renamed your Scalar test, go to the properties and select the row and column number of the field that will be tested. In this case, Row 2 and column 7 (which is the totals field) – we will be testing that this value will be 1.9200000.

Image 30

Again rerun the tests, to verify that all the tests pass.

Pre & Post Test Conditions

Like any unit testing, there are setup and teardown sections. If you wish to have specific data within your database prior to the respective test, you would create a pre-test condition to do this:

Image 31

The pre-test editor will appear, from here you can enter any pre-test logic that needs to be performed within the database – this is not a location to execute C# logic – only SQL. The sane is for the Post-test section – where you would teardown\clean-up any database logic prior to the next test.

For example, I have created this database with test data, for black box testing, the data would be inserted\deleted prior to each test (it would be normal practice for the database to have static data already populated by the creation scripts).

Image 32

Delete the default C# class UnitTest1.cs from the test project.

NB: This is a standard C# unit test project.

Image 33

Integrate Database Unit tests into TFS Continuous Integration

I have connected to my Online TFS account and checked in the code.

Image 34

I can queue a build….but!Image 35

I will receive the following error each and every time I check in code, because the connection string is not valid to connect from the Online TFS repository to my PC – plus network firewalls will not allow the connection. But, if this was your own environment\network – your build will queue and your tests will succeed.

Image 36


Using Table-Adapter to Generate CRUD Stored Procedures

To create the CRUD procedures for a database based on the table structure, developers have always used the TableAdapter to quickly create the stored procedures and then quickly modify them for their specific needs – this approach will save you time. You will need to import the database into your Visual Studio project to get the latest database changes.


SSMS Tools to Generate CRUD Stored Procedures

Download the latest SSMS Tools pack ( SQL Server 2008+

If you then right click your database table, a new option SSMS Tools will be available, allowing you to generate the CRUD procedures.

Image 37


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Ireland Ireland
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionRequirements Pin
Ilhamm21-Mar-15 0:08
Ilhamm21-Mar-15 0:08 
QuestionDownload Visual Studio 2013 DB Project - 398 KB zip file name Pin
Laiju k11-Nov-14 16:41
professionalLaiju k11-Nov-14 16:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.