using a SQL database in multiple projects

Question

I have a system comprising 5 applications. Each app accesses a database via a DAL library. In the DAL i have an app.config with the following entry:

<connectionStrings>
<add name="DataAccessLayer.Properties.Settings.ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=c:\users\something\something\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Using the full path for the attachDbFilename works fine. But I'm not happy:

1. I have copied the app.config file into each app that uses the database. Best way of doing this - copying the DAL app.config as a Link in the other projects?

2. I dont want a full path, when it comes to deployment that aint going to work. Relative paths in the app.config do not appear to work. Ideally I would like to be able to pull the DAL from source control onto any computer and not have to worry about changing the connection string each time. This: http://blogs.msdn.com/b/smartclientdata/archive/2005/08/26/456886.aspx talks about |DataDirectory| for deployment purposes but this doesn't work for me in debug (unless I'm using it wrong, see 3)

3. This might be better as a separate question but it is related to 2. - Is there a "good" way of arranging multiple projects for debug? I have created a Bin dir and in each project settings I copy the dll/exe to this bin dir. I also have a copy of the database in here (I tried no path in the app.config but that didn't work either, nor did |DataDirectory|). Also incredibly annoying is that relative paths do not work in Debug\Working Directory setting either, so it looks like that is one place that would have to change each time code is checked out to a new machine?

Apologies for the war and peace and thanks in advance for any ideas.

Answer 1

Two answers - but not really full solutions:

1) I have copied the app.config file into each app that uses the database. Best way of doing this - copying the DAL app.config as a Link in the other projects?

You could externalize the connection strings into their own config, something like:

<connectionStrings configSource="connectionStrings.config" />

and then have those connection strings in that new file:

<connectionStrings>
   <add name="DataAccessLayer.Properties.Settings.ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=c:\users\something\something\MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

That way, you can have custom app.config's, but share the commonality.

2) I don't want a full path, when it comes to deployment that ain't going to work. Relative paths in the app.config do not appear to work.

Unfortunately, the only thing you can do here is to use the |DataDirectory| placeholder, which is a placeholder for the App_Data folder in an ASP.NET application.

   <add name="DataAccessLayer.Properties.Settings.ConnectionString" 
        connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" 
        providerName="System.Data.SqlClient" />

The only other solution would be to use a server and connect to a server - rather than having files to dynamically mount and attach.

Answer 2

Using the connection string AttachDbFilename feature from multiple processes referring the same MDF is very bad idea. Sharing an auto-atached database can get you in all sort of complicated security/ownership database startup problems. And specifying User Instance=True is like pouring gas over the flame, since each user instance is per user so if your applications are ever configured to run under different apppool credentials or one suddenly is changed to impersonate, all hell breaks loose.

Just attach for good the MDF as an normal database to your SQL instance and use it as such, with a normall connection string: Data Source=.\SQLEXPRESS;Initial Catalog=<dbname>; Integrated Security=True.