Geeks With Blogs
Devin Rader's Blog I write the code...

I discovered an interesting (and extremely frustrating) phenomenon in working on my first Windows Forms project in VS2005.  VS2005 lets you add a SQL Express database to your project through the Add New Item dialog, which is really a convenient way of adding a data store to your application. 

When you add the database this way, VS2005 automatically adds it as a Data Source to the application and wires up the connection string info in the app.config file.  All was working well in my application until I tried to insert or update any new data into the database.  The data appeared to get saved and I could open and close the child forms in my app and everything worked well, until I stopped debugging and restarted the app.  When I completely restarted the app, and changes that I had made to the database were gone.

What made this even more frustrating was that opening the tables in VS2005 showed that apparently no data was ever actually being saved to the database.

After two days of pounding my head against a wall, I discovered this:

Apparently, when you add the database to the app, there is a property on the file called 'Copy to Output Directory', and by default its value is set to 'Copy Always'.  This meant that every time I ran the app in VS2005, the MDF in the app directory would be copied to the apps output directory, overwriting any changes that I had made in previous debugging sessions.  VS also uses a relative path in the connection sting it creates for the database connection:

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"

The |DatabaseDirectory| relative path points to the directory where the application executable starts from by default.  See this blog for more info on relative paths.

On top of that, to make everything even more confusing, using the VS2005 SQL editing tools to open the DB tables and examine their contents only opens the copy of the DB in the app directory, not the output directory.

So once I found the Copt To Output Directory property, I simply changed it to 'Copy if newer', which tells VS to only copy the file if I make a change to it (like adding a table).

Maybe its just me, but it seems like 'Copy if newer' should be the default setting for MDF file types.

Posted on Thursday, November 17, 2005 8:20 AM | Back to top

Comments on this post: Arg...VS2005 frustrations already

# re: Arg...VS2005 frustrations already
Requesting Gravatar...
Thanks! You post probably saved me hours! (already cost me 30 mins of google time)
I saw the property but never occurred to me that it would actually replace when debugging a project. By the way I'm using an Access/mdb so it's the same default setting for these.
Left by maxfloden on Mar 27, 2006 6:09 AM

Your comment:
 (will show your gravatar)

Copyright © Devin Rader | Powered by: