Changes do not get saved permanently into SQL Server Compact edition

Question

I am having a weird problem. I am using vs2012 to connect to SQL Server CE and executing some insert queries.

public void EstablishConnection()
{
        connection = new SqlCeConnection("Data Source=DataDump.sdf;Persist Security Info=False;");
        try
        {
            connection.Open();
            Console.WriteLine("Connection Successful");
        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
        }
    }

    public void AddRecord(string link,string content)
    {
        int num = 0;
        var command = new SqlCeCommand("INSERT INTO Webpages(Link,Data) VALUES('"+link+"','"+content+"');",connection);
        num = command.ExecuteNonQuery();
        Console.WriteLine("Command Successful rows affected"+num);
        var cmd2 = new SqlCeCommand("SELECT * FROM Webpages",connection);
        SqlCeDataReader reader = cmd2.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(reader[0]);
            Console.WriteLine(reader[1]);   
        }
    }

However I am having the problem that once VS is closed and when later I open it to display the data, the data is gone as it was never saved

How is that possible when it is clear then it executed the query?

Did you have your database file listed between your project items? Is its property `Copy to OutputDirectory` set to `Copy Always`? — Steve, Oct 12 '13 at 16:39
yes the database is listed between project items, however its copy to property is set to Copy If Newer. Here's the pic http://s23.postimg.org/c8ghiu9x7/Capture.png — Win Coder, Oct 12 '13 at 16:44

score 7 · Accepted Answer · edited May 23 '17 at 10:25

7

It is a common scenario.
You have your database file listed between your project items.
Its property Copy to Output directory is set to Copy Always.

Now, you run your debug session with VS. The compile is succesfull and VS copies your sdf file from the project folder to the current output directory (BIN\DEBUG).
Your code runs smoothly and inserts the data in your database file (on the output directory).
You stop and restart the debug session to fix something, but, at restart, the VS recopies the empty file from the project folder to the output directory.

To break this circle, set Copy to Output Directory to Copy Never (or Copy if Newer)

EDIT Another source of confusion is due to the use of SERVER EXPLORER to view the contents of your database file. If the server explorer use a connection string that points to the database file in the project folder you never see the changes made to the database file in the Output Directory.
You should create two connections in Server Explorer, one named DEBUG DataDump that points to PROJECTFOLDER\BIN\DEBUG. You could use this connection to check the data inserted during debug or for other debugging tasks. Another one, called DISTRIBUTION DataDump, points to the file in the project folder and you make here your schema changes needed for the distribution of your app.

Said that, keep in mind that your code has a BIG problem. It is called Sql Injection

A parameterized query will avoid quotations problems and remove the Sql Injection

int num = 0;
var command = new SqlCeCommand("INSERT INTO Webpages(Link,Data) " + 
                               "VALUES(@lnk, @cnt)",connection);
command.Parameters.AddWithValue("@lnk", link);
command.Parameters.AddWithValue("@cnt", content);
num = command.ExecuteNonQuery();

edited May 23 '17 at 10:25

Community

1
1

answered Oct 12 '13 at 16:43

Steve

213,761
22
232
286

But it is set to Copy If Newer – Win Coder Oct 12 '13 at 16:45
Using Server Explorer to see the changes? Then look at the update – Steve Oct 12 '13 at 16:46
Ok so now i am using just query instead of server explorer to see the data. But as soon as i call connection.close() and then open again there is no data there. – Win Coder Oct 12 '13 at 16:50
Ok so now i inserted the data using sever explorer and it seems to be the only persistent data. What's wrong with my query. – Win Coder Oct 12 '13 at 16:52
So the `num` variable is not zero, but the following reader don't see any data? – Steve Oct 12 '13 at 16:52
Ok now i think i know what happens. Whenever i execute a query it will successfully display all the results even when i close vs and open it again. However now if i try to use Sever Explorer to access the Database, all the data gets deleted and i only see that data which i had inserted using Sever Explorer. – Win Coder Oct 12 '13 at 16:57
1

That's because your connection string points to a database file without any path. For Server Explorer this database is in your project folder, for your running program is in the BIN\DEBUG folder. – Steve Oct 12 '13 at 16:59
hmmm yup i check my folder and there are indeed two database's one in the project folder and one in the debug folder. So now the question arises which database is the correct one to point to ? – Win Coder Oct 12 '13 at 17:02
For any debug session the correct one is the one in BIN\DEBUG and you should create two connections in Server Explore, one renamed `DEBUG DataDump` that points to PROJECTFOLDER\BIN\DEBUG. You could use this for checking data inserted during debug. Another one called DISTRIBUTION DataDump points to the file in the project folder and you make here your schema changes prior distribution of your release – Steve Oct 12 '13 at 17:05
Ok i have now modified my connection string to point towards the DB in the Project Folder. I am grateful for your kind help otherwise i would have been struck on this for god knows how long :) – Win Coder Oct 12 '13 at 17:10
And as far as SQL injection is concerned this program is for internal use and will not be accessible for the end user. – Win Coder Oct 12 '13 at 17:12
Well if internal use is just you or some very reliable persons then OK, but if you have other people working for you, that's not really a secure situation on the long term period. – Steve Oct 12 '13 at 17:15

score 0 · Answer 2 · answered Oct 12 '13 at 16:41

set Copy to Output Directory property as Copy if newer for your sdf file

it seems now you have set it as copy always which result :

The database file is copied from the project directory to the bin directory every time that you build your application. Any changes made to the data file in the output folder are overwritten the next time that you run the application.

Changes do not get saved permanently into SQL Server Compact edition

2 Answers2