The mission was to embed a SQL Express database into a desktop application and have it use User Instances.  In addition, the database itself will be stored in the project as a resource and copied to the application directory the first time it is used by code that's included in the project.  This is done so that subsequent updates to the database can be deployed as a SQL script and can be run by the application against its copy of the database preventing the application from loosing user data by overwriting previous versions of the database.  This technique is discussed in this webcast that I've linked to before, but today we are going to talk about the next step. 

UNIT TESTING

In my project I created a Visual Studio unit test to verify the functionality of copying the database to a file from an embedded application resource, connect to it with Linq to SQL and run some queries against the database to confirm it worked.  Everytime the query ran I was plagued with a SQL Exception error....

System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file c:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\AppData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share..

 The real key to fixing this error was the database path.  Notice in the error message it's pointing to c:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\AppData.mdf however, in the context of running the unit test, the path should be something like... C:\Development\EOD\TestResults\mcstar_HP_LAPTOP 2008-10-15 09_21_35\Out\AppData.mdf

so, why is the unit test looking in the wrong location for the mdf file?  The secret to understanding that is the connection string.  Here's my connection string :

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AppData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

That keyword DataDirectory informs SqlExpress to use the current application directory.  Since the Visual Studio IDE is the current executing process whenever you run unit tests, DataDirectory is referring to Visual Studio's startup path!  That's not going to work.  In my case, the fix was to force my data context to open with the correct path by setting the connection string in the unit test to include the correct path to the mdf file created by the application.

Is it possible that thousands of scientist could actually be wrong?  Perhaps the answer is simpler than you think.  This video puts a little perspective around the debate.