How to use Application Data in an (App.config) connectionString

Question

I've got an SQL Server CE database in a project that I wan't to store somewhere in the %AppData% directory. However I can't find a way to make a reference to the Application Data path in the connection string (in the App.Config)

<?xml version="1.0"?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="EntityConnectionString" connectionString="metadata=res://*/EntityModel.csdl|res://*/EntityModel.ssdl|res://*/EntityModel.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=|ApplicationData|\Entities.sdf&quot;" providerName="System.Data.EntityClient"/>
  </connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
</configuration>

So far I learned that: %APPDATA% is not supported and using the settings class (like suggested) won't work either (the settings class isn't constructed at the time the exception is already thrown).

Is it possible to use the application data folder (or another special folder) in the connectionString property (in the App.Config)?

Note: it seems like I'm searching for an solution to modify the connection string (in code) as early as possible rather than an native App.Config solution.

Answer 1

Use your custom build environment variable support:

Let you have:

<connectionStrings>
    <add name="My" connectionString="..;Data Source=|%AppData%|\Entities.sdf;.." />
</connectionStrings>

The you can use:

using System.Configuration; // requires reference to System.Configuration.dll

ConfigurationManager.ConnectionStrings["EntityConnectionString"].ConnectionString.Replace("%AppData%", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);

Next way you can support several environment variables:

var vars = new Dictionary<string, string>
{
    { "%AppData%", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
    { "%Temp%", Environment.GetFolderPath(SpecialFolder.Temp) },
    // etc..
    { "%YourNonStandardVar", "YourNonStandartPath" }
};

var result = ConfigurationManager.ConnectionStrings["YourString"].ConnectionString
foreach (var v in vars)
    result = result.Replace(v.Key, v.Value);