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 3 and 4 and type the answer here:
  • Post
  • Have really enjoyed viewing  (and trying out) the techniques in your videos so far, Beth.   Keep them coming!

    In addition to covering the local data file isse in an upcoming video, may I suggest you also include the Many-To-Many DataBinding in a video also?  (Just to reinforce the instructions given in your earlier blog item).

  • I'm very insterested by your videos !

    I redo your course but when I tried to create the stored procedures, I don't have the templates (Delet, Insert, Update) in the toolbox of my Visual Studio 2005 Professional. I've to create them manually.

    What I have to do, to obtain that templates in the toolbox ?

    Best regards.

  • Hi Luc,

    Stored procedures are not created automatically for you. I wrote the stored procedures for the video and put them as snippets in the toolbox. You can create code snippets by selecting text in the code editor and then dragging that onto the toolbox. Then you can use that snippet of code again by dragging from the toolbox into the editor.

    You do not need to create stored procs for your update, insert or delete to work on a table though. The TableAdapter wizard will generate those statements instead if you don't use stored procs.

    Cheers,

    -B

  • One of the features of the latest Interop Forms Toolkit is the ability to develop UserControls in addition

  • Thank you very much for clearly explaining everything. I am new to programming and tried everything to learn how to use SQL, but this finally helped me solve my problem!

  • Please let Me Know How To Save a form in Visual basic 2005 When you have a computed columns

  • Thank you very much Beth, your tutorials have helped me a great deal :)

  • Thank you so much, i've been spending countless hours to see how i can get my winforms app to pick up the mdf file from the application running directory instead of hardcoding the project directory to the connection string. the trick was to use the macro that you suggested \|DataDirectory|\

    Again that you :)

  • Hi,

    I am in the process of a small database prog using MSAccess. I am using related data tables to add, edit, del, update. While my parent datatable gets updated and saved i am unable to update or save the child table. i am getting an error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows.".

    Please let me know "how to enable Refresh Data Table in Advanced Options of Table Adapter Configuration Wizard in Visual Studio Express 2008"

    Awaiting your response asap

    Thanks

    Ravi

    ravi323in@hotmail.com

  • Hi,

    I am in the process of a small database prog using MSAccess. I am using related data tables to add, edit, del, update. While my parent datatable gets updated and saved i am unable to update or save the child table. i am getting an error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows.".

    Please let me know "how to enable Refresh Data Table in Advanced Options of Table Adapter Configuration Wizard in Visual Studio Express 2008"

    Awaiting your response asap (Posted earlier on 12/06/2008)

    Thanks

    Ravi

  • Beth,

    When you detach the mdf and then add the file into the project itself, does that mean I can transport the project to another machine that doesn't have SQL Server running and work from there?

    Also, when I compile and distribute, does that mean I don't have to make sure users have SQL Express installed?

    Scott

  • Hi Scott,

    You still need to have SQL-Server Express installed to work with a local data file so you'll need to select the SQL Express redist in your deployment project.

    A local data file is a detached MDF that can auto-attach and auto-detach itself to the service when you connect to it with a local user connection. It's a feature of SQL Server (and Express) 2005.

    If you only need single-user access to a simple data store you may want to use SQL Server Compact Edition instead. This is just a simple file-based, single user data store so only a few assemblies need to be distributed and there is no separate install other than your app.

    However if you want to go multi-user, you'll need to move to SQL Express.

    HTH,

    -B

  • Hello Beth,

    Thx for the article. I tried to play with properties of my local database and still not having the data saved I have made an output parameter of the id of the data I'm entering to the database and outputting it on the console and still have when I run the program the id is 1 for the first time I enter values and the second time the id is 2 and no data is saved when I look in the database and if I close the application and rerun it i get the same for the 1st time the id is 1 and 2nd time is 2 and so on that's as if nothing was saved.

    Thanks for your time.

    Bayan

  • Hi Bayan,

    Those ID's are only for the client-side dataset, the identity keys are generated by the database when you save them and may or may not be the same keys. Check the database table's contents by opening it directly in visual studio and see if the rows are there. You also should watch these videos on understanding data:

    VS 2005 version: http://msdn.microsoft.com/en-us/vbasic/bb725824.aspx

    VS 2008 verison: http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

    HTH,

    -B

  • sql1.Open();

                       SqlCommand ins_user = new SqlCommand();

                       ins_user.Connection = sql1;

                       string ins_query = "INSERT INTO User_Infoo(First_Name,Last_Name,Pass,UID,Email,Desg_ID,User_ID) Values(@Fn,@Ln,@p,@ID,@email,@DID,@user)";

                       SqlParameter p1 = new SqlParameter("@Fn", SqlDbType.NVarChar, 50);

                       SqlParameter p2 = new SqlParameter("@Ln", SqlDbType.NChar, 10);

                       SqlParameter p3 = new SqlParameter("@p", SqlDbType.Text);

                       SqlParameter p4 = new SqlParameter("@DID", SqlDbType.Int);

                       SqlParameter p5 = new SqlParameter("@user", SqlDbType.NChar,10);

                       //SqlParameter p6 = new SqlParameter("@Fn", SqlDbType.NChar, 50);

                       SqlParameter p6 = new SqlParameter("@email", SqlDbType.Text);

                       SqlParameter p7 = new SqlParameter("@ID", SqlDbType.Int);

                       ins_user.Parameters.Add(p1);

                       ins_user.Parameters.Add(p2);

                       ins_user.Parameters.Add(p3);

                       ins_user.Parameters.Add(p4);

                       ins_user.Parameters.Add(p5);

                       ins_user.Parameters.Add(p6);

                       ins_user.Parameters.Add(p7);

                       p1.Value = "Karthik";

                       p2.Value = "Uthaman";

                       p3.Value = "adfask";

                       p4.Value = 1;

                       p5.Value = "karthik_u";

                       p6.Value = "asdasd";

                       p7.Value = 1;

                       ins_user.CommandText = ins_query;

                       ins_user.CommandType = System.Data.CommandType.Text;

                       MessageBox.Show(ins_user.ExecuteNonQuery().ToString());

                       sql1.Close();

                       return true;

    i set the Build Action = Content and Copy to Output directory = Copy if newer !!

    Now i am able to insert and retreive data.. but the problem is that the inserted data is not visible when i click on the "Show Table Data" .

    and also the stored data is available just for few compilations.. if i build the app for 3 pr more times , the data tht has entered before are lost... please help me out pppll ....

    I don understand what kinda problem is thiss... somebodyyy plsss help me soon!!

Page 1 of 2 (19 items) 12