Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]

jamiet — Fri, 20 Aug 2010 18:41:00 GMT

[This blog post assumes a familiarity with the terms Continuous Integration (CI), MSBuild & MSTest. If you don’t have a good appreciation of those terms then this blog post probably isn’t for you anyway so don’t worry about it!]

Over the past few days I have been working to get database unit tests executing as part of our Continuous Integration (CI) build and in this blog post I’ll explain how I went about it because it is decidedly not straightforward.

We are using the DB Tools in Visual Studio 2010 (aka DBPro or Datadude - I will refer to it as Datadude from here on in) which includes unit testing functionality. The rest of this blog post also assumes a familiarity with database unit testing in Datadude although if you want to do some background reading an excellent place to start is Jamie Laflen’s whitepaper Apply Test-Driven Development to your Database Projects on MSDN.

We got to the point where we had a C# test project containing database unit tests that executed successfully inside Visual Studio. For demonstration purposes I have put together a solution that contains a simple database project and a test project containing a database unit test:

And just to prove that the test executes successfully in Visual Studio:

N.B. The code for the database object(s) and the unit test itself are not important, that is outside the scope of this blog post.

At this point we have some tests that run in our development sandbox, the configuration for which is done using the Database Test Configuration dialog:

Those config settings are stored in the app.config file which exists as part of our test project (screenshot of which is at the top of this blog post). If we take a look inside that file we can see the ConnectionString that we defined in the Database Test Configuration dialog:

Note the <DatabaseUnitTesting> element, we’re going to be coming back to that a little later!!

Once we have the tests running in Visual Studio the next step is to get them running inside a CI build and for that we call out to MSTest.exe from inside an MSBuild script:

When we execute that script we get some positive results and all looks peachy:

OK, we now have a test that runs successfully both in Visual Studio and by calling MSTest.exe directly from MSBuild. The problem I have to solve now though (and this is the real crux of this blog post) is that the test is still running against the server I specified in app.config; I don’t want this, I want the test to run against a server of my choosing, namely my build server. For this I had to call on the help of the aforementioned Jamie Laflen (who, luckily, I have met in the past) and he gave me guidance on how to achieve it. Below are the steps that you need to take.

Firstly you need to tell Datadude that you want to specify some different credentials and that is done by adding an attribute AllowConfigurationOverride="true" to the <DatabaseUnitTesting> element of app.config like so:

Don’t forget to build your test project after changing the app.config file!!!

This will cause datadude unit testing framework to go and search for a file called *.dbunittest.config where the * indicates either:

The name of machine upon which the tests are being run or
The name of the user running the tests

According to Jamie L datadude runs the following precedence checks:

Is an override specified in app.config? If not, use app.config
Does an override file exist called <machinename>.dbunittest.config? If so, use it, if not…
Does an override file exist called <username>.dbunittest.config? If so, use it, if not…
Fail!

“So”, you may be asking, “what goes in this *.dbunittest.config file then?”. Fair question, that’s where the <DatabaseUnitTesting> element that I mentioned earlier comes in. Copy that element from the app.config file into your *.dbunittest.config file, remove the AllowConfigurationOverride="true" attribute and change the ConnectionString property accordingly:

The next step is to tell MSTest.exe that there is another file that it needs to be aware of when it runs the tests and to do that we need to create a custom testsettings file that will be used instead of the default Local.testsettings. To create a new testsettings file use the Add New Item dialog:

In the Test Settings dialog go to ‘Deployment’, click ‘Enable deployment’, then ‘Add File…’. Browse to your *.dbunittest.config file and add it to the project:

At this point you’re good to go and all you need to do edit your call to MSTest.exe and tell it to use your new testsettings file:

Notice the extra information in the output:

You’ll have to trust me that it used the new config file and therefore the new ConnectionString, although having said that you do get some useful information in the test results file (*.trx) that MSTest.exe creates:

I think its fair to say that this process is a little fiddly to say the least so I’m hoping that Microsoft come up with a better solution in the future, one that integrates better into the whole testing infrastructure. Meanwhile you can download the demo that I put together for this blog post from my SkyDrive.

Hope that helps! Comments are welcome.

Jamiet

UPDATE: Atul Verma has written a 3-part blog series on how to write your database unit tests so if Jamie Laflen's whitepaper (linked to above) doesn't tickle your fancy try reading Atul's series instead:

SSIS Junkie : mstest

Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]