c# - Update datagrid row by row from a big data table (Progress database), using a task or thread, but keeping the UI responsive during the update

Question

There is a datatable containing 40-50.000 records in it. I'm connecting to it via ODBC because that's the only way I'm allowed to. The code below is a little project in which I want to test out a solution for viewing the records row by row, but in a way that the UI isn't frozen during the query. The code now works, more or less. The program opens, and when I click on the button, the UI freezes and at every record, updates itself, so that the user can see the articles. Though, they cannot controls the UI until the query is finished. Can somebody help to solve it? I had a hard time reading documentations and listening to videos about tasks.

using System;
using System.Data.Odbc;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AsyncSQLtest
{
    public partial class MainFrom : Form
    {
        DataPresenter presenter;

        public MainFrom()
        {
            InitializeComponent();
            presenter = new DataPresenter(this);
        }

        private async void LoadData(object sender, EventArgs e)
             => await presenter.QueryDataAsync(this.dataGridView1);
    }

    internal class DataPresenter
    {
        private MainFrom mainFrom;

        public DataPresenter(MainFrom mainFrom)
               => this.mainFrom = mainFrom;

        internal async Task QueryDataAsync(DataGridView dgv)
        {
            OdbcConnection con = new OdbcConnection("DSN=PA;UID=XXX;PWD=YYYYYYYYY");
            OdbcCommand cmd = new OdbcCommand("select Artikel from Table", con);

            dgv.Columns.Clear();
            dgv.Columns.Add("artikel", "artikel");

            try
            {
                con.Open();
                var reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    dgv.Invoke(new Action(
                        () =>
                        {
                            dgv.Rows.Add(reader.GetString(0));
                            dgv.Refresh();
                        }
                        ));
                }

                reader.Close();
                con.Close();
                cmd.Dispose();
            }
            catch (Exception e)
            {
                reader.Close();
                con.Close();
                if (cmd != null) cmd.Dispose();
                MessageBox.Show(e.Message);
            }
        }
    }
}

question from:https://stackoverflow.com/questions/65851090/update-datagrid-row-by-row-from-a-big-data-table-progress-database-using-a-ta

Answer 1

The ExecuteReaderAsync of the OdbcCommand class most probably has the default synchronous implementation provided by the DbCommand base class. This means that also the await reader.ReadAsync() is most probably 100% synchronous. You could fix this problem by offloading the synchronous call to a background thread, using the Task.Run method:

var reader = await Task.Run(() => cmd.ExecuteReader());
while (await Task.Run(() => reader.Read()))
{

The dgv.Invoke(new Action( is probably redundant, because the QueryDataAsync is called from the UI thread.

Mixing data retrieval code with UI manipulation code is probably not a good idea, because it results to tight coupling between the application layers. If you are using C# 8 or later you could consider exposing the data in the form of an asynchronous stream (IAsyncEnumerable<string>):

internal async IAsyncEnumerable<string> QueryDataAsync()
{
    //...
    con.Open();
    var reader = await Task.Run(() => cmd.ExecuteReader());
    while (await Task.Run(() => reader.Read()))
    {
        yield return reader.GetString(0);
    }
    //...
}

...and consume it from the presentation layer like this:

private async void LoadData(object sender, EventArgs e)
{
    await foreach (string value in presenter.QueryDataAsync())
    {
        dgv.Rows.Add(value);
    }
}

It would still be tricky because of the compile error:

Error CS1626 Cannot yield a value in the body of a try block with a catch clause

You would probably have to replace the catch with a finally, and have the consumer handle the errors.