Working With SQL Server Compact Edition 2005
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:
|
1 2 3 4 5 |
<span style="font-family: Courier New;"><span style="color: #0000ff;"><</span><span style="color: #808000;">connectionStrings</span><span style="color: #0000ff;">></span> <span style="color: #000000;"> </span><span style="color: #0000ff;"><</span><span style="color: #808000;">add </span><span style="color: #ff0000;">name</span><span style="color: #0000ff;">=</span><span style="color: #ff00ff;">"ClientDatabase" </span> <span style="color: #800080;">connectionString</span><span style="color: #0000ff;">=</span><span style="color: #ff00ff;">"Data Source=<strong>|DataDirectory|</strong>\data-file.sdf"</span> <span style="color: #800080;">providerName</span><span style="color: #0000ff;">=</span><span style="color: #ff00ff;">"Microsoft.SqlServerCe.Client" </span><span style="color: #0000ff;">/></span> <span style="color: #0000ff;"></</span><span style="color: #808000;">connectionStrings</span><span style="color: #0000ff;">></span></span> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<span style="font-family: Courier New;"><span style="color: #008000;">/// <summary></span> <span style="color: #008000;">/// Initializes the logging subsystem for the <see cref="Connect"/> class.</span> <span style="color: #008000;">/// </summary></span> <span style="color: #0000ff;">static </span><span style="color: #000000;">Connect() {</span> <span style="color: #0000ff;">string </span><span style="color: #000000;">path = </span><span style="color: #808000;">Assembly</span><span style="color: #000000;">.GetExecutingAssembly().Location;</span> <span style="color: #000000;">path = path.Substring(</span><span style="color: #800080;">0</span><span style="color: #000000;">, path.LastIndexOf(</span><span style="color: #ff00ff;">'\\'</span><span style="color: #000000;">));</span></span> <span style="font-family: Courier New;"> <span style="color: #008000;">// Set the DataDirectory for the SQL Server CE connection string.</span> <span style="color: #808000;">AppDomain </span><span style="color: #000000;">domain = </span><span style="color: #808000;">AppDomain</span><span style="color: #000000;">.CurrentDomain;</span> <span style="color: #000000;">domain.SetData(</span><span style="color: #ff00ff;">"DataDirectory"</span><span style="color: #000000;">, path);</span> <span style="color: #000000;">}</span></span> |
