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