Category Archives: SQL Server

Unit testing SQL development is generally more difficult than other, more “traditional”, application development, such as with .Net.  The nature of databases and transactions don’t always lend themselves to easily run a large number of small individual tests of things such as complicated queries or stored procedures.  Some of this is due to our ingrained design patterns as SQL developers, and some is due to a lack of tools.  Thankfully, on the subject of tools, there has been a lot of progress over the last several years.

A colleague of mine, Mark Wojciechowicz, recently took it upon himself to create a library that can be used in Visual Studio with the NUnit framework and the NUnit3TestAdaptor (both can be downloaded via Nuget) to conduct unit tests of SQL Server objects – aptly named SqlTest.  You can read his introductory post about SqlTest, and some of the deficiencies of existing tools, at his website –   https://www.thisintelligentlife.net/introducing-sqltest and check out the code on github.

In this post I am going to walk through my own experience setting up some simple unit tests with NUnit and SqlTest.  I only recently became familiar with unit testing for SQL Server myself,  so hopefully this post can help beginners to get started.   I hope to expand on this with a series of posts on more CICD in the near future including using SqlTest to test SSIS packages and integrating SqlTest based unit tests with TFS.

Setup

First we are going to need to set up a Database Project in Visual Studio and add a few NuGet packages to our solution.  In this example I used SQL Server 2016 and VS 2015, and I have a Solution called SandboxDB which contains a SandboxDB SQL Server DB project, and a SandboxDBTests C# class library project.  The SandboxDB database project is a simple local database with just a few basic tables and a stored procedure, InsertCustomer which just takes 3 parameters and INSERTs a record in to dbo.Customer.  The CREATE scripts for this sample db can be found here.  Any db project will work as long as it builds and the Tests are modified accordingly.

Before you are able to select the NuGet package manager option in VS, you first need to add a new C# class library project to your solution.  Once you have added a class library project, you can go to the below menu.

The three packages we need to install are:

 

After installing these 3 packages, your references should look like the below.  The SqlTest package should add a Samples folder within your class library project containing sample unit tests for SQL and SSIS.  The SandboxDBTests project contains the references to NUnit and SqlTest, as well as the Samples folder installed by SqlTest.  For now, we will exclude the SsisSample.cs file from the project and focus on the SQL db unit tests.

Writing a Test

Once installed, the next step is to edit the samples to fit our own db project, including setting the App.config file.  Below we set the connection string name to Sandbox, the Data Source to my named instance of SQL Server (localhost\MSSQLSERVER2016), and the Initial Catalog to Sandbox for our demo.  Later, this is also where we would configure the settings for our SSIS Server.

<configuration>
<connectionStrings>
<add name=”Sandbox” connectionString=”Data Source=localhost\MSSQLSERVER2016;Initial Catalog=Sandbox;Provider=sqloledb;Integrated Security=SSPI;” />
</connectionStrings>
<appSettings>
<add key=”ssisServer” value=”localhost” />
</appSettings>
</configuration>

Next, in the file generated as an example when we installed SqlTest, SqlSample.cs, we modify the SqlTestTarget and connection strings to match our project.  Such as:


public class SQLUnitUnderTest
{
TransactionScope scope;
SqlTest.SqlTestTarget SandboxDb;
[SetUp]
public void Setup()
{
scope = new TransactionScope();
SandboxDb = new SqlTest.SqlTestTarget("Sandbox");
}

Thanks to NUnit, we have a robust framework to Setup, TearDown, Arrange, Act, and Assert our tests. Setup prepares a new transaction and performs any common Db preperations, TearDown returns the database to its original state by rolling back any transactions used in the test. Arrange, Act, and Assert correspond to a Test’s specific preparation, execution of the unit of code that is being tested, and validating that the results are as expected, respectively.

Each [Test] block designates a separate unit test. It is helpful to name each method/Test with a naming standard such as UnitUnderTest_Action_ExpectedOutcome; such as InsertCustomer_Insert_OneRow – Stored procedure “InsertCustomer”, Action “INSERT”, expected outcome “one row”. After the Assert, the teardown method is called to rollback the transaction.

In the below example test, first we TRUNCATE the Customer table to set it up for the test, next we call the dbo.InsertCustomer stored procedure and pass in the required parameters, and lastly we do a SELECT COUNT(1) to make sure we return the expected value, one row.


[Test]
public void InsertCustomer_Insert_OneRow()
{
//Arrange
SandboxDb.ExecuteAdhoc($"TRUNCATE TABLE Customer;");
//Act
SandboxDb.ExecuteAdhoc($"Exec dbo.InsertCustomer 'Steve', 'Rogers','7/4/1920';");
//Assert
var actual = SandboxDb.GetActual("SELECT COUNT(1) AS TotalRows FROM dbo.Customer");
Assert.That(actual, Is.EqualTo(1));
//The teardown method runs after every test and will rollback all actions in one transaction
}


The NUnit framework is what provides the Assert.That method, and the various arguments, such as Is.EqualTo, which we can use to check our results.  In the above example, we are checking that the variable “actual” is equal to the integer 1.  There are a ton of possibilities built into NUnit assertions that you can use for your tests.

Running a Test

Once the code is set up and built, we are ready to run our test. Having installed the NuGet NUNIT3TestAdapter above, our NUnit SqlTests can be run directly from within Visual Studio. If the Test Explorer window is not open, go to Test > Windows > Test Explorer to open the pane. The Test Explorer should display something like the below. If it says there are no tests, make sure to build the project containing the tests. The blue “!” indicates that the test has yet to be run for this build.

Right click on the InserCustomer_Insert_OneRow test and click Run Test.  If all goes well, the test will pass.

Congrats! That’s all there is to it to run a simple Unit Test of a stored procedure using NUnit and SQLTest.  From here you can copy/paste the [Test] code block (this is how NUnit recognizes that a method is a test) and modify it accordingly to run various tests on various objects.  For each [Test] code block you add, you will get another test listed in the test explorer.  You can even sort the tests based on what class they are in to organize them for each object you want to test.

Had we messed up any of the SQL statements, or if a result is different than what is expected in the Assert, the test would fail, and throw an error with the SQL error message.  In this screenshot you can see I left out the FROM in the Assert SQL statement.

Summary

In this post we got started writing unit test for SQL Server DB projects using VS, NUnit, and SqlTest.  After writing one test, you’ll want to write another.  After you get started with these frameworks, you can begin to copy/paste much of the code in order to write more unit tests.  Adding another test for SqlTest is as simple as copying the [Test] code block and changing the tables and queries.  The small amount of upfront cost will return huge dividends later with improved code reliability and confidence.

In future posts I will review how to use SqlTest to write unit tests for SSIS packages, and hopefully how to integrate these SQL Server unit tests into a full TFS CICD solution.

Lastly, another shout out to Mark for taking the time to create the SqlTest library to help make unit testing SQL Server easier!