|DataDirectory| does not come from config settings;
ConfigurationManager.AppSettings["DataDirectory"]
This comes from config settings; a .config file you have to create and put into your project. This particular setting is the value of the element with key "DataDirectory" in the AppSettings element. This doesn't exist unless you put one in the .config file. Typically this is where you put configuration or startup data that is never changed. You should not put file paths here, as they can be different on the machine users install your database to.
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
This is the path to the current user's roaming application data folder defined by the operating system your app was installed on. You cannot change this, it is defined by the OS. You can be sure this folder is writable by the user, and will be available if the user roams, or logs on from another machine. This is typically where you want to put editable user data.
SqlConnection("Data Source=|DataDirectory|\\DatabaseFileName.sdf;...")
This is a connection string for an ADO.NET connection. ADO.NET treats vertical bars specially, it looks for an AppDomain data matching the key name between the vertical bars. You can get the same data with:
AppDomain.CurrentDomain.GetData("DataDirectory")
Submit an article or tip