%APPDATA% in connection string is not substituted for the actual folder?

Question

When using WPF and entity-framework I have an APP.CONFIG that looks like the following:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
     <add name="DatabaseEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=%APPDATA%\Folder\Database.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

When using this code it always throws the following error:

System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlServerCe.SqlCeException: The path is not valid. Check the directory for the database. [ Path = %APPDATA%\Folder\Database.sdf ]

When I run the path "%APPDATA%\Folder\Database.sdf" from the command prompt it works fine, and if I remove "%APPDATA% and hardcode the path it works fine - so it looks simply like the %APPDATA% is just not being substituted for the actual folder...

Thanks,

Answer 1

As you already reallized, %APPDATA% or any other environtment variables are not replaced with their respective value in connection strings. Environment varialbes are something related to the operating system shell. They work in command prompt because the command prompt explicitly parses the values entered and substitutes environment variables. That's not something that .NET Framwork usually performs.

To achive this, you have to manually provide the value for %APPDATA% (using Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) or Environment.GetEnvironmentVariable("APPDATA")). There are two options:

1. Change your connection string and use |DataDirectory|:

   <connectionStrings>
     <add name="DatabaseEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=|DataDirectory|\Database.sdf&quot;" providerName="System.Data.EntityClient" />
   </connectionStrings>
   

   (Notice the use of |DataDirectory| in the path to the database file.)

   Then provide the value for |DataDirectory| in your application's Main method:

   AppDomain.CurrentDomain.SetData("DataDirectory",
       Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
   

   Refer to this MSDN page for more information.

2. Manually provide the connection string for your ObjectContext class. This way you can parse and change the connection string:

   public static string GetConnectionString()
   {
       var conStr = System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseEntities"].ConnectionString;
       return conStr.Replace("%APPDATA%",
           Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
   }
   

   And later:

   var db = new DatabaseEntities(GetConnectionString());
   

   Or subclass you ObjectContext class and always use the new connection string:

   public class MyDatabaseEntities : DatabaseEntities
   {
       public MyDatabaseEntities()
           : base(GetConnectionString())
       {
       }
   
       public static string GetConnectionString()
       {
           var conStr = System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseEntities"].ConnectionString;
           return conStr.Replace("%APPDATA%",
               Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
       }
   }
   

   and use the new class anywhere.

Answer 2

I have another option. We don't need to replace anything. I am using below connection string without any replace and it's working fine.

<connectionStrings>
    <add name="ProjectManagementDBEntities" connectionString="metadata=res://*/Models.ProjectManagementModels.csdl|res://*/Models.ProjectManagementModels.ssdl|res://*/Models.ProjectManagementModels.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\ProjectManagementDB.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"/>
  </connectionStrings>

Main change is data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\ProjectManagementDB.mdf;integrated security=True;

I hope this will save someone.

Answer 3

You have to replace the %APPDATA% in the code with the relative path -

var connectionString = ConfigurationManager.ConnectionStrings["DatabaseEntities"]
                                                           .ConnectionString;    
connectionString.Replace("%APPDATA%", 
  Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);