C# Updating a DataGridView/DataSource from MySQL

Question

I'm writing a program that updates user accounts on a MySQL database. The program is going to be used on multiple computers at once.

Currently I load the MySQL data into a DGV by this and refresh in a similar way;

mySqlDataAdapter = new MySqlDataAdapter("SELECT * FROM a_Users;", connection); // Query the database
DataSet DS = new DataSet(); // Create new DataSet
mySqlDataAdapter.Fill(DS); // Fill the Dataset with the information gathered above
dataGridView1.DataSource = DS.Tables[0]; // Set the dgv source to the newly created DataSet

and when editing I get the DGV row to a DataRow, and then update the DGV by changing the DataRow like this (from another form);

DataRow dr = (dataGridView1.Rows[SelectedRow].DataBoundItem as DataRowView).Row; // Get the selected row to a new DataRow
dr["Phone Number"] = PhoneNumber_tb.Text;

My problem is, I want to update the DGV with any new/modified rows from the MySQL database every xx seconds, and if modifying a row when this happens the DataRow is then Invalid as the above code re-makes the whole structure.

Is there a way to update the DGV or DGV DataSource and maintain the ability to use the DataRow I pulled for editing? How can I update the Datasource with any changes from the MySQL database.

I have tried BindingSource and a whole bunch of other things I have googled.

I could find UserID in the DGV then update the row that way, if no answer is found here.

Currently when editing a user, if the DGV refreshes with the SQL Database my edit form will not update the DGV as the DataRow no longer exists where it was.

I'd turn off the timer that is refreshing the dataset while I was editing a row. Send the changes to the db then turn refreshing back on — Caius Jard

D4RKKNIGH7 D4RKKNIGH7 · Accepted Answer · 2019-05-28T08:29:03

This is what I am using, works nicely. Only gets the rows that have been updated since we last checked. Still using the example in the question to retrieve the Database initially

mySqlDataAdapter = new MySqlDataAdapter("SELECT * FROM a_Users WHERE DTGModified > " + LastUpdated.ToString() + ";", connection); // Query the database
DataTable DT = new DataTable(); // Create new DataSet
mySqlDataAdapter.Fill(DT); // Fill the Dataset with the information gathered above
LastUpdated = DateTime.Now.ToString("yyyyMMddHHmmss"); // Save the time we retrieved the database

foreach (DataRow dr in (dataGridView1.DataSource as DataTable).Rows)
{
    foreach(DataRow DTdr in DT.Rows)
    {
        if (dr["ID"].ToString() != DTdr["ID"].ToString()) continue;

        dr.ItemArray = DTdr.ItemArray;
    }
}

C# Updating a DataGridView/DataSource from MySQL

1 Answers