64

In AppConfig it is possible to use |DataDirectory| but I can't find any doc ?

gideon
  • 19,329
  • 11
  • 72
  • 113
programmernovice
  • 3,841
  • 11
  • 43
  • 63
  • [This post](http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/dc31ea59-5718-49b6-9f1f-7039da425296) seems to explain it adequately. – Ken Browning Sep 11 '09 at 06:54

6 Answers6

80

|DataDirectory| is a substitution string so you can configure the location of your database file separately.

So instead of:

SqlConnection c = new SqlConnection (
   @"Data Source=.\SQLDB; AttachDbFilename=C:\MyDB\Database.mdf;Initial Catalog=Master");

you do the following:

// Set |DataDirectory| value
AppDomain.CurrentDomain.SetData("DataDirectory", "C:\myDB");

// SQL Connection String with |DataDirectory| substitution string
SqlConnection c = new SqlConnection (
   @"Data Source=.\SQLDB; AttachDbFilename=|DataDirectory|\Database.mdf;Initial Catalog=Master");
Martin Liversage
  • 104,481
  • 22
  • 209
  • 256
Alex
  • 75,813
  • 86
  • 255
  • 348
  • 2
    Here is some documentation (search DataDirectory): http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx – ironic Apr 02 '12 at 17:09
  • this seems to work also with sqlite: http://sqlite.phxsoftware.com/forums/t/1824.aspx – juFo Jan 21 '13 at 09:28
  • 1
    A small note regarding allegedly undocumented connection string "convention" would be that "AttachDBFilename=|DataDirectory|" should not have a final slash at the end (as opposed to the example above). Moreover, the name of the MDF file (e.g. above) should match the value written within "Initial Catalog". This may well only be needed when working with SQL Express. – Dr1Ku Oct 10 '13 at 09:07
16

In the MSDN social forums this answer can be found

|DataDirectory| (enclosed in pipe symbols) is a substitution string that indicates the path to the database. It eliminates the need to hard-code the full path which leads to several problems as the full path to the database could be serialized in different places. DataDirectory also makes it easy to share a project and also to deploy an application.

For example, instead of having the following connection string:

"Data Source= c:\program files\MyApp\Mydb.sdf"

Using DataDirectory, you can have the following connection string:

“Data Source = |DataDirectory|\Mydb.sdf”

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules will be applied to access the database folder:

  • For applications that are put in a folder on the user's computer, the database folder uses the application folder.
  • For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.
Martin Liversage
  • 104,481
  • 22
  • 209
  • 256
MRG
  • 3,219
  • 1
  • 26
  • 35
  • 1
    Where would you call AppDomain.SetData in an MVC project? – alex Aug 05 '15 at 14:18
  • Is it possible to use whole path with database name like `“Data Source = |DataDirectory|”`? So `DataDirectory` must be defined as `AppDomain.CurrentDomain.SetData("DataDirectory", "c:\program files\MyApp\Mydb.sdf");`. – Quince Nov 30 '19 at 02:31
8

Incorrect guys! The |DataDirectory| refers to the mssql\data directory your instance is configured for.

So for example I am using Visual Studio 2012 inconjunction with SQL Express. |DataDirectory| places all MDF files under C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA where my sql express was installed not my solutions app_data folder.

Also the file is names MVCMovie.Models.MovieDBContext not Movies.mdf as specified in my web.config.

I'm thinking it needs to be configured somewhere in visual studio for it to be placed appropriately under app_data.

Paul Ford
  • 89
  • 1
  • 1
4

http://msdn.microsoft.com/en-us/library/aa478948.aspx

The |DataDirectory| portion of the connection string specifies that the MDF file is located in the App_Data directory.

saluce
  • 13,035
  • 3
  • 50
  • 67
Joe.P
  • 319
  • 4
  • 10
4

There is an internal class called SqlConnectionHelper which parses this and creates the MDF if needed.

Here's the only MS doc I can find about that class and the |DataDirectory| macro: http://msdn.microsoft.com/en-us/library/aa478948.aspx.

codekaizen
  • 26,990
  • 7
  • 84
  • 140
3

This might be relevant if you're using code first migrations.

With VisualStudio 2013 (at least) when running an Update-Database command the data directory is relative from the "Startup Project" currently configured in visual studio.

Even if you run the Update-Database on another project (as selected on the Package Manager Console), it will create your database on the App_Data of the currently selected Startup Project.

tggm
  • 973
  • 2
  • 15
  • 41