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>

Zev Spitz · Accepted Answer · 2018-05-08T16:23:04.503

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);

edited May 08 '18 at 16:23

answered May 08 '18 at 11:46

Zev Spitz

13,950
6
64
136

the second one doesn't work and the first one works but i can't use it because i have to put my "UserName", i need the username to be variable so it can works on any target machine – lawir May 08 '18 at 12:09
i don't have to know the deployment directory because in my installer i am going to copy my database in the AppData folder of my target computer, i just want to get a standard connectionstring that works with every username, how can i do that ? – lawir May 08 '18 at 12:34
@lawir I don't think this is possible using `DataDirectory`, which cannot be used to refer to something outside the current folder of the application. AFAIK, `DataDirectory` and `~` are the only variables supported in connection strings; there are no variables for various system and user-profile paths. Did you try using `SetData` in the code before opening any connections? I've updated my answer. – Zev Spitz May 08 '18 at 12:38
so the "dataDirectory" get the path and then pass it to "DataDirectory" with AppDomain.SetData ? so where should i put this code ? – lawir May 08 '18 at 13:12
@lawir Before you open any connection to the database. – Zev Spitz May 08 '18 at 13:13
i have an error with appdomain.setdata that says : "an object reference is required for a non static field, method or property? do i miss an assembly? – lawir May 08 '18 at 13:22
@lawir Try `AppDomain.CurrentDomain.SetData(` – Zev Spitz May 08 '18 at 13:42
It is working, with `appDomain.CurrentDomain.SetData` !!!!!! Thank you so much :) – lawir May 08 '18 at 15:51

connectionstring to Access sql localDB in appdata folder

1 Answers1

Linked