I've been experimenting with converting a small Oracle database to SQL Server Compact Edition 3.5. While I'm nonplussed with the available tools, in particular the lack of support for SQLCE in DTSWizard, the database is highly function for embedded purposes.
One thing that has been driving me nuts is a piped variable that VS 2008 places in the Settings variable for the path: DataDirectory. Your predefined connection string contains a term for Data Source that looks something like |DataDirectory|\EmbeddedAppDB.sdf. The .Net runtime expands the term |DataDirectory| as follows:
- For applications placed in a directory on the user machine, this will be the app's (.exe) folder. (This means you will have to place your sdf file in the Release or Debug folders.)
- For apps running under ClickOnce, this will be a special data folder created by ClickOnce.
- For Web apps, this will be the App_Data folder.
I've found that you can place code similar to the following in your form load and class constructor to point the DataDirectory to the desired location:
// Declare a string and assign a database path.
string s = @"C:\MyDataDirectory";
// Assign the property to the current domain for the thread
AppDomain.CurrentDomain.SetData("DataDirectory", s);
// Now you can run Debug or Release without copying the DB.
this.contactsTableAdapter.Fill(this.embeddedAppDB.Contacts);
Using this code, you can place your database in a known location and use it without copying to each executable directory used by the IDE.
Note:
Generally, the following call to GetData returns null unless you set the value of DataDirectory:
// Assign the property value to a string. Usually returns null
// unless you explicity set the value of Data Directory
string s = (string)AppDomain.CurrentDomain.GetData("DataDirectory");