How is location of |Data Directory| in connection strings resolved?

Question

If I create an asp.net project and use entity framework to create a database, something like this is automatically added to the connection strings in the web.config:

<add name="DefaultConnection" connectionString="data source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\WebAppName.mdf;initial catalog=WebAppName;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />

Note that instead of a fully qualified file path, it uses |Data Directory| which, in this case, points to the 'App_Data` folder. Here's how some of the documentation explains it:

The presence of User Instance=true and AttachDBFilename=|DataDirectory| cause SqlConnectionHelper to conclude that the connection string targets SQL Server Express and triggers the database's creation. (The presence of data source=.\SQLEXPRESS in the connection string does not factor into the decision, because SqlConnectionHelper supports non-default as well as default instances of SQL Server Express.) The |DataDirectory| portion of the connection string specifies that the MDF file is located inthe App_Data directory. SqlConnectionHelper derives the database name from the MDF file name. It also creates an App_Data folder to hold the MDF if the folder doesn't already exist.

Except, if I use Entity Framework in, say, a console application, none of that is true--you'll just get an exception saying there's no file at the specified path, and it will both ignore any App_Data folder you created and fail to create one if there is none. If you remove the AttachDBFilename section altogether, it will work, but will create the database in the local output bin where the .exe file is located. Google tells me you can manually set |Data Directory| using AppDomain.SetData but apparently that's still not true for a console application (get compile error saying "An object reference is required").

So my question is, how exactly does the location of |Data Directory| get resolved? As far as I know, the fact that it differs between console apps and Asp.net apps means the resolution can't be happening solely in SQL Server Express as both are using the same installation. So is it happening in the asp.net server? Or is there a hidden settings file that gets created in asp.net projects?

You CAN use `SetData` from console apps, I've done it in multiple projects. — Bradley Uffner, Dec 17 '15 at 14:25
Using Data Source in connection string can cause credential issues. It is better to use Database name. The SQL server already knows the file location of the database by name. See www.connectionstrings.com for all versions of connection string. — jdweng, Dec 17 '15 at 14:33

score 2 · Accepted Answer · answered Dec 17 '15 at 14:41

Here is the code which specifies where is |DataDirectory|

GetDataDirectory

[PermissionSet(SecurityAction.Assert, Unrestricted = true)]
internal static string GetDataDirectory() {
    if (HostingEnvironment.IsHosted)
        return Path.Combine(HttpRuntime.AppDomainAppPath, HttpRuntime.DataDirectoryName);

    string dataDir = AppDomain.CurrentDomain.GetData(s_strDataDir) as string;
    if (string.IsNullOrEmpty(dataDir)) {
        string appPath = null;

#if !FEATURE_PAL // FEATURE_PAL does not support ProcessModule
        Process p = Process.GetCurrentProcess();
        ProcessModule pm = (p != null ? p.MainModule : null);
        string exeName = (pm != null ? pm.FileName : null);

        if (!string.IsNullOrEmpty(exeName))
            appPath = Path.GetDirectoryName(exeName);
#endif // !FEATURE_PAL

        if (string.IsNullOrEmpty(appPath))
            appPath = Environment.CurrentDirectory;

        dataDir = Path.Combine(appPath, HttpRuntime.DataDirectoryName);
        AppDomain.CurrentDomain.SetData(s_strDataDir, dataDir, new FileIOPermission(FileIOPermissionAccess.PathDiscovery, dataDir));
    }

    return dataDir;
}

Old now, but I'd like to see the formal documentation describing this. I know in a sense the code is even more authoritative, but the docs are really the correct reference. — Joel Coehoorn, Apr 20 '17 at 01:08
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx @JoelCoehoorn — Hamid Pourjam, Apr 22 '17 at 05:16

How is location of |Data Directory| in connection strings resolved?

1 Answers1

Linked