c# - 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:SheetsDataSheet.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

}