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

Question

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();
        }
    }

r0x0r007 · Accepted Answer · 2022-08-26 10:46:20Z

1

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...

answered Aug 26, 2022 at 10:46

r0x0r007

462 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related