Deploying SQL Compact databases – Part II

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:

void Settings_SettingsLoaded(object sender, System.Configuration.SettingsLoadedEventArgs e)

// Only do the following code to set the path of the database if in debug mode #if DEBUG
System.IO.DirectoryInfo ProjectDir = new System.IO.DirectoryInfo(System.IO.Directory.GetCurrentDirectory());
ProjectDir = new System.IO.DirectoryInfo(ProjectDir.Parent.Parent.Parent.FullName + "\\MyDataProject");
System.AppDomain.CurrentDomain.SetData("DataDirectory", ProjectDir.FullName);
#endif }

[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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s