Database will not keep data after app stopped Visual Studio C#

Question

Hope someone can help with this. The following code is meant to add an entry to a Table; however, whilst the data is added at runtime, when the app is closed it disappears. The Database is set to 'Copy if Newer'.

   private void AddToNameList()
        {
            SqlConnection con = new SqlConnection(connectionString);

            cmd = new SqlCommand("INSERT INTO NameSurname VALUES (@NameId, @Surname)", con);
     
            cmd.Parameters.AddWithValue("@NameId", textBox2.Text);

            cmd.Parameters.AddWithValue("@Surname", textBox3.Text);
            con.Open();
            int i= cmd.ExecuteNonQuery();
            con.Close();
            if (i != 0)
            {
                MessageBox.Show(i + "Data Saved");
            }
            textBox2.Clear();
            textBox3.Clear();

        }

         private void button1_Click(object sender, EventArgs e)
        {
            if(textBox2.Text!= ("") && textBox3.Text!=(""))
            {
                AddToNameList();
            }
            else
            {
                MessageBox.Show("Please enter Name and Surname","Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

The message box confirms that the data is saved, and when I carry out a search it is there. However, when I stop the app running and check the database, the data is not there.

Grateful for any help.

Thanks

Answer 1

If you want to read a .mdf file, you must install SQL Server Express or SqlLocalDb runtime. Not all the other users are willing to download and install them.

To save the table to a database, I think SQLite will be a better choice.

Hope the following steps can help you.

First, install System.Data.SQLite using NuGet.

Second, configure the connection string in App.config.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
...
  <connectionStrings>
     <add name="SQLiteDbContext" connectionString="Data Source=MyDatabase.sqlite" providerName="System.Data.SQLite.EF6" />
  </connectionStrings>
</configuration>

Third, you can use the following code to create/insert/read the database/datatable.

// create
SQLiteConnection.CreateFile("MyDatabase.sqlite");
SQLiteConnection con = new SQLiteConnection("Data Source=MyDatabase.sqlite");
con.Open();
string sql = "create table NameSurname (Nameid varchar(20), Surname varchar(20))";
SQLiteCommand command = new SQLiteCommand(sql, con);
command.ExecuteNonQuery();
// insert
SQLiteCommand cmd = new SQLiteCommand("INSERT INTO NameSurname VALUES (@NameId, @Surname)", con);
cmd.Parameters.AddWithValue("@NameId", textBox2.Text);
cmd.Parameters.AddWithValue("@Surname", textBox3.Text);
cmd.ExecuteNonQuery();
// read
SQLiteCommand sqlCom = new SQLiteCommand("Select * From NameSurname", con);
SQLiteDataReader sqlDataReader = sqlCom.ExecuteReader();
int i = 1;
while (sqlDataReader.Read())
{
    listBox1.Items.Add(i);
    listBox1.Items.Add(sqlDataReader.GetValue(0));
    listBox1.Items.Add(sqlDataReader.GetValue(1));
    i++;
}
con.Close();