Click here to Skip to main content
14,391,232 members

Data Driven Testing by Excel Named Region

Rate this:
4.33 (2 votes)
Please Sign up or sign in to vote.
4.33 (2 votes)
1 Aug 2013CPOL
Data Driven Testing in MSTest by using Excel as data source and Named Region as table name instead of sheet name


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

Image 1

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

Image 2

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.


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.

Image 3

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:

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

        dataTableName="AddNumber" dataAccessMethod="Sequential"/>

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

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

Image 4

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

Image 5

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

India India
No Biography provided

Comments and Discussions

GeneralMy vote of 3 Pin
satish koladiya27-Aug-13 0:42
professionalsatish koladiya27-Aug-13 0:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 1 Aug 2013


5 bookmarked