One interesting issue that I just solved involved how to specify the location of the database file for a SQL Server Compact Edition 2005 connection string in a .Net add-in for Microsoft Office.

You see, when the add-in starts, it sets the context directory as the user's documents directory, which of course, makes it impossible to enter a configuration string for the data source of the connection string.

It works fine if the directory is hard coded - which is what I did for testing purposes initially, but of course, when I switched over to XP64, this broke as on XP64, the program is installed to "Program Files (x86)".

The solution lies buried in Microsoft's SQL CE documentation: there's a note that you should use a special token with the connection string like so:

<connectionStrings>
	<add name="ClientDatabase" 
		connectionString="Data Source=|DataDirectory|\data-file.sdf"
		providerName="Microsoft.SqlServerCe.Client" />
</connectionStrings>

The token needs to be included exactly as entered "|DataDirectory|". So how is this token replaced? In the static constructor of my Connect class that was autogenerated by Visual Studio, I added the following code:

/// <summary>
/// Initializes the logging subsystem for the <see cref="Connect"/> class.
/// </summary>
static Connect() {
	string path = Assembly.GetExecutingAssembly().Location;
	path = path.Substring(0, path.LastIndexOf('\\'));

	// Set the DataDirectory for the SQL Server CE connection string.
	AppDomain domain = AppDomain.CurrentDomain;
	domain.SetData("DataDirectory", path);
}