VS Team Test: .Net Unit Testing with Excel as Data Source: Adapter Failed

Question

I am trying to do Unit Testing with Excel as data source. I am getting the following exception. How do we correct it?

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"

---

  [TestMethod]
  [Owner("Lijo ")]
  [TestProperty("TestCategory", "Developer"), 
      DataSource("Microsoft.ACE.OLEDB.12.0", 
     "Data Source=C:/Sheets/DataSheet.xlsx;Extended Properties=Excel 12.0;",
     "[Sheet1$]", 
     DataAccessMethod.Sequential)]
  public void ChangePasswordTest()
  {

     int a = Convert.ToInt32(TestContext.DataRow[0]); //(int)Column.UserId
     int b = Convert.ToInt32(TestContext.DataRow[1]);
     int expectedResult = Convert.ToInt32(TestContext.DataRow[2]);

     MyClass myObj = new MyClass(1, "P@ssw0rd");
     int actualResult = myObj.GetAdditionResult(a, b);
     Assert.AreEqual<int>(expectedResult, actualResult, "The addition result is incorrect.");

  }

---

Readings:

1. Unit Testing Error - The unit test adapter failed to connect to the data source or to read the data

2. Data driven unit tests problem

3. How to create Startup and Cleanup script for Visual Studio Test Project?

4. How Does MSTEST/Visual Studio 2008 Team Test Decide Test Method Execution Order?

5. Visual Studio 2010 Ultimate - Data Generation Plan Setting Incorrect Data Type for Column

6. How should I unit-test a simple CRUD-class?

Answer 1

I faded a same task today. And after some headaches I was able to solve without app.config:

[TestMethod]
[DataSource("System.Data.OleDB",
  @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Sheets\DataSheet.xlsx; Extended Properties='Excel 12.0;HDR=yes';",
  "Sheet1$",
  DataAccessMethod.Sequential
)]       
public void ChangePasswordTest()
{
 //Arrange

 //Act

 //Assert

}

If you use excel files as resources in your testproject, you have to set the Copy to Output Directory property of the file to Copy always or Copy if newer. And add the DeploymentItem attribute to your test:

[TestMethod]
[DataSource("System.Data.OleDB",
  @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=.\DataSheet.xlsx; Extended Properties='Excel 12.0;HDR=yes';",
  "Sheet1$",
  DataAccessMethod.Sequential
)]       
[DeploymentItem(".\DataSheet.xlsx")]
public void ChangePasswordTest()
{
 //Arrange

 //Act

 //Assert

}

Answer 2

Solved it myself in a different way. Other answers are welcome.

Refer: Walkthrough: Using a Configuration File to Define a Data Source http://msdn.microsoft.com/en-us/library/ms243192.aspx

  [TestMethod]
  [DeploymentItem("C:/Sheets/DataSheet.xlsx")]
  [DataSource("MyExcelDataSource")]
  public void ChangePasswordTest()
  {

     int a = Convert.ToInt32(TestContext.DataRow[0]); //(int)Column.UserId
     int b = Convert.ToInt32(TestContext.DataRow[1]);
     int expectedResult = Convert.ToInt32(TestContext.DataRow[2]);

     MyClass myObj = new MyClass(1, "P@ssw0rd");
     int actualResult = myObj.GetAdditionResult(a, b);
     Assert.AreEqual<int>(expectedResult, actualResult, "The addition result is incorrect.");

  }

App.Config

<configuration>


<configSections>

<section name="microsoft.visualstudio.testtools" 
 type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>

 </configSections>



 <connectionStrings>

<add name="MyExcelConn" 
     connectionString="Dsn=Excel Files;dbq=C:/Sheets/DataSheet.xlsx;defaultdir=.; driverid=790;maxbuffersize=2048;pagetimeout=5" providerName="System.Data.Odbc" />

 </connectionStrings>


 <microsoft.visualstudio.testtools>


  <dataSources>

      <add name="MyExcelDataSource" 
      connectionString="MyExcelConn" 
      dataTableName="Sheet1$" 
      dataAccessMethod="Sequential"/>

  </dataSources>


</microsoft.visualstudio.testtools>

</configuration>

For VS 2010, TestTools version used is Version=10.0.0.0

Answer 3

Although it's not 100% related to the question and perhaps somewhat trivial, I'd like to throw in my two cents on this general topic (this was the most relevant question I could find to post in).

In the project I'm currently working on I came across the need to do some simple data-driven unit tests (e.g. with perhaps 20 rows or so for a given test). My wish-list for a data-driven "framework" was:

1. Nice and easy integration in the Visual Studio Test Explorer
2. Ability to keep using my existing keyboard shortcut keys to run/debug the test method in which the cursor lies
3. No external files or databases to have to manage (i.e. no "DataSources")
4. "Native" support in C# and Visual Studio, i.e. no additional packages necessary

Despite wish #4, I looked into external libraries like xUnit and NUnit. They seemed to solve wish #3, but didn't do a great job at wishes #1 and #2.

Frustrated with the lack of an easy solution, I decided to implement an extremely basic data-driven helper myself:

    public static void DataDrivenTest(Action<List<object>> testAction, List<List<object>> dataRows)
    {
        foreach (var dataRow in dataRows)
            testAction(dataRow);
    }

I use it like this:

    [TestMethod]
    public void Unit_Can_Add_Two_Numbers()
    {
        UnitTestUtilities.DataDrivenTest(
            dataRow =>
            {
                // Tests a+b=c
                var a = (int)dataRow[0];
                var b = (int)dataRow[1];
                var c = (int)dataRow[2];
                Assert.AreEqual(a + b, c);
            },
            new List<List<object>>
                {
                    // Rows of arguments a,b,c respectively
                    new List<object>{1,2,3},
                    new List<object>{4,5,9}
                });
    }

Although it fulfills all of my wishes above, it has disadvantages:

1. Casting is required inside the test action definition (could probably be fixed with some generic argument magic)
2. Updating the order of the arguments in the data rows means that the accessors in the action method also have to be updated
3. Clearly not appropriate for large data-driven tests (e.g. this helper would be messy for more than, say, 20 test rows)
4. Clearly lacks "sophistication", i.e. I'm sure that libraries like xUnit have some funky features that this doesn't have
5. It runs all rows as one unit test (i.e. no separate unit test and reporting for each data row)

Regardless, it solved my basic problem in a simple way. I hope that helps somebody looking for a simple solution like I was. After all, if you find that you need a tonne of test rows for testing a method, it might be worth considering a refactor to break down the function under test into smaller components (and then use in conjunction with mocks/fakes, dependency injection etc).