|DataDirectory| distress

The issue first came up in a project last semester, and again it haunts us:
when you place your .mdf file in one project (the DAL) and execute the application from another project (the UI) evil things happen. Namely, you get an exception:

System.Data.SqlClient.SqlException was unhandled by user code
Message="An attempt to attach an auto-named database for file blah blah blah\PL\myDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

As I say, this is evil. The file myDB.mdf is right where I put it; when I go into Settings for my DAL project and open the nifty dialog box for my connection string, the "Test Connection" button works just fine.

But just you take a closer look at that error. When I compile my application, the system starts looking for my DB in the directory that contains my PL, not my DAL. What happened?

When I open the nifty dialog box, the path for my DB looks like this:

blah blah blah\DAL\myDB.mdf

The actual value of the setting in my DAL\Settings.settings file looks like this:

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

Ha ha! Foul play! I can spend the whole day setting the location of the DB in the nifty dialog box, but in the bitter end the Settings.settings file translates the path of the parent project to a variable called |DataDirectory|, and at compile time it replaces my DAL project's path with the startup (UI) project's path for the value of that variable.

What's the right workaround?

1. The wrongheaded way would be to elbow my way into the Settings.settings and set the path absolutely to the location of my DB. Wrongheaded, because if I move my project around (even on my own machine), I have to elbow back in and update the location.

2. The easy way out would be to use SQL Server instead of a local data file. Which is of course what we're doing on our project.

3. I thought to try to place the DB in the parent folder of the projects - ie, in the solution folder. But that induced Settings.settings to hard-code the location. Not much better than solution 1.

4. Finally I found what I was looking for! by jumping from here to here to (at last!)

https://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx

So I added the following line of code to a function that is executed before I try to access my data:

"AppDomain.CurrentDomain.SetData("DataDirectory", AppDomain.CurrentDomain.BaseDirectory.Replace("GUI", "DAL") );"

where GUI is the directory of my UI and DAL is the directory of my DAL and they are in the same parent (solution) directory.

But really DBs were meant to be on servers, and anyway 3 layers are 2 layers too many. So claims Microsoft.


Now considering how much trouble this has caused a good number of people since 2005, I think it's time for Microsoft to rethink its nifty features.