Finds and Facts for Coded UI Test/C#

Shared Project

  • Shared Project does not produce a reusable assembly, therefore it can only be consumed within a solution;
  • Shared Project cannot have dependencies on third-party libraries;
  • Shared Project cannot be referenced by Test projects (Unit Test, Coded UI Test, etc.);
  • Conclusion: We cannot use Shared Project for CUIT.

Test Projects

  • Test Project can only reference .dll, NOT source code,
  • therefore Shared Project is not for Test Projects.

Data Source

  • Read only, there is no way to modify data source file in run time;
  • zero base for data row, header row is not considered for index;
  • Each Data Source data row corresponds one iteration, which includes [TestInitialize], [TestCleanup] and [TestMethod] with [DataSource];
  • workaround  to write/update Data Source.

WIP – code reuse

Class Library

  • Class Library can be referenced within solution as .dll, it will re-compile if there are changes.
  • Can Class library be referenced outside of a solution?
    • should a class library be added in a solution?
    • should a class library be referenced to a project outside of its original solution without being added to the solution?
  • Considering to put Class Library in a separate Git Repository from the Test solution?

Extension Methods

  • What is an Extension Method?
    Extension methods enable you to add methods to existing types without creating a new derived type, recompiling, or otherwise modifying the original type.
    An extension method is a special kind of static method, but they are called as if they were instance methods on the extended type.
  • How to use extension methods?
    An extension method is a static method of a static class, where the “this” modifier is applied to the first parameter. The type of the first parameter will be the type that is extended.
    Extension methods are only in scope when you explicitly import the namespace into your source code with a using directive.
  • What is the difference between an Extension Method and a regular class library method?
  • extension methods must be public static? – Static yes, public?
  • What should the ExcelReadWrite class be, regular class library or extension method?
    • not even necessary for a regular class library, but still can be think of.

Functional Library


.xlsx Excel files as Data Source in Coded UI test

I want to use “.xlsx” format Excel file as Data Source in my hand coding Coded UI test. The best information I get is found in MSDN: Creating a Data-Driven Coded UI Test, which has useful information regarding of various data format as below, but it doesn’t mention about the “.xlsx” format.
After some more googling and experiment, I finally managed to get it to work. In order to save time for me and someone who might face the same challenge again, I think it’s beneficial to document the connection strings and some key points to pay attention to here as later reference.
The following are the data source strings found in the MSDN page:

Data Source Types and Attributes

  • CSV
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV", "|DataDirectory|\\data.csv", "data#csv", DataAccessMethod.Sequential), DeploymentItem("data.csv")]
  • Excel
[DataSource("System.Data.Odbc", "Dsn=ExcelFiles;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true”, "Sheet1$", DataAccessMethod.Sequential), DeploymentItem("Sheet1.xls")]
  • Test case in Team Foundation Server
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.TestCase", "http://vlm13261329:8080/tfs/DefaultCollection;Agile", "30", DataAccessMethod.Sequential)]
  • XML
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML", "|DataDirectory|\\data.xml", "Iterations", DataAccessMethod.Sequential), DeploymentItem("data.xml")]
  • SQL Express
[DataSource("System.Data.SqlClient", "Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=True", "Data", DataAccessMethod.Sequential)]

I know I can convert the “.xlsx” format into the old format “.xls”, but I rather have more options than less.Here is the Attributes:

Data Source Types and Attributes

  • Excel, “.xlsx” format
[DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\data.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true", "Sheet1$", DataAccessMethod.Sequential)]

where data.xlsx is the Excel file you want to use as Data Source, Sheet1$ is the worksheet you are using in the Excel file. Using this to replace the normal [TestMethod] attribute before the test method.

If this is not working, check the following:

  1. Add data.xlsx Excel file to your CodedUI Project:
    Right Click Project -> Add -> Existing Item… -> Add
  2. Check “data.xlsx” Properties in VS, make sure to set “Copy to Output Directory” Property to “Copy always” or “Copy if newer”
  3. If you get a connection error, it’s most likely due to the fact that you don’t have the 2007 Office System Driver installed for the OLEDB provider. (Thanks to and incyclesoftware), you can download it from the following Microsoft link:
    Link 2007 Office System Driver: Data Connectivity Components

This will install the Office 2007 drivers. Since the Access and Excel file formats haven’t changed since 2007, these drivers work just fine with Office 2010, 2016, etc.
Another comment for CSV file: if CSV file is not working, check the encoding. It should be ANSI or UTF-8 without BOM.

Read/write values from Excel data source file in CodedUI Test

Reading DataSource and then writing back with updated data will NOT work, because every iteration the test [TestMethod] is executed with the original Data Source. Updated data from last iteration will be overwritten in each iteration. You end up with only one row of updated data from the very last iteration.

Solution: (No real solution unless Microsoft makes changes, but rather a workaround)

Manually make a copy of the Data Source file before running the test, then while still utilizing Data Source,


Scenario SecondCol Result
89       23        Value
11       800       Value
888      111       Value
  1. load data from the copied file
  2. update the Data
  3. overwrite the copied file

The following is an example. data.xlsx is the Excel Data Source file, newdata.xlsx is the copy you make before running tests.

[DataSource("System.Data.Odbc", "Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\data.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=false", "Sheet1$", DataAccessMethod.Sequential)] // This is working Excel.xlsx connection!!! Excel (ODBC, Dsn)

public void ExcelReadWrite()
    //Open copied Excel file and create Excel object
    string projectName = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name;
    string dir = Environment.CurrentDirectory + @"\..\..\..\";
    string targetFilename = "newdata.xlsx";
    string targetFile = dir + projectName + @"\" + targetFilename;

    object missing = Type.Missing;
    object misValue = System.Reflection.Missing.Value;
    Excel.Application excel = new Excel.Application();
    Excel.Workbook wb = excel.Workbooks.Open(targetFile, true, false);
    Excel.Worksheet ws = wb.Sheets[1];
    Excel.Range wr = ws.UsedRange;
    int rowCount = wr.Rows.Count;
    int colCount = wr.Columns.Count;

    //make some example data update for current iteration
    int row = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow); //current iteration data row. zero base
    int generatedData = Convert.ToInt32(TestContext.DataRow[0]) + Convert.ToInt32(TestContext.DataRow[1]); //read from DataSource
    ws.Cells[row + 2, 3] = generatedData; //Worksheet, not zero base, and header row counts
    // ======================================================
    // Overwrite the copied file without warning
excel.DisplayAlerts = false;
    wb.SaveAs(targetFile, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    wb.Close(missing, missing, missing);

Don’t try to copy the Data Source file within [TestInitialize] as that will be executed before every iteration as well.

The key point is that you actually have to work with 2 separate files, regardless if they are identical at the beginning or not. One is for Data Source, which is read only and drives the iterations; the other is the one you read/write/update. For .csv file, yes it can be in append mode, but for Excel, you have to overwrite the entire file each iteration.