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 http://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: http://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 (49)

  1. CAC says:

    You hit the nail on the head with this one! This is very annoying and caused me several hours of wasted time because I thought the IDE was smart enough to switch during a debug step, etc.

  2. MRW says:

    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

  3. mntlnrg says:

    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.

  4. 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.

  5. DrMad says:

    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…..

  6. SteveT-UK says:

    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!!

  7. PJ Zargar says:

    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!

  8. EF says:

    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 !!!

  9. chuck says:

    I wish there was just a way to kill this beature. The workarounds are all a pain.

  10. RF Williams says:

    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!

  11. Sid McFarland says:

    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.

  12. Kay Castillo says:

    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?

  13. Alex Freitas says:

    Why isn’t this documented anywhere else??  Thanks for the article, I’ve spent several days trying to figure this out…

  14. KevinW says:

    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.

  15. PavlikCZ says:

    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.

  16. 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.

  17. Bojan says:

    Hi!
    I change propetie Copy to outoput directory on “Copy if newer”. Now, it’s work, data isn’t lost…

  18. cox says:

    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.

  19. BarryS says:

    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.

  20. AbMath says:

    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.

  21. I have the solutions! says:

    You must set on file property “Copy to output directory” value “Copy if newer”.

  22. MrBlobby says:

    I had two whole strands of hair before I tried updating a dataset with a TableAdaptor with VS2005 – Not any more!

    I tried everything – only to find whatever I did I could not see the data when updating after runtime was over.

    The frustration and stress!

    We have plenty of pointless red tape drain hours away from us here in the UK without this ‘feature’ doing the same!

  23. Mac says:

    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

  24. Buffa says:

    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);
    }

  25. mph130 says:

    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 solution

    thanks

  26. mph130 says:

    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

  27. Laura says:

    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…

  28. Rstone says:

    AAAAHHHHHHHHHHHHHHHHHHHHHHHHHH! Finally! I have searched For DAAAAAAAAAAAAAYYYYYYYYYYYYSSSSSSSS! Very Frustrated buy thankful for your blog. I don’t know if the new knowledge of such an easy solution makes me happy or more annoyed knowing that I rewrote code over and over and over trying to figure out what I was doing wrong. Thanks for the info. The info of this blog should be the first answer that comes up for all key words referring to data access. I got so frustrated that I gave up after days of looking for the answer thinking that programming was over my head. Luckily I gave it a second try. Thank you for your help

  29. Nik says:

    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.

  30. Phill Lewis says:

    Me Too – Days of frustration trying to follow the beginners tutorials only to find this blog after many hours of searching.

  31. Adrian L. says:

    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)

  32. Tommy says:

    Ditto. Worked on this for days – building and rebuilding – finally located the answer in this blog. This should be corrected.

  33. si es que estamos usando SQLExpress, y estamos creando una aplicación .NET para el acceso a datos, hay…

  34. si es que estamos usando SQLExpress, y estamos creando una aplicación .NET para el acceso a datos, hay

  35. asp.net2.0有一个特殊目录app_data,通常Sqlserver2005express数据文件就放在这个目录,相应的数据库连接串就是:connectionString=

  36. asp.net 2.0有一个特殊目录app_data,通常Sql server 2005 express数据文件就放在这个目录,相应的数据库连接串就是: connectionString="…… data

  37. Jackey says:

    asp.net2.0有一个特殊目录app_data,通常Sqlserver2005express数据文件就放在这个目录,相应的数据库连接串就是:connectionString=

  38. Andy says:

    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.

  39. Ryan Boo says:

    asp.net2.0有一个特殊目录app_data,通常Sqlserver2005express数据文件就放在这个目录,相应的数据库连接串就是:

    connectionString=

Skip to main content