connectionstring to Access sql localDB in appdata folder

Question

i have a winform application with SQL localDB and i used advanced installer to deploy it, but once deployed in my target machine inside C:Programme files , i get an error when i tried to update my database that say : Database read-only and that is because the folder deployed in does not give permission to the user to write in it.

So, i installed my application in \user\appdata\roaming where i have permission to write and it worked !

that's not good, i want to keep my application folder in C:Progamme files and copy only mydatabase.mdr in \user\appdata\roaming.

but if i do that, i can no longer access my database with my current connectionstring, so my question is how can i change my connectionstring to access my database inside \user\appdata\roaming in any target machine?

this is my connectionstring :

    <connectionStrings>
    <add name="GTC_Frater_Razes.Properties.Settings.db_alarmeConnectionString"
        connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\db_alarme.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Answer 1

By default, your connection string cannot use |DataDirectory| which is a sort of variable which is expanded to a full path. As stated here:

A path that starts with the DataDirectory or the ~ operator cannot resolve to a physical path outside the branch of the DataDirectory and the ~ operator.

If the database will be at C:\Users\UserName\AppData\Roaming\ApplicationName\db_alarme.mdf, you can manually set your connection string as follows:

<connectionStrings>
  <add
    name="GTC_Frater_Razes.Properties.Settings.db_alarmeConnectionString"
    connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\UserName\AppData\Roaming\ApplicationName\db_alarme.mdf;Integrated Security=True"
    providerName="System.Data.SqlClient" />
</connectionStrings>

Since apparently you can't use environment variables -- %APPDATA% -- you'll have to know the deployment directory ahead of time.

Alternatively, you can set the value of DataDirectory using the AppDomain.SetData method before you actually open the connection:

AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\Users\UserName\AppData\Roaming\ApplicationName");

You can get the location of AppData\Roaming as shown here; and pass in the combined path to SetData.

var dataDirectory = Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), 
    "ApplicationName"
);
AppDomain.CurrentDomain.SetData("DataDirectory", dataDirectory);