Overview

In Visual Studio 2005 we added a number of features to help developers build and deploy applications that need a local data store. Here's a quick peek at how it works.

In order to work with a local database file, you can simply add the file to your project (e.g. using the Project/Add Existing Item... menu). We currently support adding SQL Server data files (.mdf), Jet (Access) data files (.mdb) and SQL Mobile data files (.sdf). Note that in order to be able to use .mdf files, you need to have installed SQL Server Express. SQL Express is available on the VS CD or at http://go.microsoft.com/fwlink/?LinkId=49251. With SQL Server Express installed, you will also be able to create new databases through 'Project/Add New Item…/Database'.

Once the database file is in the project, VS will do a few things:

      1. It will automatically add a connection in the Database Explorer so you can edit the database schema or the data.

      2. It will make sure that the connection strings are serialized using a relative path (more on this below).

      3. The first time the file is added, VS will also launch the Data Source wizard to create a new typed dataset.

Full path vs relative path

One of the reasons why it was hard to work with database files before is that the full path to the database was serialized in different places. This made it harder to share a project and also to deploy the application. In this version, the .NET runtime added support for what we call the DataDirectory macro. This allows Visual Studio to put a special variable in the connection string that will be expanded at run-time. So instead of having a connection string like this:

      "Data Source=.\SQLExpress;AttachDbFileName=c:\program files\app\data.mdf"

You can have a connection string like this:

      "Data Source=.\SQLExpress;AttachDbFileName=|DataDirectory|\data.mdf"

This connection string syntax is supported by the SqlClient and OleDb managed providers.

By default, the |DataDirectory| variable will be expanded as follow:

      - For applications placed in a directory on the user machine, this will be the app's (.exe) folder.
      - 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

Under the hood, the value for |DataDirectory| simply comes from a property on the app domain. It is possible to change that value and override the default behavior by doing this:

      AppDomain.CurrentDomain.SetData("DataDirectory", newpath)

For customizing the connection string at runtime, please see our team blog at: http://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

Where is my data? -- Understanding the file copy for desktop projects