How can I add %LOCALAPPDATA% to a connection string setting?

Question

I have an n-tier application that uses a data access layer. In the data access layer project settings, there is a connection string. The string contains the text "Data Source=|%LOCALAPPDATA%|\Some Folder\File.ext" but when the application is run (and the file is there) I get an OleDbException "Not a valid File."

Am I writing the string wrong, or is this due to some other problem?

Also, if the user has Windows XP, will this directory point to anything? I'm making the installer, install an Access database to this particular folder, but I don't think XP has a "LocalAppData", but just "AppData".

Strange these two pipes, are you sure that the string is not |DataDirectory|? — Steve
– Steve, Commented May 29, 2013 at 17:12
Oh I'm sure. I tried installing the access database into the |DataDirectory| but when the application installs, it defaults to no modify permissions on the file and I receive a permissions error. I read that this is what the AppData directories are better used for so I now have the installer move the database to that directory and need to test it. — Shane LeBlanc
– Shane LeBlanc, Commented May 29, 2013 at 17:16

Steve · Accepted Answer · 2013-05-29 17:37:31Z

4

You can get and replace the predefined environment variable with this code

string str = Environment.ExpandEnvironmentVariables("%LOCALAPPDATA%");
string expandedConString = "Data Source=|%LOCALAPPDATA%|\Some Folder\File.ext".Replace("|%LOCALAPPDATA%|", str);

By the way, the %LOCALAPPDATA% environment variable points at the same value of

string str = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);

However I find this machinery is a bit clumsy. A better approach is to use the |DataDirectory| substitution string. You write your connection string with the standard

"Data Source=|DataDirectory|\Some Folder\File.ext"

then in your app, before any data access code you change the current value for |DataDirectory| reading a configuration settings and changing its value with

string myDBPath = ConfigurationManager.AppSettings["PathToDatabase"].ToString();
AppDomain.CurrentDomain.SetData("DataDirectory", myDBPath);

In this way your application will be more flexible and will adapt easily to different external constraints that your customers undoubtedly will pose.
(Of course your installer should choose or configure a directory where you have full read/write permission on your database file)

For completeness

Where is DataDirectory

edited May 29, 2013 at 17:37

answered May 29, 2013 at 17:18

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shane LeBlanc Over a year ago

Looks good, but where is ConfigurationManager located? This is a desktop application by the way.

Shane LeBlanc Over a year ago

Nevermind, added the ref to System.Configuration and it's now accessible. Thanks.

Steve Over a year ago

using System.Configuration (and add the reference to System.Configuration.dll) see msdn.microsoft.com/en-us/library/ms134260.aspx

Shane LeBlanc Over a year ago

What is the line "string myDBPath = ConfigurationManager.AppSettings["PathToDatabase"].ToString();" needed for? Also, when I add this line and it gets hit in code, the rest of my code gets skipped and the app is loaded.

Steve Over a year ago

Just an example on how to retrieve the path of the database from the external config file. Really the config file already has its own section to store connection strings, but from your code is not clear if you have a hard coded string inside the program or if you already use a config file

Collectives™ on Stack Overflow

How can I add %LOCALAPPDATA% to a connection string setting?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related