I found a solution to the aforementioned .sdf problem but it’s one that I would think is ideal or "clean". Firstly, assuming that you are using the VS-generated data adapters (you might have written your own instead) – the connection string when created is an absolute path to your database file e.g.
C:\Users\Isaac\My Documents\Visual Studio 2008\Projects\MySolution\MyDataTierProject\MyDatabase.sdf
Obviously this is a completely useless path for when it comes to deploying your application. To get around this, there seem to be a couple of choices. The first one that I thought of was to make an install-time task which modifies this page to wherever your .sdf gets deployed – most likely this is the same directory that your application lives in.
However, this solution seemed quite a lot of work. I eventually came across a couple of somewhat useful articles on MSDN, this was the most useful one: Working with local databases (Ironically enough written for VS2005, not 2008).
It talks about the DataDirectory "macro" – it’s effectively a property that gets dynamically resolved by .NET at run time, depending on how the application has been deployed (either Windows Installer or ClickOnce). For Windows Installer packages, it’ll just look in the current directory – which was exactly what I wanted.
However, this posed a problem for development time – I wanted to use, at development time, my development .sdf file which I have been working on – not the one that gets copied to the \bin\release or \bin\debug directories. The solution was to edit the settings.cs file for the project which contains the connection string to override it. This wasn’t as simple as it sounds as the ConnectionString is an Application-level property, and therefore read-only in C#. However, you can change the path that DataDirectory points to by using the AppDomain classes. Here’s what I did:
[Code Snippet plugin for Windows Live Writer available from here.]
This checks to see if we’re in debug mode, and if we are, updates the DataDirectory to point to the correct location.
It still feels "wrong" though. I’d prefer to have some way to amend the connection string at install time to DataDirectory instead of having to write some code to check if we’re in debug mode. Any ideas more than welcome.