Working with a Local Data File in VS

Working with a Local Data File in VS

Rate This
  • Comments 19

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

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post
  • Hi Karthik,

    The article explains the problem in the second to last paragraph:

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

    If you don't want to work with two copies then I'd suggest working with an attached SQL server instance, it's a lot easier to manage at development time. You can always change the connection string in the app.config after deployment.

    HTH,

    -B

  • Hey B!

    I am new at visual basic 2005 and ur videos have been such a great help!

    I have the same problem as Karthik while working with a local database file. By working with an attached SQL server instance, you mean creating a new server?

  • Thanks for your effort.  I have tried it with .mdf, .mdb etc.  same result.  I make the slightest change and all the data is lost.  I also cannot find the database under Program Files... am concerned as to how am I supposed to back it up.  VB also doesn't allow me to point directly at the database under settings.  Kindly assist!

  • Your page doesn't render properly in CHROME.   Your text starts about 2/3 of the way down the page and is not visible when you open the document.

Page 2 of 2 (19 items) 12