Set DataGridView.DataSource async

Question

I must say I've been reading this site for a long time and always found what I was looking for. Sadly, today is not the case.

I have a .NET C# Winforms Application that uses MySQL .NET Connector to query a database and fill a DataGridView with the results.

Here is my code:

using (var SqlConn = new MySqlConnection(MyConnectionString)
{
    using (var SqlComm = new MySqlCommand("SELECT * FROM my_database.city", SqlConn)
    {
        using (var SqlAdapter = new MySqlDataAdapter(SqlComm)
        {
            DataTable SqlOutput = new DataTable();
            SqlAdapter.Fill(SqlOutput);
            myDataGridView.DataSource = SqlOutput;
        }
    }
}

The code is working fine. The problem is that, considering that the table has like 20K rows, it takes some time to load and in the meantime, it blocks UI.

Doing some tests, I concluded that the database query Fill() its very fast (it actually takes like 10ms). Therefore, setting DataGridView.DataSource property delays the whole operation.

So my question is, Is there a way to set DataSource propery async? Or maybe a way where I can still use the form while the data is loading?

I was also wondering if there is a better way of doing this, since I would be calling this method every time I do changes on the database table, so I could show the user updated information.

Thanks in advance.

20K is too much rows to show in `DataGriView`. It's better to load data with paging. Also to prevent UI freeze when loading data use async/await or load data in a different thread than UI thread. [Example: Load data asynchronously into my DataTable in Windows Forms](http://stackoverflow.com/a/38427392/3110834) — Reza Aghaei, Oct 04 '16 at 17:15
@RezaAghaei I do have an async/await method that implements MySqlDataAdapter.FillAsync method. But the data pull from the database is not actually freezing the UI, setting the DataSource property does. And I would be still doing that step synchronously. Could you tell me more about loading that with paging? — mariscal47, Oct 04 '16 at 17:24
So use `DataGridView` in [virtual mode](https://msdn.microsoft.com/en-us/library/15a31akc(v=vs.110).aspx) or load data paged. — Reza Aghaei, Oct 04 '16 at 17:25

score 0 · Accepted Answer · answered Oct 04 '16 at 17:40

Documentation suggests to go with "virualmode" property setting.

https://msdn.microsoft.com/en-us/library/15a31akc.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

There is other interesting topic "Just in time loading". Implementing Virtual Mode with Just-In-Time Data Loading in the Windows Forms DataGridView Control

https://msdn.microsoft.com/en-us/library/ms171624(v=vs.110).aspx

You have good examples in above link. Worth looking at them.

score -1 · Answer 2 · answered Oct 05 '16 at 14:59

Use Thread. Fill your DataGridView Programmatically rather than using DataSource.

 public void LoadDataGrid(DataTable d, DataGridView dg )
    {

        if (dg.InvokeRequired)
        {
            dg.BeginInvoke((MethodInvoker)delegate()
            {
                dg.Rows.Clear();
                dg.ColumnCount = 7;
                dg.Columns[0].Name = "Order No.";
                dg.Columns[0].Width = 110;
                dg.Columns[1].Name = "Order Date";
                dg.Columns[1].Width = 100;
                dg.Columns[2].Name = "Excepted rcv date";
                dg.Columns[2].Width = 100;
                dg.Columns[3].Name = "Supplier";
                dg.Columns[3].Width = 150;
                dg.Columns[4].Name = "Total Items";
                dg.Columns[4].Width = 80;
                dg.Columns[5].Name = "Total";
                dg.Columns[5].Width = 80;
                dg.Columns[6].Name = "Status";
                dg.Columns[6].Width = 100;


            });
            foreach (DataRow row in d.Rows)
            {

                if (dg.InvokeRequired)
                {
                    dg.BeginInvoke((MethodInvoker)delegate() { dg.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString()); });
                }


                    Thread.Sleep(100);
                }
            }

    }

Set DataGridView.DataSource async

2 Answers2

Linked