Proper way to specify database connection string in a DataSet.xsd

I have a Windows CE Form (NETCF 3.5) application that I've created via Visual Studio 2008 (c#) - (However, the core of my question probably applies to normal Windows application, too).

The application implements a SQLite database that I've placed within the project - i.e. The database location is in the sub-directory within my project. So the connection string to the database looks something like:

@"Data Source = C:\src\myApp\data\mydb.s3db"

The connection to the database is fine and works.

After I created the database, I added a Data Set via VS2008's "Data Source Configuration Wizard" (in VS2008, go to Data > Add New Data Source...). The wizard allows us to create a connection to an existing database and, then, choose tables from within that database that will be used in the Data Set that the wizard is about to create.

So the wizard does a fine job of creating the data set which allows us to program against it. The problem that I have is when I deploy the application to my WinCE device.

The problem that I encounter is when the application attempts to access the database when it needs to fill the Data Set with data.

The root of the problem is, the connection string to th开发者_开发百科e database that is defined in the Data Set is per the dev machine - not the local path per the WinCE device.

For example: The connection string in the Data Set is set to

@"Data Source = C:\src\myApp\data\mydb.s3db"

instead of the full path of the target device similar to

@"Data Source = \Program Files\myApp\data\mydb.s3db"

I was able to fix the problem by manually editing the Data Set's .Designer.cs file (at the InitConnection() method). However, it would be nice if VS2008 can manage the connection string for me somehow - i.e. when in dev mode, the connection string points to the database on my dev machine. When in deploy mode, the connection string points to the database on the device.

Is there a way to specify the connection string to point to a relative path rather than the full path, maybe? Or?

Use "|DataDirectory|" in your connection string.

Quote from http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/dc31ea59-5718-49b6-9f1f-7039da425296/ ...

|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.