C# 优化对绑定到 DataGridView 的 DataTable 的更新

optimize updates to DataTable bound to DataGridView

提问by Will Rogers

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:

我的应用程序中有一个显示一些数据的表单。当我第一次显示 Form 时，我将一些数据加载到 DataTable 中，然后将 DataTable 绑定到 DataGridView。我还启动了一个异步方法来执行一些较慢的数据库查询。当这些慢查询完成时，我需要更新 DataTable 中的几百行，填充从慢查询返回的值，如下所示：

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.

这非常慢，将 UI 线程挂起一分钟或更长时间，大概是因为每一行都在触发重绘。

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:

经过一番研究，我找到了一种快速的方法。首先，将 DataGridView 绑定到绑定到 DataTable 的 BindingSource，而不是直接绑定到 DataTable。然后，在对 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.

但是，有一个问题，而且很棘手：上面的代码阻止 DataGridView 检测添加到 DataTable 的新行。添加到表中的任何新行都不会出现在网格中。如果您使用箭头键将当前单元格选择移出网格的底端，网格也可能会引发异常，因为基础数据源有更多行但网格尚未创建网格行来显示它们。

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 removedrows from the DataTable!)

1. 在对底层 DataTable 进行更改时，是否有更好的方法来抑制绑定更新？

2. 是否有一种简单的方法可以告诉 DataGridView 优雅地刷新其网格行集合以匹配基础 DataTable 行的数量？（注意：我已经尝试调用 BindingSource.ResetBindings，但如果您从 DataTable 中删除了行，它似乎会触发更多异常！）

采纳答案by Henk Holterman

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.

您是否考虑过在填充表时断开 dataGrid 或 bindingSource 并在之后重新连接？它可能看起来有点难看，但它应该快得多。

回答by BFree

You can try using the Merge methodon 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.

您可以尝试在 DataTable 上使用Merge 方法。我将尝试创建一个简单的演示应用程序并将其张贴在这里，但想法很简单。当要更新Grid时，将结果查询到一个新的DataTable中，然后将旧表与新表合并。只要两个表都有主键（如果它们没有从数据库返回，您可以在内存中创建它们），那么它应该跟踪更改并无缝更新 DataGridView。它还具有不丢失用户在网格上的位置的优点。

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.

好的，这是一个示例。我创建了一个带有两个按钮和一个 dataGridView 的表单。单击 button1 时，我用一些数据填充主表，并将网格绑定到它。然后，在第二次单击时，我创建了另一个具有相同架构的表。向其中添加数据（有些具有相同的主键，有些具有新的主键）。然后，他们将它们合并回原始表。它按预期更新网格。

    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;

        }
    }

回答by Robert Rossney

If you're using a BindingSourcefor complex data binding, it's important to understand that SuspendBindingand ResumeBindingonly 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.)

如果您使用的BindingSource是复杂数据绑定，那么理解这一点很重要，SuspendBinding并且ResumeBinding只暂停和恢复当前项目的绑定。这使您可以禁用当前项的绑定并更改其一系列属性，而无需将对该属性的任何单独更改推送到绑定控件。（这在 的文档中没有解释BindingSource，它会很有用，哦不：它在 的文档中CurrencyManager。）

Any changes you make to the other items in the list, i.e. everything except the current item, raise the ListChangedevent. 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 ListChangedevents off, the BindingSourcedoesn't tell the DataGridViewabout them, and so the DataGridViewremains empty.

您对列表中的其他项目所做的任何更改，即除当前项目之外的所有项目，都会引发该ListChanged事件。如果禁用这些事件，则 BindingSource 将停止将列表更改告知绑定控件，直到您重新启用它们。这具有您所看到的结果：您将所有行添加到基础DataTable，但由于您已关闭ListChanged事件，因此BindingSource不会告诉DataGridView有关它们的信息，因此DataGridView仍然为空。

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

正确的解决方案是调用ResetBindings，这会强制BindingSource使用其绑定列表中的当前值刷新所有绑定到它的控件。

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.

你打电话后会得到什么样的例外ResetBindings？因为它对我来说效果很好，无论是在底层DataTable.

回答by dcposch

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

干杯--DC

回答by delly47

I found that using resetBindings seems to move the scrollbar and the user is left thinking what did i do? I found that using a bindingList as a datasource, with an object that uses INotifyPropertyChanged, and then when I edited the row, (bound to an object). the row wasnt being updated until a click or selection change on the form.

我发现使用 resetBindings 似乎移动了滚动条，而用户却在想我做了什么？我发现使用 bindingList 作为数据源，使用 INotifyPropertyChanged 的​​对象，然后当我编辑行时（绑定到对象）。在表单上单击或选择更改之前，该行不会更新。

but calling dgv.Refresh() seemed to solve the problem, without the scroll change.

但调用 dgv.Refresh() 似乎解决了问题，无需滚动更改。

回答by Loathing

I find the solution by Ravi LVS on codeproject works well:

我发现 Ravi LVS 在 codeproject 上的解决方案效果很好：

BindingSource bs = new BindingSource();
DataTable dt = new DataTable();

bs.DataSource = dt;
bs.SuspendBinding();
bs.RaiseListChangedEvents = false; 
bs.Filter = "1=0"; 
dt.BeginLoadData(); 

//== some modification on data table

dt.EndLoadData();
bs.RaiseListChangedEvents = true;
bs.Filter = "";

Link to original page: http://www.codeproject.com/Tips/55730/Achieve-performance-while-updating-a-datatable-bou

链接到原始页面：http: //www.codeproject.com/Tips/55730/Achieve-performance-while-updating-a-datatable-bou

回答by Stix

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:

只是将其作为解决方案发布：已经对评论和帖子进行了一些注释。BFree 提到的 Merge Table 方法是一个非常好的使用方法，我认为是正确的方法，不用说非常简单和优雅。这是我的笔记，为什么以及我不确定是否有人抓住了查询的服务器上的命中。操作员在他对 BFree 的评论中表示，他需要复制表来做他需要做的事情，当然我不确定是哪个表，因为他的代码：

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.

这些行来自他的名为 data 的表，其中需要副本 - 他已经拥有它。

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.

OP 是否真的在这些行的每次迭代中查询数据库（如果它是一个大表，我们说的是 100,000 行 +）。想想服务器上的负载（他的应用程序也必须生成此查询请求！），以及它在网络上放置的流量来执行此操作！如果它是唯一的应用程序，也许还可以，但即使如此，如果我想提高效率，我也不愿意这样做。

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