Introduction
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).
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.
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.
Once the database schema has been imported, a summary will be displayed, stating any issues\conflicts encountered.
The Fabrics database schema will be imported into the Visual Studio database project.
Create a Database Unit Test
Open the SQL Server Object Explorer pane, from within Visual Studio.
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).
Right click on the respective stored procedure and select the context menu Create Unit Tests… option.
Select C# as the project, enter a name for the new Unit Tests project and give the test class a name, click OK.
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.
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!)
Remove the last Select statement, as this will skew up your tests:
Test Conditions
There are 8 types of assertions in the Test Conditions dropdown.
We will create a couple of tests for this procedure, test that determines:
- Count the number of rows returned
- Verify that no empty resultset is returned
- Test a resultset [row, column] value
- Perform a checksum on the resultset returned – to verify that the resultset is that what is expected.
Verify Row Count
Select Row Count from the dropdown list and click on the plus sign, right click the assertion and select properties.
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.
Open the Test Explorer, to run the test:
If you right click the test dbo_Top10_OrderTotalLessThan50Test and select Run Selected Tests.
The test will fail, because we have stated that we are expecting 1 row to be returned, but 10 are actually returned.
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.
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.
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.
The checksum generated by the test:
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.
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.
Finally, we will add a Scalar test, this is to test a value in a row\column within the resultset.
Once 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.
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:
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).
Delete the default C# class UnitTest1.cs from the test project.
NB: This is a standard C# unit test project.
Integrate Database Unit tests into TFS Continuous Integration
I have connected to my Online TFS account and checked in the code.
I can queue a build….but!
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.
Miscellaneous
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.
- http://www.sqlshack.com/creating-using-crud-stored-procedures/
SSMS Tools to Generate CRUD Stored Procedures
Download the latest SSMS Tools pack (http://ssmstoolspack.com/Download) 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.
