.NET Musings

A little background.  I am working on some sample code for some new presentations that I am putting together, and I need a database.  So, I went with Microsoft SQL Server Express 2008 R2 (freely availably here).  Everything was working great, and I was cranking out code, “in the groove”. (See my previous post for setting up an ADO.NET connection string)

I then realized that I had created my solution folder in the wrong place on my drive (I’m a bit OCD about this), and moved everything to where I wanted it (including the SQL Express data and log files).  Open up my solution, rebuild (worked), F5 – Crash and Burn.

I kept getting this error:

Database 'E:\Projects\2010\Fakes_Stubs_Mocks_DI\DataFiles\FSMDI.mdf' already exists. Choose a different database name.
Cannot attach the file 'E:\Projects\2010\Presentations\Fakes_Stubs_Mocks_DI\DataFiles\FSMDI.mdf' as database 'FSMDI'.

I got completely hung up on that first sentence.  OF COURSE IT EXISTS! I PUT IT THERE!

Turns out the second line is the important one. In my connection string in the old location, I had specified an Initial Catalog parameter (“Initial Catalog=FSMDI;”). Since I just moved all of my code, I still had that parameter in my connection string.

Somewhere in the deep bowels of SQL Server (because I searched the registry and my hard drives) a key value pair was created that matched the catalog “FSMDI” to the location “E:\Projects\2010\Fakes_Stubs_Mocks_DI\DataFiles\FSMDI.mdf”.  This is due to the parameter I entered AND because I specified “User Instance=true”. And it’s not worth trying to find where this key/value pair exists (but if you do, please leave a comment on my blog for me).

The problem is resolved by removing the Initial Catalog parameter from the connection string. First of all, it’s not needed, since it’s a single database file. Secondly, it just mucks everything up.

So, to quote my last blog post, here is the format that you want to use

Data Source=[Express Instance];AttachDbFilename=[Path and File Name of mdf Database File];Integrated Security=true;User Instance=true;

In the Path, you can use the shortcut “|DataDirectory|” which maps to the Path.GetDirectory(Assembly.GetExecutingAssembly.Location). In human speak, this is the directory where the application is executing.

Happy Coding!