update only modified data on the MySQL database from a C # module-CodePudding

I am creating a form to fill in with data, only now I have to do the save button with consequent loading of ONLY modified data on the mysql server, also to be able to create logs of which changes have been made and by whom, I was thinking of using the cache to compare the data just taken from the database with those that are about to be loaded in order to check if there are changes and if necessary load them, but it does not seem a good idea (maybe I'm wrong), so I thought about doing this code, to check at least if the comboboxes are empty or not, even if honestly it seems to me a very bad idea, another idea that came to me is to update every data, but it seems like a horrible idea. Do you think I should try the cache?

        private void Save_Click(object sender, RoutedEventArgs e)
    {

        MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["dbx"].ConnectionString);
        connection.Open();
        string myString = Convert.ToString(TipoClass.Text);
        MySqlCommand cmd = new MySqlCommand("UPDATE `evento` SET @A1 @B1 @A2 @B2 @A3 @B3 @A4 @B4 @A5 @B5 WHERE 1", connection);
        if (!string.IsNullOrEmpty(b1.Text)) { cmd.Parameters.AddWithValue("@A1", "`A1` = "); cmd.Parameters.AddWithValue("@B1", B1.Text   ","); }
        if (!string.IsNullOrEmpty(b2.Text)) { cmd.Parameters.AddWithValue("@A2", "`A2` = "); cmd.Parameters.AddWithValue("@B1", B2.Text   ","); }
        if (!string.IsNullOrEmpty(b3.Text)) { cmd.Parameters.AddWithValue("@A3", "`A3` = "); cmd.Parameters.AddWithValue("@B1", B2.Text   ","); }
        if (!string.IsNullOrEmpty(b4.Text)) { cmd.Parameters.AddWithValue("@A4", "`A4` = "); cmd.Parameters.AddWithValue("@B1", B2.Text   ","); }
        if (!string.IsNullOrEmpty(b5.Text)) { cmd.Parameters.AddWithValue("@A5", "`A5` = "); cmd.Parameters.AddWithValue("@B1", B2.Text); }
        {
            cmd.ExecuteNonQuery();
            connection.Close();
        }
    }

CodePudding user response：

I think these kinds of updates work best when you are using ORM like entity core and when you group your data inside a model, which you just feed the ORM with and then the heavy lifting is done for you. However, if that doesn't work for you and if you really need a log of all changes, I would simply update all the data(not bother checking differences in the code), and then I would handle the rest with sql triggers or stored procedures(check the differences and extract them to another table).Also, instead of updating you can insert a new row to the table with the name of the author and the timestamp, that way you can always search for the changes easily in the sql. You just have to handle the correct extraction of the latest updated entry when displaying that row information in your application...