C# DGV - Show Live MYSQL DB table

Question

I have a scenario where I have multiple users (around 50 at one time) connected to one table all make changes constantly to rows that already exist. What I need to do is somehow always show a live view of the database.

Having a cached copy of the table is not a valid choice because all users need to see all changes instantly.

When I first load the database I am using

mydtadp = new MySqlDataAdapter();
bindingSource1 = new BindingSource();
table = new DataTable();
MySqlConnection MyConn = new MySqlConnection(MyConnection);
MyConn.Open();

mydtadp.SelectCommand = new MySqlCommand("SELECT * FROM `Appointments`", MyConn);
cmbl = new MySqlCommandBuilder(mydtadp);


mydtadp.Fill(table);

bindingSource1.DataSource = table;
dataGridView1.DataSource = bindingSource1;

However, when another user makes a change this is not updating the local DataGridView to reflect the change. After searching the web I tried setting the VirtualMode property to true. Still no luck.

Next, I created a timer that would tick once a second that would call

dataGridView1.Refresh();

Still, when another user would update the database the changes wouldn't be reflected on the local DGV. I found another article that suggested trying

bindingSource1.ResetBinding(false); 

I put that on my timer on tick function, still not updating.

I found an article on MDSN that suggested that I try calling the mydtadp.Fill(table); function again in the timer function. Same thing.

The only thing that I could get to work was to save the index of the currently selected row then fully reload the dataset then reselect that index. However, this seems clunky and there has to be a better way to do it. This method seems like bad practice, and it does not work very well the DGV flashes whenever the timer ticks and when the user moves their cursor it takes a couple of seconds for the datagridview to catch up with the user. When the user makes changes to a row each keystroke takes a full-timer tick to update.

Answer 1

I wouldn't recommend you fully update DGV each time since this will bring to performance issues.

You can add DateTime column to your MySQL table, and when the database table is updated, you just put current DateTime into it. In your WinForms app, you also add a variable for keeping last query DateTime.

Then when the timer ticks, you just select only those rows, which were updated since the DateTime variable.

Finally you individually update corresponding rows of the DGV.DataSource (I believe it is System.Data.DataView) with the fresh data from the SQL query.