Data-driven unit tests for managed code

The Microsoft unit testing code for managed code allows to create test methods that are executed with data automatically fetched from an external data source. This is very helpful because we can extend the data sets without modifying the test code. An external data source can be an SQL database, a CSV file or Excel document, an XML document, or anything else for which a provider for .NET exists. In this article, I will show how you can define test data using all these types of source and execute unit testing methods with it.

To understand how this works, let us consider the following implementation of complex numbers. The arithmetic operators are overloaded for the Complex type so we can perform operations such as additions, subtractions, and multiplications.

public struct Complex : IEquatable<Complex>
{
  public static readonly Complex Zero = new Complex(0, 0);
  public static readonly Complex One = new Complex(1, 0);
  public static readonly Complex ImaginaryOne = new Complex(0, 1);

  public double Real { get; }
  public double Imaginary { get; }

  public Complex(double r, double i)
  {
     Real = r;
     Imaginary = i;
  }

  public override string ToString() => $"{Real}+{Imaginary}i";

  public bool Equals(Complex other)
  {
     return Real.Equals(other.Real) && Imaginary.Equals(other.Imaginary);
  }

  public override bool Equals(object obj)
  {
     Complex other = (Complex)obj;
     return Real.Equals(other.Real) && Imaginary.Equals(other.Imaginary);
  }

  public override int GetHashCode()
  {
     return Real.GetHashCode() * 13 + Imaginary.GetHashCode();
  }

  public static Complex operator +(Complex a, Complex b) =>
     new Complex(a.Real + b.Real, a.Imaginary + b.Imaginary);
  public static Complex operator -(Complex a, Complex b) =>
     new Complex(a.Real - b.Real, a.Imaginary - b.Imaginary);
  public static Complex operator *(Complex a, Complex b) =>
     new Complex(a.Real * b.Real - a.Imaginary * b.Imaginary,
                 a.Real * b.Imaginary + a.Imaginary * b.Real);
}

For the purpose of this article, we will consider testing the multiplication operator.

Typically, for testing the multiplication, you would write code as the following:

[TestMethod]
public void Test_Multiply()
{
   Assert.AreEqual(new Complex(0, 0),
                   new Complex(1, 2) * new Complex(0, 0));
   Assert.AreEqual(new Complex(1, 2),
                   new Complex(1, 2) * new Complex(1, 0));
   Assert.AreEqual(new Complex(-2, 1),
                   new Complex(1, 2) * new Complex(0, 1));
   Assert.AreEqual(new Complex(-5, 10),
                   new Complex(1, 2) * new Complex(3, 4));
}

However, this has the drawback that data is hard coded and the more tests you want to write the more lines like these you need to add. Moreover, every time you want to add new or remove or change existing tests you need to modify the test method.

The alternative for this is data-driven unit tests with the data fetched from an external data sources. When writing a data-driven unit test you have to do the following:

• Create the source of data for your tests.
• Add a public property called TestContext of the type TestContext to your test class. The framework will automatically set a reference to the context object that contains information for the unit test such as various directory paths (deployment dir, test dir, results dir, etc.), but also the current row of data in a data-driven unit test.
• Create a test method and mark it with the DataSourceAttribute specifying a provider name, connection string, table name, and data access method. The data access method specifies how data is read from the source. The options are Sequential and Random (which should be self explainable), the latter being the default if none is specified.
• In the test method, use the TestContext object and its DataRow property to access the data for the current execution of the method. The test method is executed once for every row in the data source.

Let us see how we can do this with various external sources of data.

SQL Server databases

Let us start by creating a database that we will call ComplexTests. In this database, we add a table called Multiply that has six columns, representing the real and imaginary parts of three complex numbers. The first two numbers are the ones that we multiply (ar + ai*i and br + bi*i) and the third one is the expected result (er + ei*i). We can put the data from the previous unit testing method in this table as follows:

Having this, we can create the following data-driven test method:

[TestClass]
public class ComplexUnitTest
{
   public TestContext TestContext { get; set; }

   [DataTestMethod]
   [DataSource("System.Data.SqlClient",
               "Data Source=<servername>;Initial Catalog=ComplexTests;Integrated Security=True",
               "Multiply",
               DataAccessMethod.Sequential)]
   public void Test_Multiply_DataSource_SQL()
   {
      var ar = Convert.ToDouble(TestContext.DataRow["ar"]);
      var ai = Convert.ToDouble(TestContext.DataRow["ai"]);
      var br = Convert.ToDouble(TestContext.DataRow["br"]);
      var bi = Convert.ToDouble(TestContext.DataRow["bi"]);
      var er = Convert.ToDouble(TestContext.DataRow["er"]);
      var ei = Convert.ToDouble(TestContext.DataRow["ei"]);

      Assert.AreEqual(
         new Complex(er, ei),
         new Complex(ar, ai) * new Complex(br, bi));
   }
}

We have specified the following parameters for the DataSource attribute:

The execution of this test method will produce the following output in the Test Explorer:

 Test_Multiply_DataSource_SQL
   Source: ComplexUnitTest.cs line 121

Test has multiple result outcomes
   5 Passed

Results

    1)  Test_Multiply_DataSource_SQL
      Duration: 646 ms

    2)  Test_Multiply_DataSource_SQL (Data Row 0)
      Duration: 28 ms

    3)  Test_Multiply_DataSource_SQL (Data Row 1)
      Duration: < 1 ms

    4)  Test_Multiply_DataSource_SQL (Data Row 2)
      Duration: < 1 ms

    5)  Test_Multiply_DataSource_SQL (Data Row 3)
      Duration: < 1 ms

If any test fails, it will be reported so, but the test method will be called again with data from the other rows in the source.

 Test_Multiply_DataSource_SQL
   Source: ComplexUnitTest.cs line 121

Test has multiple result outcomes
   3 Passed
   2 Failed

Results

    1)  Test_Multiply_DataSource_SQL
      Duration: 611 ms

    2)  Test_Multiply_DataSource_SQL (Data Row 0)
      Duration: 80 ms

      Message: 
        Assert.AreEqual failed. Expected:<0+1i>. Actual:<0+0i>. 
      Stack Trace: 
        ComplexUnitTest.Test_Multiply_DataSource_SQL() line 130

    3)  Test_Multiply_DataSource_SQL (Data Row 1)
      Duration: < 1 ms

    4)  Test_Multiply_DataSource_SQL (Data Row 2)
      Duration: < 1 ms

    5)  Test_Multiply_DataSource_SQL (Data Row 3)
      Duration: < 1 ms

XML documents

We can put the test data in an XML document, that we will called TestMultiplication.xml, like the following:

<?xml version='1.0' encoding='UTF-8' ?>
<data>
    <row>
        <ar>1</ar>
        <ai>2</ai>
        <br>0</br>
        <bi>0</bi>
        <er>0</er>
        <ei>0</ei>
    </row>
    <row>
        <ar>1</ar>
        <ai>2</ai>
        <br>1</br>
        <bi>0</bi>
        <er>1</er>
        <ei>2</ei>
    </row>
    <row>
        <ar>1</ar>
        <ai>2</ai>
        <br>0</br>
        <bi>1</bi>
        <er>-2</er>
        <ei>1</ei>
    </row>
    <row>
        <ar>1</ar>
        <ai>2</ai>
        <br>3</br>
        <bi>4</bi>
        <er>-5</er>
        <ei>10</ei>
    </row>
</data>

A test method that is executed with data from this document should look as following:

[DataTestMethod]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML",
            @"|DataDirectory|\TestMultiplication.xml",
            "row",
            DataAccessMethod.Sequential)]
public void Test_Multiply_DataSource_XML()
{
   var ar = Convert.ToDouble(TestContext.DataRow["ar"]);
   var ai = Convert.ToDouble(TestContext.DataRow["ai"]);
   var br = Convert.ToDouble(TestContext.DataRow["br"]);
   var bi = Convert.ToDouble(TestContext.DataRow["bi"]);
   var er = Convert.ToDouble(TestContext.DataRow["er"]);
   var ei = Convert.ToDouble(TestContext.DataRow["ei"]);

   Assert.AreEqual(
      new Complex(er, ei),
      new Complex(ar, ai) * new Complex(br, bi));
}

We have specified the following parameters for the DataSource attribute:

If you compare the two test methods we wrote so far you will notice that the only difference between them is the parameters supplied to the DataSource attribute.

CSV files

Using an XML document can be cumbersome because of all the tags you need to write. Having the data in a comma-separated values file is simpler, at least in the case of the kind of tests we are doing. The following is the content of a CSV file called TestMultiplication.csv:

ar,ai,br,bi,er,ei
1, 2, 0, 0, 0, 0
1, 2, 1, 0, 1, 2
1, 2, 0, 1, -2, 1
1, 2, 3, 4, -5, 10

The first line is the names of the columns. The others are rows of data, the same we had in the previous examples. This time, the test method will be as following:

[DataTestMethod]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV",
            @"|DataDirectory|\TestMultiplication.csv",
            "TestMultiplication#csv",
            DataAccessMethod.Sequential)]
public void Test_Multiply_DataSource_CSV()
{
   var ar = Convert.ToDouble(TestContext.DataRow["ar"]);
   var ai = Convert.ToDouble(TestContext.DataRow["ai"]);
   var br = Convert.ToDouble(TestContext.DataRow["br"]);
   var bi = Convert.ToDouble(TestContext.DataRow["bi"]);
   var er = Convert.ToDouble(TestContext.DataRow["er"]);
   var ei = Convert.ToDouble(TestContext.DataRow["ei"]);

   Assert.AreEqual(
      new Complex(er, ei),
      new Complex(ar, ai) * new Complex(br, bi));
}

Again, the only difference is the parameters of the DataSource attribute, which are detailed below:

Excel documents

An alternative to a CSV file is an Excel document. The advantage of an Excel file is that you can have multiple sets of different data in different sheets. For this article, however, we’ll only have one sheet, called Multiply, in a document called TestMultiplication.xlsx.

The test method will look as follows:

[DataTestMethod]
[DataSource("System.Data.Odbc",
            @"Dsn=Excel Files;DriverID=790;dbq=|DataDirectory|\TestMultiplication.xlsx",
            "Multiply$",
            DataAccessMethod.Sequential)]
public void Test_Multiply_DataSource_Excel()
{
   var ar = Convert.ToDouble(TestContext.DataRow[0]);
   var ai = Convert.ToDouble(TestContext.DataRow[1]);
   var br = Convert.ToDouble(TestContext.DataRow[2]);
   var bi = Convert.ToDouble(TestContext.DataRow[3]);
   var er = Convert.ToDouble(TestContext.DataRow[4]);
   var ei = Convert.ToDouble(TestContext.DataRow[5]);

   Assert.AreEqual(
      new Complex(er, ei),
      new Complex(ar, ai) * new Complex(br, bi));
}

The method is slightly changed this time, because columns in the data row are accessed by their index. Excel does not support renaming columns, so we cannot give them names like ar, ai, etc. as we did in the previous examples. The parameters for the DataSource attribute are the following:

Troubleshooting

When your data source is an Excel document, make sure you have the driver that provides access to Excel documents properly installed. If the test fails with the following error message then either the path to the document is wrong or the driver is not installed.

The unit test adapter failed to connect to the data source or to read the data. For more information on troubleshooting this error, see “Troubleshooting Data-Driven Unit Tests” (http://go.microsoft.com/fwlink/?LinkId=62412) in the MSDN Library. Error details: ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object ‘Multiply$’. Make sure the object exists and that you spell its name and the path name correctly. If ‘Multiply$’ is not a local object, check your network connection or contact the server administrator.’

In order to read data from an Excel document you need to have the Microsoft Access Database Engine installed on your machine. You can check that you have it from Control Panel > Programs > Programs and Features > Uninstall or change Program.

If you don’t have it then you can download it from here: Microsoft Access Database Engine 2016 Redistributable. This is available both as 32- and 64-bit.
However, if you try to install it, you might run into the following errors:

The reason for this is that this installation is conflicting with existing Office components. Office Click-to-Run Extensibility is a key component of the Office suite and cannot be uninstalled. Therefore, in order to bypass this error, you need to do the following:

• Open a comment prompt elevated as administrator.
• Run the redistributable executable with the /quiet option:

  AccessDatabaseEngine.exe /quiet

• Verify in Control Panel that the engine has been properly installed.

Defining data sources in App.Config

Rather than specifying the connection strings and the other data source parameters in code, we can actually put them in the application configuration file. To do so, you need to add a custom configuration section as follows:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <configSections>
      <section name="microsoft.visualstudio.testtools"
               type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.TestPlatform.TestFramework.Extensions"/>
   </configSections>
   <connectionStrings>
   </connectionStrings>
   <microsoft.visualstudio.testtools>
      <dataSources>
      </dataSources>
   </microsoft.visualstudio.testtools>
</configuration>

Then, you need to add connection strings and data sources. For a connection string you must specify a name, the actual connection string, and a provider name. For a data source, you must specify a name, the name of the connection string defined in the other section that this source is using, a table name, and the data access method. For the four data sources used for the data test methods shown earlier, we can define the following data sources in App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <configSections>
      <section name="microsoft.visualstudio.testtools"
               type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.TestPlatform.TestFramework.Extensions"/>
   </configSections>
   <connectionStrings>
      <add name="SQLConn" 
           connectionString="Data Source=<servername>;Initial Catalog=ComplexTests;Integrated Security=True" 
           providerName="System.Data.SqlClient" />
      <add name="XMLConn" 
           connectionString="|DataDirectory|\TestMultiplication.xml" 
           providerName="Microsoft.VisualStudio.TestTools.DataSource.XML" />
      <add name="CSVConn" 
           connectionString="|DataDirectory|\TestMultiplication.csv" 
           providerName="Microsoft.VisualStudio.TestTools.DataSource.CSV" />
      <add name="EXLConn" 
           connectionString="Dsn=Excel Files;DriverID=790;dbq=|DataDirectory|\TestMultiplication.xlsx" 
           providerName="System.Data.Odbc" />
   </connectionStrings>
   <microsoft.visualstudio.testtools>
      <dataSources>
         <add name="SQLDataSource" 
              connectionString="SQLConn" 
              dataTableName="Multiply" 
              dataAccessMethod="Sequential"/>
         <add name="XMLDataSource" 
              connectionString="XMLConn" 
              dataTableName="row" 
              dataAccessMethod="Sequential"/>
         <add name="CSVDataSource"
              connectionString="CSVConn"
              dataTableName="TestMultiplication#csv"
              dataAccessMethod="Sequential"/>
         <add name="EXLDataSource"
              connectionString="EXLConn"
              dataTableName="Multiply$"
              dataAccessMethod="Sequential"/>
      </dataSources>
   </microsoft.visualstudio.testtools>
</configuration>

Having these data sources defined like this, we can pass their names to the constructor of the DataSource attribute. The implementation of the methods is the same, therefore, for simplicity, it’s skipped it in the next listing:

[DataTestMethod]
[DataSource("SQLDataSource")]
public void Test_Multiply_DataSource_SQL()
{
   // ...
}

[DataTestMethod]
[DataSource("XMLDataSource")]
public void Test_Multiply_DataSource_XML()
{
   // ...
}

[DataTestMethod]
[DataSource("CSVDataSource")]
public void Test_Multiply_DataSource_CSV()
{
   // ...
}

[DataTestMethod]
[DataSource("EXLDataSource")]
public void Test_Multiply_DataSource_Excel()
{
   // ...
}

References

For further readings on the topic of data-driven unit testing with the Microsoft unit testing framework see:

• How to: Create a data-driven unit test
• Create a data-driven coded UI test
• Walkthrough: Using a configuration file to define a data source
• Data Driven Testing by Excel Named Region
• MsTest DataDriven tests from Xml and CSV files