Data driven tests using Xunit, Excel & Selenium

So, what is is a unit testing tool for the .NET framework. For example, C# and It was created by the original inventor of the NUnit testing framework and seeks to address some of the shortcomings of the NUnit framework in use. It's a free and open source framework, and it's licensed under the Apache Version 2 license.When we create tests with, we can run them with the Visual Studio 2012/2013 Test Runner, we can run them in Resharper, in CodeRush with TestDriven.NET, and we can also execute them from the command line. For updates on xUnit, you can follow xunit@jamesnewkirk or bradwilson on Twitter. And the home of the project is at

So, with that background lets get started.

Create a class library project
Let's start by creating a class library project, targeting .NET 4.5 (or later). Open Visual Studio, and choose File > New > Project:

2. Extract and add the 2 dll’s as reference to your class project. Along with that you can also add the Selenium Webdriver if you have already downloaded or you can use the nuget package references for it.


3. Now you can go ahead and write your normal Selenium cases in the Xunit framework using the fixtures of Xunit , using either [Theory] or [Fact] .You can read more about this here

4. So, a simple usercase of gmail login is as follow

4. Now, if we want to make this test as data driven using Excel, we start off with first creating an excel data sheet. Let’s call it as SampleData.xls .


5. Create a named range in the excel sheet for the data. You can create the same by

  •    Highlight the desired range of cells in the worksheet and right click on it. Choose Define Name
  •   Type the desired name for that range in the name box, such as TestData

6. Once the named range is defined, we can save that as .xls and not .xlsx ( just ensure that it’s 2003 xls , for some reason, I never got it to work with .xlsx) in the solution folder. Also ensure that the properties of the excel is set to “copy always”


7. Now we need to add a new fixture below the [Theory] , called as [ExcelData] . The query statement implies that we select all data from the named range TestData. So the above code can be changed to data driven as below

8. Now, you can run these tests either using the Visual studio runner or the Resharper. There is another simple way to run the test. If you go to your Xunit folder where you downloaded the binaries from, you can see an exe – “xunit.gui.clr4.x86.exe” . You can launch this app, and point it to your dll from your debug folder and voila , the tests will run. ( Just remember to add the path to your environment variable)

9. In case you run into an error related to JetOLEDB,

Just change the Property of the project into x86 format

Project---> Properties--->Build--->Target Framework---> x86


Deleting a named range

  • Open Microsoft Excel, then click "File" and open the document containing the named range you want to delete.
  • Click the "Formulas" tab and click "Name Manager" in the Defined Names group. A window opens that contains a list of all the named ranges in the document.
  • Click the name you want to delete. If you want to delete multiple names in a contiguous group, press the "Shift" key while clicking each name. For names in a non-contiguous group, press "Ctrl" and click each name you want to delete.
  • Click "Delete," then confirm the deletion by clicking "OK."

Change a Named Range

  • Launch Microsoft Excel and open the file containing the name you want to replace.
  • Click the "Formulas" tab. Click "Name in Manager" under the Defined Names heading.
  • Click the name you want to replace, then click "Edit" in the Name Manager box.
  • Enter a new name for the range in the Name box. Change the reference for the name in the Refers To box. Click "OK."
  • Change the formula, constant or cell the name represents in the Refers To field in the Name Manager box.
  • Click "Commit" to accept the changes.

Cross Browser Playback in CodedUI

In effect , Cross Browser Playback is only useful to actually check UI differences between the different browsers. So the popular belief of replaying all the tests for testing the cross browser is just a myth. To improve your effectiveness, you may want to target specific tests at specific known UI problems in your app.   
Cross Browser Playback enables you to validate if your app is usable from different browsers. It also makes sense to create a few core end 2 end scenario’s you want to validate before you ship like purchasing an item from an online shop. You can also focus on critical business function that would seriously impact your business when stuff breaks. Thereby making playback resilient tests is crucial here so just ensure that your controls are  easy to identify, e.g. by ID across browsers


So, how do you get the ability to run the UI web tests you created in multiple browsers? First you need to have Visual Studio 2012 Update 1 or higher. So, this will not work with Visual Studio 2010 if you have not yet upgraded yet to the latest version of Visual Studio. The next thing you need to do is you need to go to the Visual Studio Gallery and there search for cross browser. Then you will find the Selenium components for Coded UI Cross Browser Testing. You can then download the installer, and then you need to install this package on every machine you want to play back the tests. So, when you have multiple test machines that are part of a test lab environment, for example in Team Foundation Server Lab Management, then you need to go to all these machines and install this package. You can also search for this package from the Visual Studio IDE. There you can go to the Tools menu, and there you go to the Extensions and Update menu. Here you can search the Visual Studio Gallery feed and then install straight from Visual Studio. Another thing of course that you need to install are the browsers Firefox and Chrome in order to play back on those browsers. One last thing to note is rather important, and that is that you can only record with Internet Explorer. So, if you choose to use the UI map files that we've discussed in the previous modules, then you can only record using Internet Explorer. You can still play back those recordings using the other browsers, but the recording itself needs to be done from IE.

Understanding cross Browser Playback
Look at the architecture of CodedUI. To understand how cross browser playback works we have to look at the bottom layer of this architectural diagram again. We know that CodedUI can work for any technology we'd like as long as there's a driver that can plug into the technology manager layer, and then we need to be able to select the right driver to run the test. Now, for cross browser playback what Microsoft did is write a switch in the web driver that can switch between the two technologies for playback. It still uses the standard implementation leveraging the MSHTML/DOM of Internet Explorer, but they now added the option to switch to a different engine called Selenium. Selenium is a technology solidly designed for browser testing. Selenium has the ability to play back scripts on different browsers for a few years now, and rather than building a competing technology, Microsoft adapted their engine to use the Selenium web driver to run the tests. You might ask yourself but what about Safari? I don't see that browser here in the playback browser symbols. Unfortunately that's true. There's no web driver in Selenium as well for supporting Safari, so that means that we can only play back on other WebKit-based browsers like Firefox and Chrome. This can give at least some confidence that it might work in the Apple WebKit-based browser, but unfortunately Google forked their implementation of WebKit for their browser so it becomes more likely each day that you will not find issues that might occur in Safari-based browsers because the browsers don't use theexact same rendering engine anymore.

How to Switch Browser on Playback
So, now we know what to install and how it works, but what do we need to do in our code to make this all work? The good news is almost nothing. The fact that Microsoft provides an implementation of their web driver in CodedUI that can switch technologies for playback makes switching browsers a breeze. The key element of making the switch is setting the current browser property of the BrowserWindow class. So, what we need to do is we need to specify the browser we want to use for playback. If we don't specify anything or IE, then this means it will be played back in Internet Explorer. If we set the current browser property to contain a text string Chrome before we call BrowserWindow.Launch, it will launch the Selenium Chrome web driver to run the test. If you specify the string Firefox, then it will use the default Selenium implementation that plays back on Firefox. One thing we of course also need to do is install the correct browsers on the machine, so we do need to install Google Chrome, Firefox, or Internet Explorer on the machine that runs the test.

Unsupported Features & Known Issues
So, there are a few caveats to look out for when using cross browser playback. Of course the thing we already discussed, we cannot play back on Safari-based browsers, and that's a problem we cannot fix other than by trying some of the key scenarios by hand and validating every now and then if you see differences in the browser behavior and watch out for those cases. The other problem that you might encounter is that search fails when it normally dependent on a filter to find the right control. A search is executed first based on the search properties, and when multiple controls are returned then a filter is applied to find the control in a set of returned controls. The cross browser implementation does not actually use the filter other than TagInstance, meaning that if your search relies on a filter on some property other than TagInstance of a control then the search will fail. To solve this problem you need to move the filter properties to the search properties. Since all the search properties are translated into a Selenium search, you will see that the search will then succeed. It's always best to try and use search as much as possible and try to keep away from filtering. But when using CodedUI record and playback, the filter properties are used more often, so therefore chances are that this will happen to you when you use record and playback, and it is less likely to happen when you hand code using the object model. Since search in Selenium is done in a different way, it is possible that you can get an error message Error Element does not exist in cache or that your search fails when an element appears delayed on the screen because JavaScript needs to complete on an AJAX call before it shows on the screen. In these cases there is a simple solution to fix this problem. The solution is to use the WaitForControlExist API and before we access any property on the control we can use the WaitForControlExist API to block the call until the control becomes available.

Data Driven tests in Coded UI using MSTest

Microsoft MSTest supports data driven tests where you can specify a data source and the test method will be executed for each row in the set , either in Sequential or Random mode based on your choice and need.
Since CodedUI is based on MSTest, we can build data driven UI tests as well. This enables interesting options to specify all data entry in a data source and not directly in your test method. This also enables functional testers to specify the test scenarios

Now, Different data sources can be used to drive the UITests. Some of them are
Comma Separated files (CSV)

  • XML
  • Excel (xls, xlsx)
  • Test Case from Microsoft Test Manager (MTM)
  • SQL Server

You can easily specify the data source as part of the test method declaration. Refer previous post for more clarity.

The Data Source attribute defines the data connection you want to use

Now, in your test method you can access the data row via the TestContext

For CSV,

The |DataDirectory| will resolve to the correct location at test as long as you deploy the csv file with your test. Data#csv is the table name you have to use with CSV

For SQL,

For XML,

For MTM,


FOr Excel,

If you have the actual connection available, so you can use ADO.NET to access additional sheets of data. E.g. let first sheet be the index into subsequent data sets or Query on named ranges


Deploying the required resources
Now, the question is how can you deploy your excel sheet with your tests? or how can you deploy assets required during the test run in general?
The answer lies within MSTest itself. MSTest has the option to specify deployment items. For this, you need to do the below things

  • Need to Set deploy to true in test settings file
  • Annotate the test class or method with DeploymentItem attribute

Add the assets to your build deployment

  • Add a folder named assets to your solution
  • Add files you need like excel sheets or pictures and set them to copy to output

Refer the DeploymentItem attribute to the assets folder

  • Use test context Deployment location to refer to the deployed item you need

Data driven tests using MSTest & Selenium

There are a lot of literature including the ones on Microsoft KB’s which talks about how we can make our tests data driven. These employ the use of additional app.config file. After many headaches, I have figured out a way to accomplish the same without the use of app.config.

Getting ready

1. Create a new C# unit test project in Microsoft Visual Studio 2012 and name it SeleniumDataDrivenTest

2. Add a reference to the WebDriver .NET binding

3. Create a excel sheet from where you want to read the data from. Let’s call that data sheet as Data.xlsx

You can parameterize a test in MSTEST by adding the Excel spreadsheet to deployment items of the test project, using the following steps:

1. Click on solution and choose to add a Test Setting and continue as shown in the following screenshot:



2. The Test Settings dialog will appear. We need to add the test data file by clicking the Add File button in the Deployment section, as shown in the following screenshot:


3. Once you add the file to the Deployment section, it will appear in the list, as shown in the following screenshot: Let's ensure that we create an excel sheet from where you want to read the data and call it as as Data.xlsx.



4. In the solution explorer, use the Show all files and your file "Data.xlsx" should now show up. Once it does , right click on it and choose include in project. Your solution show now resemble something like the screenshot below


5. Now right click on the "Data.xlsx" file and choose to modify it's properties. Set the Build Action to Content and Copy to output directory as either to "Copy always" or "Copy if newer". I always tend to leave it as "Copy always"


6. Now copy the below code to the class

How it works :

When we add the DataSource attribute to a test in MSTEST, it provides data source-specific information for data-driven testing to the framework.


It reads the test data from the source. In this example, the source is an Excel spreadsheet. The framework internally creates a DataTable object to store the values from the source.The TestContext test method provides a collection of data rows for parameterization. We can access a field by specifying its name, as follows:

With the DataSource attribute, we can specify the connection string or a configuration file to read data from a variety of sources including CSV File, Excel spreadsheets, XML files,or databases.

Powered by Blogger.


Total Pageviews

Copyright © T R I A G E D T E S T E R