Friday, February 18, 2011

Using Excel 2003 File As Data Source Of Visual Studio .Net Data Driven Unit or Functional Test

First of all this will be the attribute tag for setting up data access of a data driven Unit or Function test in Visual Studio, I am using Visual Studio 2008 but I think same will work in Visual Studio 2010

[DataSource("System.Data.OleDb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\FunctionalTestsDataFiles\\Smoke_Cred.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", "ldapdest$", DataAccessMethod.Sequential), TestMethod]

Put the name of your .xls file in place of "Smoke_Cred.xls" and put the name of your excel sheet in place of "ldapdest$", don't forget to put the $ sign after the name of your Excel sheet.

Be very careful with the "System.Data.OleDb" because the people at Microsoft have made a case sensitive comparison in their code which wasted so many of my hours, and gave me too much tension.
If you write "System.Data.OleDB" instead, it will fail in functional/unit test but it will work fine in other connection string testing utilities.


Second thing, the syntax of spread sheets inside excel files is different from syntax used for Access databases. You need to put a $ character after the name of the sheet you are trying to access.
I found an article on MSDN about running data driven test cases with Ms Access, but there was no mention of these little pieces of crap which I am sure must have been killing time of many poor developers.
Instead of solving problems, you have to run after crappy case sensitive and idiotic connection strings, what an irony!!!

If you use "System.Data.OleDB" mistakenly, following error will be displayed

Error details: Unable to find the requested .Net Framework Data Provider.  It may not be installed.

And if you somehow luckily fix this, and don't know that you need to put a $ sign after the name of that excel sheet, you'll get following error

Error details: The Microsoft Jet database engine could not find the object 'ldapdest'.  Make sure the object exists and that you spell its name and the path name correctly.









One more twist, the TestContext used by some s of b in MSDN article is not found anywhere by default, and you need to define it before using it. I wonder what Microsoft's trying to do with us developers.

 private TestContext testContextInstance; 
public TestContext TestContext
{
get { return testContextInstance; }
set { testContextInstance = value; }
}

The link of the piece of crap MSDN article is following:
I know I'm using rash and bad language but what else do you expect from someone on 4:30 AM? When you come to know that only problem with your code was that you used "B" instead of "b" and you must be intelligent enough to put a $ in the end of table name of excel sheets here. I mean why in the world Microsoft idiots cant just fix these tiny issues??? I'm too much pissed right now... literally pissed...


RECOMMENDATION : Don't use Excel files as a data source for your dynamic tests, instead use .csv provider. It works well on both 32 and 64 bit machines. Excel will only increase the pain.