26 Aug 2014

Integrating tsqlt with Team Foundation Build Services continuous build

As I've mentioned in a previous blog post I have been integrating a SQL Server unit test framework: tsqlt into my project life-cycle (click here to read more).  Within this post I will share how I got my tsqlt unit tests executing within the continuous integration solution and the results influencing the outcome of the build. The continuous integration platform I am using is Team Foundation Build Services 2012 (TFBS). However my approach will work with any continuous integration platform which handles MS-Build and MS-Test, like Team City.

Build Integration

As I had created my database unit tests within SQL Server Data Tools (SSDTs) integrating them into the continuous integration was a straightforward process.  By extending the build definition to include the unit test project into the list of solution / projects which have to be built.  As I had my own solution, which was listed within the build definition, for all the database projects and other related projects my unit tests were already included.

I also added a very useful MS-Build argument to the build definition: /p:GenerateProjectSpecificOutputFolder=true which puts the outputs of the projects in their own separate folders. This was another way of separating the unit test framework and the unit test from the application database.

Unit Test Execution

Integrating the execution of the database unit tests could be achieved by writing a new unit test adapter to execute the tsqlt unit tests and report the results back to TFBS. However I decided to use an easier approach by making use of the SSDTs extension within MS-Test.  I use MS-Test database unit test to execute the tsqlt unit test and to check the execution result. I have reasons why I don't use MS-Tests to unit test my application database. These can be found in a previous blog post (click here to find out more).

I structured my MS-Test project to mirror the approach taken within tsqlt.  By having a MS-Test class for each tsqlt test schema. Then within each test class have one MS-Test database unit test per tsqlt unit test, using the same names minus spaces and the beginning “test”. This will make it easier seeing the results of the tsqlt unit test within result viewer.

Within the MS-Test database unit test the following SQL pattern was used:

   1:  --Execute the unit tests
   2:  EXECUTE [tSQLt].[Run] @TestName = N'[MyUnitTestClass].[test My Unit Test for My database Application]';
   3:  --Then check the results using the following SQL
   4:  SELECT [tr].[Result]
   5:  , [tr].[Msg]
   6:  FROM [tSQLt].[TestResult] AS tr
   7:  WHERE [tr].[Class] = 'MyUnitTestClass'
   8:  AND [tr].[TestCase] = 'test My Unit Test for My database Application';

Then use the single value assert method to check row 1 column 1 contained the following value: Success.

I have been able to write a T4 code generator which connects to a SQL Server database with tsqlt unit tests and does the following:
  • Create a class file for each of the tsqlt test schema
  • Generate the database unit tests for each tsqlt test found within the schema.
You can download my code generator on the following link: https://onedrive.live.com/redir?resid=E6F77D4B3FF8D47C!385&authkey=!AH-1sMQPJIs_B0c&ithint=file%2c.zip.

The code generator requires the following prerequisites:
To use the code generator update the tsqltUnitTest.tt file with the following:
  • SQL Server: With the name of the SQL Server which contains the unit test database.
  • Database name: The name of the database containing the unit tests.
  • Test name space: With the name of your MS-Test project that output files will be copied to.
Then right mouse click on the tsqltUnitTest.tt and select the option of running custom tool. After the execution of the T4 template there will be a directory, with the same name as your name space setting, containing all the files which need to be copied to your MS-Test project.

To create the empty MS-Test, to copy the output of the code generator into, use the following steps:
  1. Add a new C# MS-Test project to the solution

  2. Add a SQL Server unit tests file. This will add all the necessary references and code to convert the MS-Test project into a SQL server test project.

  3. Delete the newly added SQL server unit test file.
Adding MS-Test Project to solution

To get the unit tests to execute successfully I had to alter the standard build process template. The alteration was to insert an activity, after build and before the MS-Test activities, to run a power shell script. The script would do a deployment of my application database. Then a deployment of the unit test project, which would include the framework, to the localdb.

Standard Build Process Template With Powershll Task

The reason for the extra step was to work around the problem of SQL package only executing Pre/Post deployment files of the dacpack of the referenced source parameter. I needed my application database post deployment script to be executed as this sets up all my reference data which my unit test require.

To integrate the unit tests execution I only had to update my build definition to start executing test based on DLL file pattern match which was: *test*.  And change the setting "failed the build if tests fail" to true.

Finally another useful advantage of using MS-Test is that database developers can deploy and test their code to localdb all within Visual Studio.  So now they can follow the same rules as their .Net counterparts by ensuring that no code is check-in before it passes its unit tests on their local machines.

further reading:


Anonymous said...

Hi - I've used this in anger and it is very effective. Easily customisable. Can you tell me where you got the background info on the template files - I've not come accross them before.


Steven Wright said...

Hi Simon
Here is a link about T4 Toolbox That I used: http://www.olegsych.com/2012/12/t4-toolbox-for-visual-studio-2012/.

Also for anyone else who wish know more about T4 I found this link helpfull: http://www.hanselman.com/blog/T4TextTemplateTransformationToolkitCodeGenerationBestKeptVisualStudioSecret.aspx

Anonymous said...

Visual Studio noob here again. Now that I've gotten some tests written, I want to automate testing. I installed the extension and loaded your project. It generates a file for the single test schema I have, but once moved into the Unit Test project, it won't compile. I'm not entirely sure I have an MS-Test project. I added the Visual C# Unit Test Project - is that MS-Test?

Some of the errors I get:
Expected ; or = (cannot specify constructor arguments in declaration)
Invalid token '{' in class, struct, or interface member declaration
A namespace annot directly contain members such as fields or methods

If I just need to set up MS-Test, a pointer to some instructions would be appreciated!

Lee Anne

Steven Wright said...

Hi Lee Anne.
After you create a C# Unit Test Project you will need to manually add a Sql Server Unit Test. This will then get visual studio to update the project with the required references, which I think is causing your problem.

Also it will add a require class file call: SqlDatabaseSetup.cs prompt you add connection string for the database the unit test will be executed against.

Steve Wright

Anonymous said...

Hi Steve,

I've wrapped your T4s into Nuget package for simplicity (https://www.nuget.org/packages/T4tSQLtMSTestCodeGen/1.0.0). Now you can just create new Unit Test project, add Nuget reference to T4tSQLtMSTestCodeGen, replace [YOUR DATABASE NAME] hardcoded in app.config and runme.tt and just save runme.tt to generate Unit Tests.

Source code is available here https://github.com/aiacovlev/T4tSQLtMSTestCodeGen
Hope this help,