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.

This feature is available only for .NET Framework.
At this time, data-driven unit testing from a data source is not supported for .NET Core.

Demo test project source:
DataDrivenTestsDemo.zip (4 downloads)

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.

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:

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:

We have specified the following parameters for the DataSource attribute:

Name Value Comments
providerInvariantName “System.Data.SqlClient”
connectionString “Data Source=<servername>;Initial Catalog=ComplexTests;Integrated Security=True” <servername> is a placeholder for the actual name of the SQL Server instance
tableName “Multiply”
dataAccessMethod DataAccessMethod.Sequential Sequential order of access.

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

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.

XML documents

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

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

We have specified the following parameters for the DataSource attribute:

Name Value Comments
providerInvariantName “Microsoft.VisualStudio.TestTools.DataSource.XML”
connectionString @”|DataDirectory|\TestMultiplication.xml” TestMultiplication.xml is the name of the XML document, and |DataDirectory| is a placeholder for the data directory (see this for details). The TestMultiplication.xml file is located directly in the data directory, but can be in other location relative to it. The use of the substitution string |DataDirectory| avoids hard-coding paths. If you do not set a value for it, the data directory is the application directory. You can change that by calling AppDomain.CurrentDomain.SetData("DataDirectory", path).
tableName “row” The name of the XML node that contains the data for a row.
dataAccessMethod DataAccessMethod.Sequential Sequential order of access.

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:

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:

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

Name Value Comments
providerInvariantName “Microsoft.VisualStudio.TestTools.DataSource.CSV”
connectionString @”|DataDirectory|\TestMultiplication.csv” The TestMultiplication.csv file is located in the data directory.
tableName “TestMultiplication#csv” The name of the table is the name of the file without extension followed by #csv.
dataAccessMethod DataAccessMethod.Sequential Sequential order of access.

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:

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:

Name Value Comments
providerInvariantName “System.Data.Odbc”
connectionString @”Dsn=Excel Files;DriverID=790;dbq=|DataDirectory|\TestMultiplication.xlsx” The connection string to the Excel file. Properties such as DriverID=790 or Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} are optional.
tableName “Multiply$” The name of the sheet following by the symbol $.
dataAccessMethod DataAccessMethod.Sequential Sequential order of access.

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:

  • 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:

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:

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:

References

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.