Clicking "No" on a message box results in the data still being saved to the database

Question

If I click the "No" button at MessageBoxButton.YesNo the data that is being typed is still inserting in the database. How should I fix this?

This is my code:

string insertQuery = "INSERT INTO db_personal(per_image,per_Fname,per_Mname,per_Lname)VALUES(@per_image,@per_Fname,@per_Mname,@per_Lname)";

connection.Open();

MySqlCommand cmd = new MySqlCommand(insertQuery, connection);
cmd.Parameters.AddWithValue("@per_image", newPicture.Image);
cmd.Parameters.AddWithValue("@per_Fname", newFirstName.Text);
cmd.Parameters.AddWithValue("@per_Mname", newMiddleName.Text);
cmd.Parameters.AddWithValue("@per_Lname", newLastName.Text);

try
{
    if (cmd.ExecuteNonQuery() == 1)
    {
        MetroFramework.MetroMessageBox.Show(this, "New student information has been successfully saved.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    else
    {
        MetroFramework.MetroMessageBox.Show(this, "Incomplete information. Are you sure you want to save?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

connection.Close();

Answer 1

First off you run the query:

if (cmd.ExecuteNonQuery() == 1)

And check the result to see what message you want to show. At this point the data is either already saved to the database or not. You then ask the user if they still want to save the data to the database but do nothing with that information, you'd want to do something like:

DialogResult dr = MetroFramework.MetroMessageBox.Show(this, "Incomplete information. Are you sure you want to save?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

if (dr == DialogResult.Yes)
{
    //Handle saving, although it probably already has been
    //Ask to re-enter the data?
}
else
{
    //Rollback the previous command
}

I have never had to rollback a command to a database but I found the method SqlTransaction.Rollback, although I don't know how it works or if it is applicable.

You should think about redoing your logic so that it flows better:

1. Check the input data looks as expected, all validation routines
2. Ask the user if they really want to save the information
3. IF yes save the data

So something like:

//Some validation

DialogResult dr = MessageBox.Show("Do you want to save the information?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
    var result = cmd.ExecuteNonQuery();

    //Do something with the result here
}

If the validation check fails but it is still compatible with the database you could also present the user with a message if they still want to continue:

DialogResult dr = MessageBox.Show("Incomplete information. Are you sure you want to save?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr != DialogResult.Yes)
{
    //Do not save the data
}