Aysnc method still blocking the ui interface

Question

I have this method which is using ODBC and executes the reader async but the method is still blocking my UI for some reason it's loading 4000 records but I am wondering if someone can look at my code see where I am going wrong.

async Task<BindingList<PurchaseLinkHeaderC>> GetPurchaseOrders( IProgress<int> progress)
{
    BindingList<PurchaseLinkHeaderC> _purhcaseOrderList = new BindingList<PurchaseLinkHeaderC>();
    try
    {
        string sageDsn = ConfigurationManager.AppSettings["SageDSN"];
        string sageUsername = ConfigurationManager.AppSettings["SageUsername"];
        string sagePassword = ConfigurationManager.AppSettings["SagePassword"];
        //using (var connection = new OdbcConnection("DSN=SageLine50v24;Uid=Manager;Pwd=;"))

        using (var connection =
            new OdbcConnection(String.Format("DSN={0};Uid={1};Pwd={2};", sageDsn, sageUsername, sagePassword)))
        {
            connection.Open();
            string fromD = dtpFrom.Value.ToString("yyyy-MM-dd");
            string toD = dtpTo.Value.ToString("yyyy-MM-dd");

            string SQL =
                "SELECT 'ORDER_NUMBER', 'ORDER_OR_QUOTE', 'ORDER_DATE', 'DELIVERY_DATE', 'ORDER_STATUS_CODE', 'ORDER_STATUS', 'DELIVERY_STATUS_CODE', 'DELIVERY_STATUS', 'ACCOUNT_REF', 'NAME', 'ADDRESS_1', 'ADDRESS_2', 'ADDRESS_3', 'ADDRESS_4', 'ADDRESS_5', 'C_ADDRESS_1', 'C_ADDRESS_2', 'C_ADDRESS_3', 'C_ADDRESS_4', 'C_ADDRESS_5', 'DEL_NAME', 'DEL_ADDRESS_1', 'DEL_ADDRESS_2', 'DEL_ADDRESS_3', 'DEL_ADDRESS_4', 'DEL_ADDRESS_5', 'VAT_REG_NUMBER', 'REFERENCE', 'CONTACT_NAME', 'TAKEN_BY', 'SUPP_ORDER_NUMBER', 'SUPP_TEL_NUMBER', 'NOTES_1', 'NOTES_2', 'NOTES_3', 'SUPP_DISC_RATE', 'FOREIGN_ITEMS_NET', 'FOREIGN_ITEMS_TAX', 'FOREIGN_ITEMS_GROSS', 'ITEMS_NET', 'ITEMS_TAX', 'ITEMS_GROSS', 'TAX_RATE_1', 'TAX_RATE_2', 'TAX_RATE_3', 'TAX_RATE_4', 'TAX_RATE_5', 'NET_AMOUNT_1', 'NET_AMOUNT_2', 'NET_AMOUNT_3', 'NET_AMOUNT_4', 'NET_AMOUNT_5', 'TAX_AMOUNT_1', 'TAX_AMOUNT_2', 'TAX_AMOUNT_3', 'TAX_AMOUNT_4', 'TAX_AMOUNT_5', 'COURIER_NUMBER', 'COURIER_NAME', 'CONSIGNMENT', 'CARR_NOM_CODE', 'CARR_TAX_CODE', 'CARR_DEPT_NUMBER', 'CARR_DEPT_NAME', 'FOREIGN_CARR_NET', 'FOREIGN_CARR_TAX', 'FOREIGN_CARR_GROSS', 'CARR_NET', 'CARR_TAX', 'CARR_GROSS', 'FOREIGN_INVOICE_NET', 'FOREIGN_INVOICE_TAX', 'FOREIGN_INVOICE_GROSS', 'INVOICE_NET', 'INVOICE_TAX', 'INVOICE_GROSS', 'CURRENCY', 'CURRENCY_TYPE', 'EURO_GROSS', 'EURO_RATE', 'FOREIGN_RATE', 'SETTLEMENT_DUE_DAYS', 'SETTLEMENT_DISC_RATE', 'FOREIGN_SETTLEMENT_DISC_AMOUNT', 'FOREIGN_SETTLEMENT_TOTAL', 'SETTLEMENT_DISC_AMOUNT', 'SETTLEMENT_TOTAL', 'PAYMENT_REF', 'PRINTED', 'PRINTED_CODE', 'POSTED', 'POSTED_CODE', 'QUOTE_STATUS_ID', 'RECURRING_REF', 'DUNS_NUMBER', 'PAYMENT_TYPE', 'BANK_REF', 'GDN_NUMBER', 'PROJECT_ID', 'ANALYSIS_1', 'ANALYSIS_2', 'ANALYSIS_3', 'INVOICE_PAYMENT_ID', 'RESUBMIT_INVOICE_PAYMENT_REQUIRED', 'RECORD_CREATE_DATE', 'RECORD_MODIFY_DATE', 'RECORD_DELETED' FROM 'PURCHASE_ORDER' WHERE ORDER_DATE >='{0}' and ORDER_DATE <='{1}'";

            int counter = 0;
            using (var command = new OdbcCommand(string.Format(SQL, fromD, toD), connection))
            {
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var purhcaseOrders = new PurchaseLinkHeaderC();
                        if ((reader["ORDER_NUMBER"] != ""))
                        {
                            counter++;

                            string orderNumber = Convert.ToString(reader["ORDER_NUMBER"]);
                            purhcaseOrders.Order_Number = OrderNumber.ToString();
                            purhcaseOrders.PurchaseOrderNo = Convert.ToInt32(reader["ORDER_NUMBER"]);
                            purhcaseOrders.Name = reader["NAME"].ToString();
                            purhcaseOrders.Selected_PurchaseOrder = false;
                            _purhcaseOrderList.Add(purhcaseOrders);
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        var logger = NLog.LogManager.GetCurrentClassLogger();
        logger.Info(ex, "Error at GetSalesOrders " + ex.ToString());
    }

    return _purhcaseOrderList;
}

I want to be able to show a progress bar of the list being loaded so I was attempting to call it as this way. But I am not sure how to attach the progress method either.

var progressIndicator = new Progress<int>(ReportProgress);
//call async method
BindingList<PurchaseLinkHeaderC> purchaseOrders = await GetPurchaseOrders(progressIndicator);
_masterPurchaseOrders = purchaseOrders;

I am hoping that someone can help here.

Answer 1

When you await, the default behaviour is to use the sync-context, if one, when coming back from the async operation. In the case of a UI application, the sync-context is: the UI.

So; right now, there are a lot of things that come back to the UI. This is useful when context matters, but in your case: it doesn't - since you are simply returning a list.

This means that you should be able to add .ConfigureAwait(false) to a lot of those await expressions - for example:

while (await reader.ReadAsync().ConfigureAwait(false))

This disconnects the sync-context behaviour, and may improve what you are seeing. You would ideally to add that to all of the await calls in the utility method (GetPurchaseOrders).

You may also wish to look for any missing async operations - for example, the connection.Open(); could be a await connection.OpenAsync().ConfigureAwait(false);

Note that the calling code should not use ConfigureAwait(false) - since the binding-list touches the UI, it needs the sync-context. So: don't add ConfigureAwait(false) to the await GetPurchaseOrders(...) call.

---

There is also one other possibility: you say that you are using ODBC and "sage". It is entirely possible that the ODBC/sage API doesn't support await, and it is being implemented as "sync over async". If this is the case, it gets tricky. You might need to use a thread instead of async/await in that case - perhaps via ThreadPool.QueueUserWorkItem. There are ways to invoke async code on worker threads, but if the "async" code is actually "sync code that pretends to be async", there's not really any point, and you might as well do it "the old way". This usually means:

• start a worker (ThreadPool)
• do some work on the worker (your existing code, but perhaps using the non-async implementation)
• at the end of the worker, use Control.Invoke to push the work back to the UI thread for the final "update the UI" step