Working with a Local Data File in VS
Visual Studio 2005 made some big improvements in the way applications work with data, from BindingSources to the Data Sources Window all the way to a free version of SQL-Server called SQL-Server Express. I've had a few customers get stuck on using SQL-Server Express local database files with their project so I thought I'd post on how this works.
The problem that people are having is that when you add a local database file to your project, VS will happily create the proper connection string for you and immediately get you connected and building DataSets, but when you run the project and save some data, then run the app again, you'll notice that your data is gone. Which means you spend time trying to debug your dataset or your connection when your data actually saved just fine.
In my Forms over Data Videos I build a database from scratch which attaches the database to my SQL-Server Express database instance. This is the same experience when creating databases on remote SQL-Servers. The actual database file is located in the Data directory under the appropriate SQL-Server instance, in my case: \\BETHFACE\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data. However, in order to send the data with my code samples I detached the database and added the database MDF file into the project itself and changed the connection string in My.Settings -- at this point it became a local SQL-Server Express data file.
If you look in those sample projects, open the Solution Explorer, and look at the Properties for the MDF file you will see that the Build Action is set to Content and the Copy to Output Directory is set to Copy if Newer. (If not, tell me - I must have missed one <g>). By default, MDF files are treated like any other content file and so it is set to Copy Always. When you change it to Copy if Newer, the database file will be copied to your build output directory only if you made any schema changes to the file or modified the data in VS. This is because there are actually two copies of your MDF file (as a matter of fact there are two copies of any of your files with Build Action = Content).
One copy is in your project folder, this is the file you work with when making schema changes with the VS tools. The VS tools always use this connection. However, you'll notice that the connection in the My.Settings uses a macro called |DataDirectory| to open the connection when your app is running (AttachDBFileName=|DataDirectory|\OMS.mdf). The |DataDirectory| expands to the location the EXE is running during runtime. So because of the default behavior of always copying out content files to the build directory, your database gets copied and it just looks like your data is not saving, sending you on a wild goose chase.
Just remember to mark your databases as Copy if Newer that way you only "lose" data when you make changes to the structure of the database itself or if you add data via the Visual Studio designers. For more information check out this post as well. I'll be creating a video on how to work with local database files including how to deploy them to your users in the next series.
UPDATE: Here's that video - #10
Beth is a Program Manager on the Visual Studio Community Team at Microsoft and is responsible for producing and managing content for business application developers, driving community features and team participation onto MSDN Developer Centers (http://msdn.com), and helping make Visual Studio one of the best developer tools in the world. She also produces regular content on her blog (http://blogs.msdn.com/bethmassi), Channel 9, and a variety of other developer sites and magazines. As a community champion and a long-time member of the Microsoft developer community she also helps with the San Francisco East Bay .NET user group and is a frequent speaker at various software development events. Before Microsoft, she was a Senior Architect at a health care software product company and a Microsoft Solutions Architect MVP. Over the last decade she has worked on distributed applications and frameworks, web and Windows-based applications using Microsoft development tools in a variety of businesses. She loves teaching, hiking, mountain biking, and driving really fast.
