How to persist inserted records in an .MDF file?  Inserted records dissappear.

Posted on 2007-08-11
Last Modified: 2013-11-26
How do you persist data in an .MDF file?  I created an app that has a data source to an sql .MDF file, but I am not using the server (kind of like using an access database file).  I execute this code with no error, but when I close the app and open it again, the data is not in the table.  Why would this be?

My test code is like this

            SqlConnection myConn = new SqlConnection(s.ConnectionString);

            for (int i = 0; i < 1000; i++)
                string SQL = "INSERT INTO TEST_TABLE (FIELD1, FIELD2, FIELD3)" +
                             " VALUES ('TestData1'," +
                             "'TestData3'," +

                SqlCommand ins = new SqlCommand(SQL, myConn);
Question by:searchei
    LVL 32

    Expert Comment

    by:Daniel Wilson
    Reading / Writing the .MDF file directly is not supported.

    I don't know why no error was raised and I don't know how you're reading the file to verify what's in it.  But if you're going to use SQL Server, you need to talk to the server!

    With Access, you're not doing direct file reading/writing either.  But to get the database stuff (Jet, I think it still is) to know what database you talk to, you tell it the file.  You're still using the installation of the DBMS.
    LVL 8

    Accepted Solution

    DanielWilson might be on to something, but there is another typical mistake when working with MDF files.

    If you're wokring in a Visual Studio project, ensure that the mdf file that is part of your project is not set to "Deploy - Copy Always".  Otherwise, every time you restart your app the database will be overwritten with a fresh (and empty) copy.
    LVL 8

    Expert Comment

    Oh, to check this, right-click the MDF file in Visual Studio, and select "Properties".  This option is in the Properties window.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
    THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now