Debugging SQL Express Apps - Beware VS Copy To Output Directory default

Here is a little trick that has bitten me on more than one occasion, and just bit someone who attended my data binding session in NYC the other night.

Here is the setup to be bitten by the defaults in Visual Studio:

– Add a SQL Express database to your project

– Edit the schema, add some tables, etc.

– Write some code / data binding that uses the database

– Run a debug session adding records or modifying ones you created through the designer.

– Save the changes from your running debug session.

– Shut down the app and run another debug session, and your changes are no longer there.

First instinct at this point is “there is something screwed up with my data binding / data access code”. That instinct may be wrong (although we are all good at writing bugs as well).

When VS adds the MDF file to your project, it places it and its related LDF file in the project root folder, and add them to your project in solution explorer. The file properties for the MDF file include Build Action = Content, and Copy To Output Directory = Copy Always.

What is happening is that your changes are being persisted to the copy of the MDF file that was placed in your build output directory (bin\debug) on the first debug run. Then on the second debug run, the unchanged MDF file from the project root folder is copied down into the build output folder, overwriting the one that was there with your changes from the previous run. As a result, it looks like your changes were not persisted when in fact they were (or may have been if your code was correct).

The solution is that whenever you add a SQL Express DB to your VS project, you probably want to change the Copy To Output Directory property for the file to Copy If Newer.