The problem of data not being saved is usually the result the Copy to output directory property of the database file. The Copy to output directory property controls if and when a file is copied during deployment of a VS application, including when you debug (F5) your application. (Debugging an application copies all the project files into a Debug directory as part of the build process.)

The default value for the Copy to output directory property is Copy Always which results in the original database from your project directory being copied every time you debug or deploy your application. Because the original copy of the database is copied every time, it appears that data is not being saved. What is really happening is that data is being saved, but the file with the data is being copied over.

Proposed Solutions

Change the Copy to output directory property

A quick solution is to set the Copy to output directory property to Copy if Newer. Using this setting results in the database only being copied if it is newer than the database in the output directory. This solution is not complete for two reasons:

• When your database is newer, it will still be copied over the database in the deployed folder, for example Debug, and will result in your database being over written.
• There are a number of things that can happen in VS that will make your database appear to be newer and cause a file copy. This can happen even if you have not modified the structure of your database.

Write a custom deployment Class

A better solution is to write a custom deployment Class that will create your database at first run but use the existing database once it has been created. This same Class could be used to update your database by applying T-SQL scripts to the existing database.

An example of a custom database deployment class is demonstrated in the Embedded Database web cast.

Mike Wachal
SQL Express team