0

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".

Shane LeBlanc
  • 2,633
  • 13
  • 42
  • 74
  • Strange these two pipes, are you sure that the string is not |DataDirectory|? – Steve May 29 '13 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 May 29 '13 at 17:16

1 Answers1

3

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

Steve
  • 213,761
  • 22
  • 232
  • 286
  • Looks good, but where is ConfigurationManager located? This is a desktop application by the way. – Shane LeBlanc May 29 '13 at 17:30
  • Nevermind, added the ref to System.Configuration and it's now accessible. Thanks. – Shane LeBlanc May 29 '13 at 17:35
  • using System.Configuration (and add the reference to System.Configuration.dll) see http://msdn.microsoft.com/en-us/library/ms134260.aspx – Steve May 29 '13 at 17:35
  • 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. – Shane LeBlanc May 29 '13 at 19:05
  • 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 – Steve May 29 '13 at 19:19