DeCare Systems Ireland Blog

Data-Driven Unit Tests are very powerful but, it seems to me, rarely used feature in Visual Studio. Although they are not suitable in all situations they can be of a great help. Personally, I used them to drive testing of business rules. In one of the scenarios I had 9 parameters and they had about 900 possible combinations. I was able to cut the number of possible tests and I did so, but the point is that in some scenarios just typing in test data into source code is very laborious and error prone and can be easily avoided.

Intro

There are basically 3 ways to data-drive your unit tests in Visual Studio:

I prefer Excel files to drive my tests. All the tools are there to create your data, it is the fastest to setup and you don’t need external dependencies. For database you need to use database server, then question is if all developers have the access, or if every developer should have server locally installed, again what happens if you want to work from home? If you want to use XML it is insane to type it in a text editor, you need tools. CSV seems simplest but I had issues with formatting and it was real pain to maintain.

The problem with Excel might be that you don’t have it You can use Google Documents or Open Office for free with the same effect.

Step by Step

Unfortunately New Test Data Source Wizard won’t help you much when it comes to excel files, so we’ll have to do it manually. I prepared some simple test solution (Visual Studio 2008) with sample excel file to use for testing. First of all create your unit tests project as you would normally do. Then add some test data file:

I added here testData.xls with properties Build Action: Content and Copy to Output Directory: Copy if newer should be set. We need action to be content so the file is copied to the output directory, and so we can later pick it up for our testing purposes by using DeploymentItemAttribute. Now we have to embelish our test method with some new attributes:

TestMethod attribute is there since this is a test method. DeploymentItem attribute is there since we want to deploy our testData.xls with our test. This attribute affects our DataSource attribute. I wanted to keep it simple, if the file is not deployed with the test then absolute path to the file is required and then there might be issues with developers having source in different folders.

DataSource attribute is interesting:

[DataSource(
“System.Data.Odbc”,
@”Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=testData.xls;DefaultDir=.”,
“Sheet1$”,
DataAccessMethod.Sequential)]

We are using Odbc provider, the name of the provider is the first parameter of the attribute. The next one is the connection string. Dbq parameter in the connection string is the file name of the excel file. Since the file will be deployed to the same folder where the test is going to be ran we can just use the file name (otherwise absolute path to the file would be required), and for the same reason we can use DefaultDir=.

The third parameters is the table name and since I haven’t changed the name of the worksheet it should be Sheet1$. At the end is data access method and it can be random or sequential.

This is how the content of the excel file might look like:

The first row are the column names later used in the test:

So this is a very simple test. The data is normally used as:

int a = (int)((double)TestContext.DataRow[”a”]);

TestContext.DataRow has all the data from the current row. One note is due: when you get number from excel it is always double, so in this case it has to be casted from object to double and then to int. Another note is due for Visual Studio 2005, to be able to access TestContext add the following code:

private TestContext testContext;
public TestContext TestContext
{
get { return testContext; }
set { testContext = value; }
}

It magically works, Visual Studio 2008 adds this code automatically for you.

I deliberately introduced a bug so this test won’t be successful so I can just show you couple of more things:

You get a nice report with all the results laid out in a table and you have an index of the data which had been used in the test in Data Row column. One tip: create Data Row column or name it whatever you want in your excel file so you can easily navigate to the data row that caused the test to fail:

It is by no means required but it is handy when you have 50 or more records in your excel file. You can use simple formula as shown on the picture to populate the column. Mind though that Visual Studio will use all the rows from the excel file as long as there is at least one populated column in the row.

Closing words

Having the separate data file to be used in your tests helps obviously separate your data and the test code, but especially keeps your test data clean and easily maintainable. Using excel for the test data files is just natural and familiar. Use it wherever it makes sense.