Working with local databases

---

Overview

In Visual Studio 2005 we added a number of features to help developers build and deploy applications that need a local data store. Here's a quick peek at how it works.

In order to work with a local database file, you can simply add the file to your project (e.g. using the Project/Add Existing Item... menu). We currently support adding SQL Server data files (.mdf), Jet (Access) data files (.mdb) and SQL Mobile data files (.sdf). Note that in order to be able to use .mdf files, you need to have installed SQL Server Express. SQL Express is available on the VS CD or at https://go.microsoft.com/fwlink/?LinkId=49251. With SQL Server Express installed, you will also be able to create new databases through 'Project/Add New Item…/Database'.

Once the database file is in the project, VS will do a few things:

      1. It will automatically add a connection in the Database Explorer so you can edit the database schema or the data.

      2. It will make sure that the connection strings are serialized using a relative path (more on this below).

      3. The first time the file is added, VS will also launch the Data Source wizard to create a new typed dataset.

---

Full path vs relative path

One of the reasons why it was hard to work with database files before is that the full path to the database was serialized in different places. This made it harder to share a project and also to deploy the application. In this version, the .NET runtime added support for what we call the DataDirectory macro. This allows Visual Studio to put a special variable in the connection string that will be expanded at run-time. So instead of having a connection string like this:

      "Data Source=.\SQLExpress;AttachDbFileName=c:\program files\app\data.mdf"

You can have a connection string like this:

      "Data Source=.\SQLExpress;AttachDbFileName=|DataDirectory|\data.mdf"

This connection string syntax is supported by the SqlClient and OleDb managed providers.

By default, the |DataDirectory| variable will be expanded as follow:

      - For applications placed in a directory on the user machine, this will be the app's (.exe) folder.
- For apps running under ClickOnce, this will be a special data folder created by ClickOnce
- For Web apps, this will be the App_Data folder

Under the hood, the value for |DataDirectory| simply comes from a property on the app domain. It is possible to change that value and override the default behavior by doing this:

      AppDomain.CurrentDomain.SetData("DataDirectory", newpath)

For customizing the connection string at runtime, please see our team blog at: https://blogs.msdn.com/smartclientdata/archive/2005/07/25/443034.aspx

---

Where is my data? -- Understanding the file copy for desktop projects

One of the things to know when working with local database files is that they are treated as any other content files. For desktop projects, it means that by default, the database file will be copied to the output folder (aka bin) each time the project is built. After F5, here's what it would look like on disk

      MyProject\Data.mdf

      MyProject\MyApp.vb

      MyProject\Bin\Debug\Data.mdf

      MyProject\Bin\Debug\MyApp.exe

At design-time, MyProject\Data.mdf is used by the data tools. At run-time, the app will be using the database under the output folder. As a result of the copy, many people have the impression that the app did not save the data to the database file. In fact, this is simply because there are two copies of the data file involved. Same applies when looking at the schema/data through the database explorer. The tools are using the copy in the project, not the one in the bin folder.

If this copy behavior is not what you want, there are few ways to work around it:

1. If you select local database file in the Solution Explorer window, you will see a property called ‘Copy to Output’ in the Properties window. By default it is set to ‘Copy Always’ which means that on each build, the data files in the project folder will be copied to the output folder overwriting the existing data files if any. You can set this property to Copy Never and then manually put a copy of the data file in the output folder. This way, on subsequent builds, the project system will leave the datafile in the output folder and not try to overwrite it with the one from the project. The downside is that you still have two copies so after you modify the database file using the app, if you want to work on those changes in the project, you need to copy it to the project manually and vise-versa.

2. You can leave the data file outside the project and create a connection to it in Database Explorer. When the IDE asks you to bring the file into the project, just say no. This way, both the design-time and the run-time will be using the same data file but the downside is that the path in the connection string will be hard coded and therefore it’ll be harder to share the project and deploy the app. Before deploying the app, just make sure to replace the full path in the settings with a relative path.

 

Antoine
Software Design Engineer
Visual Studio Data Design-time

Comments

• Anonymous
  September 01, 2005
  The comment has been removed

• Anonymous
  October 24, 2005
  Hi There,
  
  Sneaky. Cost me a day or two and a lot of hair I can ill afford to lose!
  
  Question: Do I understand correctly that the DataBase Explorer tools should NOT be used to verify the contents of the working database during design time?
  
  i.e. after running the app I need to check whether the data was actually written correctly. I jumped to the DataBase Explorer, Show Table Data but this of course points to the project database and will not show the new changes.
  
  What tools/methods with Visual Studio should be used to examine the contents of the working database?
  
  MRW

• Anonymous
  November 18, 2005
  The 'Copy to Output' property lacks one crucial feature that defeats the whole purpose of this new feature: an option that simultaneously updates both files when one is modified, not just the one-way street as it stands right now. This "feature" is brewing up quite a stir in the MSDN forums.

• Anonymous
  November 27, 2005
  Do you know , if it is possible to set up from VStudio preferences, registry or somewhere the way the IDE handles the .MDF -database files so that as a pre-set setting for projects with database files would be "Copy if newer" instead of "Copy always" ?
  
  If there are a hotfixes of service packs coming to VStudio 2005, this would be a request from me to be fixed - the way VStudio is handling local database files by default is rather annoying.

• Anonymous
  December 14, 2005
  You saved me, what a relief!!!!!!
  Been trying to figure this out for a week...
  I reinstalled VStudio, I changed the version of sql server, I tested my app on a different machine, I searched the net for solutions and still nothing till I just wrote your article...
  
  Thanks again.... I can now move on, till i find the next hideous undocumented feature.....

• Anonymous
  December 25, 2005
  Having to re-write my VS2003 app because converting is not reliable! Now I've had to waste four days getting to the bottom of this. IDE should check to see if .mdf exists in bin - if it does it should use it otherwise revert back to Projectapp - it's not brain surgery!!
  MS should put a banner on VS home page warning that this new feature could cost you several days work!!

• Anonymous
  January 02, 2006
  I have a major issue with the way |DataDirectory| gets substituted. In a simple scenario when a precompiled web site xcopied to a target web server (e.g. a staging or production server). It seems |DataDirectory| still gets substituted with the value from the source server data directory not the target server’s directory. Extremely unproductive!

• Anonymous
  January 04, 2006
  I am using VS 2005 with SQL Server 2000. I do not seem to have any .mdf files in my working directories and the setup in 'do not copy', but I Still experience the problem that the database does not seem to be updated !!!

• Anonymous
  January 13, 2006
  The comment has been removed

• Anonymous
  January 19, 2006
  MS gives out a free program (MS Visual Basic2005 Express Edition) that is more difficult to use than the program MS Visual Basic.net 2003.
  The only drawback with that program was the steps in deploying the program. Microsoft should have a service pack to update the VB2005 Express Edition software. I have spent many hours trying to figure out how to get aroung the exception that was being thrown when I would run my program. I guess you don't anything for nothing!

• Anonymous
  February 10, 2006
  Here's the solution I came up with, which is a combination of some of the above approaches.
  
  1) Create a new database in your project, which places the database in your Solution Explorer.  The database should also appear in your Database Explorer.
  2) Design the database and enter data as necessary using Database Explorer.
  3) Build the project, which places a copy of your database in the bin folder
  4) In the Solution Explorer, click on your database and change the "Copy to Output Directory" propery to "Do Not Copy"
  5) In the Database Explorer, right click on your database and select "Modify Connection".  Locate the database that was placed in your bin folder noted in step 3 above.
  
  The IDE will now allow you to work with the same database in Database Explorer (design-time) AND in your application (runtime) because you're looking at the same database in both cases) and that file is never being overwritten by other copies.
  
  The only fault with this approach that I can see is that you won't be able to modify your database and have the modified database objects available to you at design-time.  Of course, most developers agree your database design should be solidified BEFORE you start application development, but in the real world it just never works out this way because of changing business needs.  If you need to modify the database, simply reverse steps 4 and 5 above and then repeat steps 2-5.  Of course, you'll loose any data changes made since the last time you performed steps 2-5.  If you need those data changes, then copy the database out of your bin folder and into your application folder prior to performing steps 2-5 above.
  
  There is no slick solution I can think of because no one solution will provide everyone with all the functionality needed, as we all have different programming styles.  The important thing is that you understand what is going on and come up with the solution that works best for you.  As long as you can remember that there are TWO copies of the database and you know WHEN, WHERE and HOW each is accessed then you'll be OK.
  
  I think we all agree, however, that this is a huge pain.  Microsoft should really consider a more friendly approach to managing design-time vs. run-time database files.  Just my two cents.

• Anonymous
  February 20, 2006
  I echo the questions from MRW about the best way to view the data.  I have hard-coded my references and marked "copy always" but still don't see an updated copy - I've looked in every folder I can find, but still don't see the updates.  In my particular case, I'm bringing data from a non-SQL Server source into the in-memory dataset, merging it with the SQL Server Express dataset, and updating the (or attempting to update) the mdf.  The datasets are merging fine because I can see the results on the screen, but I have yet to find a .mdf with the updates.
  
  Also, if I'm building a new application that references this same mdf, what's the best way to attach or reference it?  
  
  Does MS recommend a best practice for this?
  

• Anonymous
  March 06, 2006
  Why isn't this documented anywhere else??  Thanks for the article, I've spent several days trying to figure this out...
  

• Anonymous
  March 14, 2006
  I am getting this message that says:
  
  An error occured while creating the new data source:
  Could not get type information for 'app.appdataset'.
  
  I am at wits end, this is the third time i have built the complete db (30 tables).  I see nothing wrong.  If I build a simple test db (2 tables) I get no error.  Am I missing something here, I have been through a days worth of help files with nothing mentioning this...  I am about another hour away from taking the 500$ loss and switching back to vb6.

• Anonymous
  March 29, 2006
  To: KevinW
  
  Check if any of your tables is not named like any of reserved words in C#.
  
  One of my tables was named "System" and this was reason for the very same error message.

• Anonymous
  April 12, 2006
  wow. I've spent two days browsing the internet in frustration as to why my database wasn't being updated. Well I'm glad I stumbled upon this blog before I lost even more hair.

• Anonymous
  April 18, 2006
  Hi!
  I change propetie Copy to outoput directory on "Copy if newer". Now, it's work, data isn't lost...

• Anonymous
  April 30, 2006
  Hi, Have seen that this is a problem not only in express or beta releases, but also in the stansard version. After trying VS 2005 for some time now i was quite impressed by all the things functioning as it shuld. So when i discovered this stupid anomaly i blaimed myself for days not consulting googl(Have made a promise never to do it again).
  
  The reason this is not documented has to be because this functionality was never intended by Microsoft. They got embarased, and now they call it a feature?
  
  I think that what i am most disapointed about is not the fact that there can be bugs or unintended functionality in any system, but the fact that the company and people making this software is to proud to admit it.
  
  

• Anonymous
  May 08, 2006
  I've also run into this problem.
  Lucky for me, I've learned thru vb6 to check Goggle first.  
  And found the solution in less than 5 minutes.
  
  The Property is rediculously hidden and the solutions are far too complex for an absolute beginner.
  
  The word 'copy' is too generic and should be replaced by the work 'replace'.
  
  Another property should be added like:
  "Run against debug(bin) DB":  True or False
  and only if True:
  Enable "Copy to Output Directory"
  Then rename Copy to Output Directory to:
  "Replace debug(bin) DB with Original":   Never/Always/If Newer
  
  Now that I'm commenting, more propertys should be added:
  Debug File Name:
  Debug Full Path:
  
  Please update any and all reference material relating to this issues ASAP.
  
  
  

• Anonymous
  May 26, 2006
  My goodness, i and my colleagues have been scouring the net for this solution. I'm glad we've stumbled upon this blog. Someone tell MS to do things better next time with data connectivity. I have abandoned the above approach and am going to use remote connectivity. Similar to VS 2003.

• Anonymous
  June 05, 2006
  You must set on file property "Copy to output directory" value "Copy if newer".

• Anonymous
  June 18, 2006
  The comment has been removed

• Anonymous
  June 19, 2006
  I am a VS Newbie (but a database "Oldie") so here's my question:AbMath mentions "remote connectivity", which sounds like what I want. Can someone point me to a good article that describes this?I don't see ever using local databases once I learn how to write apps. I intend on connecting to db's that are on a different server. Surely in that case, VS wouldn't be trying to copy stuff. How do I set that up?BTW, when I say "ever using local databases" I may have to modify that statement. In the case of a mobile app, the device would use a local database, that has to sync with a server database. In such a case, am I back into the whole local database issue?Thanks,Mac

• Anonymous
  July 01, 2006
  Thanks for the article, and no thanks for the mis-design...I'm trying the code below as a workaround, by putting it in my app initialization. So far so good.This code simply checks if the app is running from my binDebug folder (change/expand as you like), and if it does, redirects the DataDirectory to two folders up - to the application folder (where the project sits).I didn't try deploying my app, but assuming the deployment does not sit in a binDebug folder, I think it should work - tell me if I miss any point... string baseDirectory = AppDomain.CurrentDomain.BaseDirectory; if (baseDirectory.EndsWith(@"binDebug", StringComparison.InvariantCultureIgnoreCase)) { string dataDirectory = baseDirectory.Replace("\bin\Debug", ""); AppDomain.CurrentDomain.SetData("DataDirectory", dataDirectory); }

• Anonymous
  July 08, 2006
  Sorry but I am confused, where do I find "Copy to Output Directory" in VS 2003. I am using VS 2003 and having the problem but could not find this property as suggested as solutionthanks

• Anonymous
  July 08, 2006
  If I understand it correctly this problem only happens in design time. What it I point the browser to the .aspx? I seem still have the same problem. I am using VS 2003 C#Thanks

• Anonymous
  July 16, 2006
  I am new to C# (sadly, 2005 version)...As I understand (or at least I think I did).... the solution for the database being updated is to set a Copy property to "Copy if newer" (if i got it right)???I have lost a lot of hiar, too... and called myself "Stupid" a lot of times... but now I have searched the Internet and I see the problems persists to many... :) ... and I say: "I am not so stupid!... Microsoft is... FOR WASTEING PEOPLE'S TIME!!"...Hope for a clear answer...

• Anonymous
  July 26, 2006
  The comment has been removed

• Anonymous
  August 16, 2006
  Great !! it wasted my several hours. thanks a lot for info. it would be better is MS team takes such issues seriously coz it happend with many guys it seems. but thanks again bud.

• Anonymous
  August 19, 2006
  Me Too - Days of frustration trying to follow the beginners tutorials only to find this blog after many hours of searching.

• Anonymous
  August 31, 2006
  What about 2 different programs using the same DB. If you are developing with a local DB wouldn't there be access problems? locked file errors and such?I'm writing a 2 part app. 1 part forms based the other is a system service. They both have to write and read from the same DB. Should make setting connection strings fun.(forgive me, I'm a newbie)

• Anonymous
  September 01, 2006
  Ditto. Worked on this for days - building and rebuilding - finally located the answer in this blog. This should be corrected.

• Anonymous
  November 15, 2006
  si es que estamos usando SQLExpress, y estamos creando una aplicación .NET para el acceso a datos, hay...

• Anonymous
  November 15, 2006
  si es que estamos usando SQLExpress, y estamos creando una aplicación .NET para el acceso a datos, hay

• Anonymous
  November 24, 2006
  PingBack from http://www.primetime-software.de/simon.steckermeier/PermaLink,guid,e9648ca4-81e7-4caf-b6d1-400a88a7a598.aspx

• Anonymous
  February 23, 2007
  The comment has been removed

• Anonymous
  February 23, 2007
  The comment has been removed

• Anonymous
  May 20, 2007
  The comment has been removed

• Anonymous
  July 11, 2007
  Even with the above tips, changes I make in a DataGridView are not saved when I close the Win Form. I see that the database copy in the bin directory has a new time stamp but changes in the grid are not to be found in the database file. I confirmed this by searching for text strings in the .mdf source code of the files in the bin directory. Only the original data was to be found. So it seems like some code is required to update the database when closing the form.

• Anonymous
  July 11, 2007
  PingBack from http://www.sitesugu.com/geekzone/76/

• Anonymous
  July 30, 2008
  The comment has been removed

• Anonymous
  January 18, 2009
  PingBack from http://www.keyongtech.com/2392083-copy-always-flag-for-mdf

• Anonymous
  January 20, 2009
  PingBack from http://www.hilpers.com/1025361-sqlexpress-connectstring-bei-mehreren-sql

• Anonymous
  January 20, 2009
  PingBack from http://www.hilpers-esp.com/402592-sql-servermobile-2005-a

• Anonymous
  January 21, 2009
  PingBack from http://www.keyongtech.com/449968-maybe-dumb-questions-but-what

• Anonymous
  January 22, 2009
  PingBack from http://www.hilpers.fr/920213-comment-se-connecter-a-une

• Anonymous
  May 29, 2009
  PingBack from http://paidsurveyshub.info/story.php?title=smart-client-data-working-with-local-databases

• Anonymous
  June 09, 2009
  PingBack from http://hairgrowthproducts.info/story.php?id=4340

• Anonymous
  June 19, 2009
  PingBack from http://debtsolutionsnow.info/story.php?id=13229