Dealing with Local Databases, or Why Your Updates Don't Stick
Probably everyone in the world but me knows how Visual Studio handles database files during testing in a GUI-based project. Since I work primarily in ASP.NET projects, I didn't clue into this behavior until recently. But even if you do know about this "feature," you may not be aware of how you can improve on Visual Studio's default behavior.
Here's what happens: You add a database to some non-ASP.NET project to use for testing. You run some code that makes some updates and everything seems to work with all of your updates accepted. You then do a second run…and it's like your previous run never happened! And sure enough, when you go to Server Explorer and browse to the rows that should have been changed in the first run you can see that the data is unchanged.
That's because, when you press F5, Visual Studio quietly copies your database from your project's root folder to the bin/Debug folder, and that's the version of the database that your code updates. When you think about it, this is a good idea: Individual test runs leave your test data unchanged. But if you want one test run to build on the changes from a previous test run…well, you're out of luck.
To check the results of your test runs, you'll need to add a connection in Server Explorer to the version of the database in your bin/Debug folder. Just turn on Show All Files at the top of Solution Explorer, drill down through the bin and Debug folders, and double-click on the database file to add it to Server Explorer. While that will let you check the results of any test run in Server Explorer, you still won't be able to have one test run build on the changes made by a previous test run.
To get that to happen, you'll need to change the Copy to Output Directory property on the database file in your project's root. The default setting for the property is "Copy Always." The setting you probably want is "Copy If Newer". This will copy the database from the project root only if you make some change to the database and build your project. Now, if you make some changes in a test run, those changes will still be there in your next test run.
When you want to restore to your original test data, just delete the database file in the Debug folder from Solution Explorer and build your project (if you want to see the database file after it's been copied to the folder, you'll need to click the Refresh button at the top of Solution Explorer).
And my thanks to my buddy Greg Adams, who explained it to me (he didn't have to draw me pictures, but it was a near thing).
Posted by Peter Vogel on 05/14/2012