Debugging with Local Database File

Most of the time, your business application needs to deal with data stored in a database. Sometimes the database is on a remote server, while sometimes it is a local database file (SQL Server Compact database file, SQL Server Express database file, or Microsoft Access database file).
One of the frequently asked questions about debugging an application that has a local database file is: “my update method executes successfully, why the database data is not updated?”

My data is not updated!

For example, my project has a SQL Server Compact database file (Northwind.sdf), and I add a dataset with Customer table data in my database file:

In Program.cs file, input following code to Main method: 
            NorthwindDataSet ds = new NorthwindDataSet();
NorthwindDataSetTableAdapters.CustomersTableAdapter ta = new ConsoleApplication1.NorthwindDataSetTableAdapters.CustomersTableAdapter();
ds.Customers[0].Postal_Code = "11111";
int result = ta.Update(ds);

Now if you hit F5 to debug this project and check the return value of Update method, the value is 1, which means one row was successfully updated. Now double click the Northwind.sdf file in Solution Explorer, and choose “Show Table Data” on Customers table:
You will notice that the Postal_Code is not updated at all!

What happened?

Let’s check the property of the local database file:

There’s a property “Copy to Output Directory” and the default value is “Copy if newer” (if you’re using .mdf or .mdb file, the default value is “Copy always”). You could check this MSDN document to learn what this property means. In short, the local database file will be copied to Output directory, and THAT database is the one that will get updated.

Let’s select “Show All Files” button on upper left corner of Solution Explorer, and navigate to bin/Debug folder. Now you could see the Northwind.sdf file in output directory:

If you double click on this file, Server Explorer will create a connection to this database file. “Show Table Data” now! You will see the Postal_Code of first customer is now "11111”.

What other options do I have?

If you don’t want Visual Studio to copy the database file for you, you could set the “Copy to Output Directory” property to “Do not copy”. Then it’s your choice when and how to overwrite the database file. Of course, you still need two copies of database file: at design time, you’re using the database file in solution directory, while at run time, you’re modifying the one in output directory.

If you want to get rid of this “two-copy-confusion” and want both design time and run time using the same copy of database file, you should select No when IDE offers to “copy the file to your project and modify the connection”.
One thing worth mentioning is, if you leave the database file outside of your project, the path of the database file will actually be hardcoded into your project. Before deployment, please update this setting and replace the full path with a relative path.


08/04 Update: this MSDN How to is helpful too. It describes in detail how to manage local data files in your project.