optimize updates to DataTable bound to DataGridView

Question

I have a Form in my application that displays some data. When I first show the Form, I load some data into a DataTable then bind the DataTable to a DataGridView. I also start an asynchronous method that executes some slower database queries. When these slow queries complete, I need to update a few hundred rows in the DataTable, filling in values returned from the slower queries, like so:

foreach (DataRow row in data.Rows)
{
    SlowLoadingData slow_stuff = slow_query_results[(int)row["id"]];

    row.BeginEdit();
    row[column_one] = slow_stuff.One;
    row[column_two] = slow_stuff.Two;
    row[column_three] = slow_stuff.Three;
    row.EndEdit();
}

This is extremely slow, hanging the UI thread for a minute or more, presumably because each row is triggering a redraw.

After some research, I found a way to make it fast. First, bind the DataGridView to a BindingSource that is bound to the DataTable, instead of directly to the DataTable. Then, do as follows when you make changes to the DataTable:

binding_source.SuspendBinding();
binding_source.RaiseListChangedEvents = false;
// foreach (DataRow in Data.Rows) ... code above
binding_source.RaiseListChangedEvents = true;
binding_source.ResumeBinding();
grid.Refresh();

There is a problem, though, and it's a doozy: the code above prevents the DataGridView from detecting new rows added to the DataTable. Any new rows added to the table do not appear in the grid. The grid may also throw exceptions if you use the arrow keys to move the current cell selection off the bottom end of the grid, because the underlying data source has more rows but the grid has not created grid rows to display them.

So, two possible solutions that I can see:

1. Is there a better way to suppress binding updates while making changes to the underlying DataTable?

2. Is there an easy way to tell the DataGridView to gracefully refresh its grid row collection to match the number of underlying DataTable rows? (Note: I've tried calling BindingSource.ResetBindings, but it seems to trigger more exceptions if you have removed rows from the DataTable!)

Answer 1

You can try using the Merge method on the DataTable. I'll try to create a simple demo app and post it here, but the idea is simple. When you want to update the Grid, query the results into a new DataTable, and then merge the old table with the new table. As long as both tables have primary keys (you can create them them im memory if they don't come back from the DB) then it should track changes and update the DataGridView seamlessly. It also has the advantage of not losing the users place on the grid.

OK, here's a sample. I create a form with two buttons and one dataGridView. On button1 click, I populate the main table with some data, and bind the grid to it. Then, on second click, I create another table with the same schema. Add data to it (some that have the same primary key, and some that have new ones). Then, they merge them back to the original table. It updates the grid as expected.

    public partial class Form1 : Form
    {
        private DataTable mainTable;
        public Form1()
        {
            InitializeComponent();
            this.mainTable = this.CreateTestTable();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            for (int i = 1; i <= 10; i++)
            {
                this.mainTable.Rows.Add(String.Format("Person{0}", i), i * i);
            }

            this.dataGridView1.DataSource = this.mainTable;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable newTable = this.CreateTestTable();
            for (int i = 1; i <= 15; i++)
            {
                newTable.Rows.Add(String.Format("Person{0}", i), i + i);
            }
            this.mainTable.Merge(newTable);
        }

        private DataTable CreateTestTable()
        {
            var result = new DataTable();
            result.Columns.Add("Name");
            result.Columns.Add("Age", typeof(int));
            result.PrimaryKey = new DataColumn[] { result.Columns["Name"] };

            return result;

        }
    }

Answer 2

Have you considered disconnecting the dataGrid or the bindingSource while filling the table and reconnecting afterwards? It might look a bit ugly, but it should be a lot faster.

Answer 3

If you're using a BindingSource for complex data binding, it's important to understand that SuspendBinding and ResumeBinding only suspend and resume binding for the current item. This lets you disable binding for the current item and change a bunch of its properties without any of the individual changes to the property being pushed out to the bound control. (This isn't explained in the documentation for the BindingSource, where it would be useful, oh no: it's in the documentation for the CurrencyManager.)

Any changes you make to the other items in the list, i.e. everything except the current item, raise the ListChanged event. If you disable these events, the BindingSource stops telling the bound control about changes to the list until you re-enable them. This has the result you've seen: you add all of your rows to the underlying DataTable, but since you've turned ListChanged events off, the BindingSource doesn't tell the DataGridView about them, and so the DataGridView remains empty.

The proper solution is to call ResetBindings, which forces the BindingSource to refresh all of the controls bound to it, using the current values in its bound list.

What sort of exceptions are you getting after you call ResetBindings? Because it works just fine for me, whether I add, edit, delete, or remove rows from the underlying DataTable.

Answer 4

I ran into a similar problem. Here's a solution that is even simpler (if less elegant).

I found that this:

dataGridView.DataSource = null;
dataTable.BeginLoadData();
foreach(DataRow row in dataTable.Rows){
    //modify row
}
dataTable.EndLoadData();
dataGridView.DataSource = dataTable;

...is way faster than this:

dataTable.BeginLoadData();
foreach(DataRow row in dataTable.Rows){
    //modify row
}
dataTable.EndLoadData();

Cheers--DC

Answer 5

Just posting this as a solution: Making some notations to the comments and posts already. The Merge Table method mentioned by BFree is a very good method to use and I think is the right approach not too mention very simple and elegant. Here are my notes why and the big one I am not sure if any one caught was the hits on the server for the query. The op stated in his comments to BFree that he would need to copy the table to do what he needed to, of course which table that was I am not sure , because his code:

foreach (DataRow row in data.Rows)

Those rows come from his table called data where is the copy required on that - he already has it.

Then here is something that just smacks right out on EACH Iteration of that loop :

SlowLoadingData slow_stuff = slow_query_results[(int)row["id"]];

Is the OP really querying the database each iteration of those rows (Which if it is a large table are we talking 100,000 rows +). Think of the load on the Server (his app too must generate this query request!), and also the amount of traffic it places on a network to do this! IF it is the only app maybe it's ok, but even at that it is not what I would prefer to do if I wanted to be efficient.

If gathering the data from the database in one query seems to much - then perhaps a better method would be to page his data in and do the merge.

SlowLoadingData Page1_SlowLoadingData = slow_query_results[Page1] as DataTable;
data.Merge(Page1_SlowLoadingData);

SlowLoadingData Page2_SlowLoadingData = slow_query_results[Page2] as DataTable;
data.Merge(Page2_SlowLoadingData);