SQL CE Not updating database values from windows forms

Question

I am building a inventory based application that can be directly run from any pc without any installation and so i am using SQL CE.. On Start, I am checking if database is present. If Not i am creating new database as :

        try
        {
            string conString = "Data Source='ITM.sdf';LCID=1033;Password=ABCDEF; Encrypt = TRUE;";
            SqlCeEngine engine = new SqlCeEngine(conString);
            engine.CreateDatabase();
            return true;

        }
        catch (Exception e)
        {
            //MessageBox.Show("Database Already Present");
        }

The database is created properly and i can access the database to create tables as well.. The Problem i am facing is - I am inserting and updating records in windows form on button click with code :

         using (SqlCeConnection connection = new SqlCeConnection(DatabaseConnection.connectionstring))
        {
            connection.Open();
            String Name = NameTxt.Text.ToString();
            String Phone = PhoneTxt.Text.ToString();
            double balance = Double.Parse(BalanceTxt.Text.ToString());
            String City = CityTxt.Text.ToString();
            string sqlquery = "INSERT INTO Customers (Name,Phone,Balance,City)" + "Values(@name,@phone, @bal, @city)";
            SqlCeCommand cmd = new SqlCeCommand(sqlquery, connection);
            cmd.Parameters.AddWithValue("@name", Name);
            cmd.Parameters.AddWithValue("@phone", Phone);
            cmd.Parameters.AddWithValue("@bal", balance);
            cmd.Parameters.AddWithValue("@city", City);
            int x = cmd.ExecuteNonQuery();
            if (x > 0)
            {
                MessageBox.Show("Data Inserted");
            }
            else
            {
                MessageBox.Show("Error Occured. Cannot Insert the data");
            }
            connection.Close();
        }

and Updation Code is

        using (SqlCeConnection connection = new SqlCeConnection(DatabaseConnection.connectionstring))
        {
            connection.Open();
            int idtoedit = select_id_edit;
            String Name = NameEditTxt.Text.ToString();
            String Phone = metroTextBox1.Text.ToString();
            String City = CityEditTxt.Text.ToString();
            string sqlquery = "Update Customers Set Name = @name, Phone = @phone,City = @city where Id = @id";
            SqlCeCommand cmd = new SqlCeCommand(sqlquery, connection);

            cmd.Parameters.AddWithValue("@name", Name);
            cmd.Parameters.AddWithValue("@phone", Phone);
            cmd.Parameters.AddWithValue("@id", idtoedit);
            cmd.Parameters.AddWithValue("@city", City);
            int x = cmd.ExecuteNonQuery();
            if (x > 0)
            {
                MessageBox.Show("Data Updated");
            }
            else
            {
                MessageBox.Show("Error Occured. Cannot Insert the data");
            }
            loadIntoGrid();
            connection.Close();
        }

Whenever i execute code for inserting and updating records - Records are reflected in datagrid filled with adapter from database table. But once i restart the application, values do not appear in database. Once in a million, values are reflected in database. I cannot understand the reason behind this issue.

I have reffered to these articles :

C# - ExecuteNonQuery() isn't working with SQL Server CE

Insert, Update, Delete are not applied on SQL Server CE database file for Windows Mobile

But since i am creating database programmatically - It is getting created directly to bin/debug directory and i cannot see it in solution explorer in visual studio for changing copy options

Answer 1

You probably rewrite your database file with a blank copy from your project.

See this answer. You should not store your database in a bin folder, rather you should find a place in user's or public profile in AppData or another folder, depending on your needs.

The connection string would look like this:

<connectionStrings>
    <add name="ITMContext" connectionString="data source=|DataDirectory|\ITM.sdf';LCID=1033;Password=ABCDEF; Encrypt = TRUE;" providerName="System.Data.SqlServerCe.4.0">

You shuld deploy your db file with your custom code run at app's starup to a chosen location, see this ErikEJ blog: http://erikej.blogspot.cz/2013/11/entity-framework-6-sql-server-compact-4_25.html

private const string dbFileName = "Chinook.sdf";

private static void CreateIfNotExists(string fileName)
{
    string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
    // Set the data directory to the users %AppData% folder            
    // So the database file will be placed in:  C:\\Users\\<Username>\\AppData\\Roaming\\            
    AppDomain.CurrentDomain.SetData("DataDirectory", path);

    // Enure that the database file is present
    if (!System.IO.File.Exists(System.IO.Path.Combine(path, fileName)))
    {
        //Get path to our .exe, which also has a copy of the database file
        var exePath = System.IO.Path.GetDirectoryName(
            new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
        //Copy the file from the .exe location to the %AppData% folder
        System.IO.File.Copy(
            System.IO.Path.Combine(exePath, fileName),
            System.IO.Path.Combine(path, fileName));
    }
}

Check also this post.

You have several options to change this behavior. If your sdf file is part of the content of your project, this will affect how data is persisted. Remember that when you debug, all output of your project (including the sdf) if in the bin/debug folder.

• You can decide not to include the sdf file as part of your project and manage the file location runtime.

• If you are using "copy if newer", and project changes you make to the database will overwrite any runtime/debug changes.

• If you are using "Do not copy", you will have to specify the location in code (as two levels above where your program is running).

• If you have "Copy always", any changes made during runtime will always be overwritten

The database file in your project can be different when debugging or not. Check also this answer. You might be writting your records to another copy of the database file.