Managing Test Databases for VSTS

I'm having fun playing with data driven testing in VSTS. In theory data driven testing is easy, but there are some nuances that you have to think about, in particular where do you store the data to run the data driven test. Before we look at that let's look at how to setup a DataDriven test.

You need a couple of things to do this, firstly your test class needs a property of type TestContext, the context holds references to various things that the data driven test needs. We'll talk about the details in a sec. You then need to write your test. The test looks something like:

 [TestMethod] [DataSource("System.Data.SqlClient",
   "server=.\\SQLExpress;Initial Catalog=StarshipTestData;Integrated Security=True",
   "FireTorpedoesTest",
 DataAccessMethod.Sequential)]
public void TestFirePhotonTorpedoe()
{

The TestMethod attribute marks the method as a test, and the DataSource attribute provides the location of data needed for a data driven test. Here we specify that the test database is managed by SQLExpress; the database is 'TestData' and the database is FireTorpedoesTest.

The table for this test looks like this

CREATE TABLE dbo.FireTorpedoesTest
(
   TestId int NOT NULL PRIMARY KEY,
   MaxTorpedoes int NOT NULL,
   TorpedoesToFire int NOT NULL,
   ShouldPass bit NOT NULL
)  ON [PRIMARY]

The test method would be called once per test. Within the body the test uses the TestContext's DataRow property to access the current row in the database (these will be presented sequentially or rnadmoyl (sic!) depending on the datasource parameters). The test would look something like:

public void TestFirePhotonTorpedoe()
{
    int id = (int)TestContext.DataRow[(int)Column.TestId];
    int maxTorpedoes = (int)TestContext.DataRow[(int)Column.MaxTorpedoes];
    int torpedoes = (int)TestContext.DataRow[(int)Column.TorpedoesToFire];
    bool shouldPass = (bool)TestContext.DataRow[(int)Column.ShouldPass];

    // test the class here
}

This is relatively easy to setup and manage. However the problem is with the DataSource attribute. At the moment this relies on the database being available within SQL Server or SQLExpress (or whatever other database you chhose to use), and in particular requires the data set be referenced directly through that server. This is an issue if you work in a team. maybe not everybody has access to a full SQL Server instance, or if they do maybe they don't have the rights to create and manage a database. A better solution would be to be able to pass the database around with the test code, and simply have the DataSource reference the MDF file (assuming you are using SQLExpress) that contains the data. (in fact the best solution is to create the database at the start of the test process, something I'll talk about at some other time). To pass the databse around you need to create it in a local directory, relative to the project), something like:

CREATE DATABASE StarshipTest ON PRIMARY (NAME=N'StarshipTest',
        FILENAME = 'C:\project\StarshipTest\data\StarShipTest.mdf') 

then in the DataSource do this

[DataSource("System.Data.SqlClient",
   "server=.\\SQLExpress;AttachDBFilename=C:\\project\\StarShipTest\\data\\StarShipTest.mdf;Integrated Security=True",
   "FireTorpedoesTest",
 DataAccessMethod.Sequential)]

The problem here is that, while this will work, the path used in the DataSource is absolute. Ideally you want a relative path as the project location on your machine is likely different to mine, so the first thought would be to try this

[DataSource("System.Data.SqlClient",
   "server=.\\SQLExpress;AttachDBFilename=data\\StarShipTest.mdf;Integrated Security=True",
   "FireTorpedoesTest",
 DataAccessMethod.Sequential)]

Unfortunately this doesn't work, connection string paths cannot be relative!

After scratching my head I came up with various solutions to this, based on the fact that ADO.Net 2.0 has a 'substitution string' called DataDirectory, you see this used by ASP.Net 2.0 (look in machine.config for an example) and until today I thought it was only used there, this entry should me how it can be used by any app.

This means the DataSource can look like this

[DataSource("System.Data.SqlClient",
   "server=.\\SQLExpress;AttachDBFilename=|DataDirectory|\\StarShipTest.mdf;Integrated Security=True",
   "FireTorpedoesTest",
 DataAccessMethod.Sequential)]

But that begs another question, where is |DataDirectory| in a VSTS test run? It turns out that VSTS creates a new directory for each run of the tests (called the test deployment directory), on my machine this directory is in <solution base>\TestResults\[username_machinename_datetimestring], where username is the currently logged in user, machine name is the name of this windows machine, the datatimestring is the time the test was started. So to use the |DataDirectory| syntax you have to copy the MDF and LDF files into the current deployment directory. This is easy to achieve, you simply annotate the test method with a DeploymentItem attribute. This attribute references a file or a directory, and whatever is referenced is copied into the deployment directory. It is a way of copying something needed for every test. Our method now looks like

[TestMethod]
[DeploymentItem("StarshipTest\\data\\")]
[DataSource("System.Data.SqlClient",
   "server=.\\SQLExpress;AttachDBFilename=|DataDirectory|\\StarShipTest.mdf;Integrated Security=True",
   "FireTorpedoesTest",
 DataAccessMethod.Sequential)]
public void TestFirePhotonTorpedoe()
{

And this works!

However, this is not the end of the story. The MDF file I use for this test contains one table of four columns with five rows of data, yet is 2 1/4 MB in size. Running the test suite hundreds of times would soon eat up gigabytes of disk space, it would be better to keep the data in one place and have the test reference it there. The article I referenced earlier points out that it is possible to change the location of the DataDirectory. To take advantage of this I added a ClassInitialize method. This is a static method that gets called once when the code for the test is first loaded. The method looked like

public static void ClassSetup(TestContext testContext)
{
    AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\project\StarShipTest\data\");
}

which puts us back where we started, i.e. we've hardcoded the path again.

VSTS to the rescue. In VSTS you can add an app.config file to the project, this file (post beta2) is copied into the build and deploy directories and renamed to [mytestdll].dll.config and is read and parsed by the runtime (NUnit has a very similar mechanism), which means that our code can read this file. Creating an app.config file that looked like this

<configuration>
  <appSettings>
    <add key="DataDirectory" value="C:\home\kevinj\Course\dotnet\VSTS\demos\TDD\Data\"/>
  </appSettings>
</configuration>

means our code can look like this:

public static void ClassSetup(TestContext testContext)
{
    AppDomain.CurrentDomain.SetData("DataDirectory", ConfigurationManager.AppSettings["DataDirectory"]);
}

This is much better, we have an app.config that contains the settings and code that reads it. This is fine if every developer has thier own app.config, i.e. if the app.config file is not part of version control. If it is part of version control then every developer will need to change the DataDirectory setting, which is not a great solution. What is needed is a way to get a version of app.config that can be version controlled but to allow every developer to have an independent configuration section. This can be done by using the file attribute of app.config giving us this

<configuration>
  <appSettings file="mysettings.config"/>
</configuration>

and a file called mysettings.config that looks like

<appSettings>
  <add key="DataDirectory" value="C:\home\kevinj\Course\dotnet\VSTS\demos\TDD\Data\"/>
</appSettings>

However, we're now back to the problem of getting mysettings.config into the deployment directory. The file has to be copied before the ClassInitialize method runs. Adding the DeploymentItem to ClassInitialize doesn't work, neither does adding it to TestInitialize, seems it only works with a TestMethod. According to MSDN DeploymentItem can be added to the class, but according to the compiler it can't :)

To solve this you have to use the test configuration framework. Open the configuration file (by default localtestrun.testrunconfig), in its default editor and there is a 'Deployment' page. In here you can specify that the files to be copied when the test starts to execute. Add the mysettings.config file here (it will be saved with a relative file name) and you can finally run the tests with the database file where you want it and not being copied to the deployment directory each time you execute the test. Of course the downside now is that if you have multiple configurations you need to edit each one, and you need to create mysettings.config for the test server.