Data Driven Testing by Excel Named Region

Introduction

Tests are always based on some input values which is passed to method and validated with an expected result. But, how do you run the same test for a different test scenario by dynamically adding the new scenarios?

Data Driven Testing in MSTest offers to define different scenario by setting input parameter values and expected result as the data source for a unit test. It also allows adding new scenario in data source without any changes in test or source application. Newly added scenario can be evaluated by re-running the test application.

For example, following are the test scenario for first run

And, test scenarios with new scenario(s) added for second run

All scenarios including the newly added scenario are evaluated when the test application is executed.

This is achieved by defining Excel as data source and Named Region as table name instead of sheet name.

Background

Declare test data for all possible test scenarios in Excel which is the data source for test application.

For example,

In a single class which has 20 methods, you need to create 20 different sheets i.e. each sheet per method. In case you want to add new scenarios or create methods for other class, either you need to create new Excel file or new sheets for each method in existing test Excel file. This makes the Excel file size grow and difficult to navigate to respective method test cases.

To overcome this problem, you can use Excel Named Region as data source for defining the test data.

Create Excel Named Region

Select cell range to define the test data such as parameter value and expected result and assign unique name to selected cell range.

Configure Data Source

Define the data source information in configuration file such as Excel file name and Excel named region name

Define the below details in the app.config file:

<connectionStrings>
    <add name="MyExcelConn" connectionString="Dsn=Excel Files;dbq=data.xlsx;defaultdir=E:\bin\Debug\; 
      driverid=790;maxbuffersize=2048;pagetimeout=5" providerName="System.Data.Odbc" />
  </connectionStrings>
  <microsoft.visualstudio.testtools>
    <dataSources>
      <add name="AddNumber" connectionString="MyExcelConn" 
        dataTableName="AddNumber" dataAccessMethod="Sequential"/>
    </dataSources>
</microsoft.visualstudio.testtools>

Pass the name of data source which is defined in Config file (in above Config details, name is AddNumber) through method data source attribute.

[TestMethod()]
[DataSource("AddNumber")]
public void TestAddNumber()
{
    int firstParamValue = Convert.ToInt32(TestContext.DataRow[1]); 
    int secondParamValue = Convert.ToInt32(TestContext.DataRow[2]);
    int actualResult = (new DynamicFactory()).AddNumber(firstParamValue, secondParamValue);
    int expectedResult = Convert.ToInt32(TestContext.DataRow[3]);

    Assert.AreEqual<int>(expectedResult, actualResult, "x:<{0}> y:<{1}>", 
        new object[] { firstParamValue, secondParamValue });
}

Evaluate Test Driven Data

Below is output when the test application is ran for first time

Below is the output, when the test application is re-ran with new test scenarios added without doing any changes in test project.

Points of Interest

• Use Named Region as table instead of sheet for easy maintenance and navigation.
• New test scenario can be defined without any changes in test application.