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:
clip_image001

In Program.cs file, input following code to Main method: 
            NorthwindDataSet ds = new NorthwindDataSet();
NorthwindDataSetTableAdapters.CustomersTableAdapter ta = new ConsoleApplication1.NorthwindDataSetTableAdapters.CustomersTableAdapter();
            ta.Fill(ds.Customers);
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:
clip_image002
You will notice that the Postal_Code is not updated at all!

What happened?

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

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:
clip_image004

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”.
clip_image005
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.

Cheers!

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

Comments

  • Anonymous
    July 30, 2009
    Maybe select "Do not copy", and make a hard link to the Release and Debug folders once? I think its should solve all problems.

  • Anonymous
    July 31, 2009
    Hi Ben, That works if you want to manage copying the database over manually if you update the schema. I prefer to set it to Copy if Newer so that Visual Studio will do that for me if I modify any of the tables/columns. FYI, we also have a video on this as well: http://msdn.microsoft.com/en-us/vbasic/bb643822.aspx Enjoy!

  • Anonymous
    November 28, 2009
    Hi Beth i have some problem about connet to database. Could you help me. Thx. An unexpected error has occurred. Error Message: Could not load type 'Microsoft.VisualStudio.DataDesign.SynDesigner.SyncFacade.SyncManager' from assembly 'Microsoft.VisualStudio.DataDesign.SynDesigner.DslPackage,Version=9.0.0.0, Culture=neutral, PlublicKeyToken=b03f5f7f11d50a3a'.

  • Anonymous
    November 29, 2009
    Hi, Lucio, The issue you encountered is due to the incomplete installation of VS SP1. Please refer to Shichao's post here: http://blogs.msdn.com/vsdata/archive/2009/08/21/some-error-messages-when-you-use-the-data-source-configuration-wizard-in-microsoft-visual-studio-2008-service-pack-1.aspx Please let us know if reinstallation doesn't solve your problem.

  • Anonymous
    March 16, 2010
    Thank you so much for sharing this information. I've been struggling a while with this problem.