UI will be blocked while using async and await in c#

Question

Here I need to export some data from a local server into Excel. I am using an async call for that method. But still, it is blocking the UI while exporting the data.

May I know the reason why the UI is being blocking?

Also, I need some clarification

1)await keyword is using to wait some time until the process is completed, then what is the difference b/w synchronous and asynchronous process.

2)If one method is declared as Async task, then all inner methods are performing as asynchronous?

3)While Executing inner methods(method1,method2,method3), method3 will be depends upon method1. So, put await keyword for method1. Am I right?

Code snippet:

private async void ConvertExcel(object sender)
{
    var excelEngine = new ExcelEngine();
    var application = excelEngine.Excel;
    var newbookWorkbook = application.Workbooks.Create(1);
    var work = newbookWorkbook.Worksheets[0];
    var openDialog = new SaveFileDialog
    {
        FilterIndex = 2,
        Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2013 Files(*.xlsx)|*.xlsx"
    };
    if (openDialog.ShowDialog() == true)
    {
        newbookWorkbook.Version = openDialog.FilterIndex == 1 ? ExcelVersion.Excel97to2003 : ExcelVersion.Excel2013;
    }
    else
        return;
    try
    {            
         // This method is used to get the data from server.          
         var table =  GetFullDataAsync();
         work.ImportDataTable(table, true, 1, 1, true);
         using (Stream stream = openDialog.OpenFile())
         {
            newbookWorkbook.SaveAs(stream);
         }

         newbookWorkbook.Close();
         excelEngine.Dispose();
    }
    catch (Exception exception)
    {
       Console.WriteLine("Exception message: {0}",ex.Message);
    }
}

internal async Task<DataTable> GetFullDataAsync()
{
    DataTable dataTable = new DataTable();
    dataTable = GetDataFromServer(DataEngine.Query,DataEngine.Connection);

    dataTable.Locale = CultureInfo.InvariantCulture;
    return dataTable;
}        


public DataTable GetDataFromServer(string query, DbConnection connection)
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    var command = connection.CreateCommand();
    command.CommandText = query; 
    command.Connection = connection;

    var reader = command.ExecuteReader();
    var dataTable = new DataTable
    {
        Locale = System.Globalization.CultureInfo.InvariantCulture
    };
    dataTable.Load(reader);
    return dataTable;
}

Answer 1

You can change your code to use async communication with Sql server like that:

private async Task ConvertExcel(object sender)
{
    var excelEngine = new ExcelEngine();
    var application = excelEngine.Excel;
    var newbookWorkbook = application.Workbooks.Create(1);
    var work = newbookWorkbook.Worksheets[0];
    var openDialog = new SaveFileDialog
    {
        FilterIndex = 2,
        Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2013 Files(*.xlsx)|*.xlsx"
    };
    if (openDialog.ShowDialog() == true)
    {
        newbookWorkbook.Version = openDialog.FilterIndex == 1 ? ExcelVersion.Excel97to2003 : ExcelVersion.Excel2013;
    }
    else
        return;
    try
    {            
         // This method is used to get the data from server.          
         var table = await GetFullDataAsync();
         work.ImportDataTable(table, true, 1, 1, true);

         var saveWorkbookTask = Task.Run(() => {
             using (Stream stream = openDialog.OpenFile())
             {
                newbookWorkbook.SaveAs(stream);
             }
         });

         await saveWorkbookTask;

         newbookWorkbook.Close();
         excelEngine.Dispose();
    }
    catch (Exception exception)
    {
       Console.WriteLine("Exception message: {0}",ex.Message);
    }
}

internal async Task<DataTable> GetFullDataAsync()
{
    DataTable dataTable = new DataTable();
    dataTable = await GetDataFromServer(DataEngine.Query,DataEngine.Connection);

    dataTable.Locale = CultureInfo.InvariantCulture;
    return dataTable;
}        


public async Task<DataTable> GetDataFromServer(string query, DbConnection connection)
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    var command = connection.CreateCommand();
    command.CommandText = query; 
    command.Connection = connection;

    var reader = await command.ExecuteReaderAsync();

    var loadTableTask = Task.Run(() => {
        var dataTable = new DataTable
        {
            Locale = System.Globalization.CultureInfo.InvariantCulture
        };
        dataTable.Load(reader);
        return dataTable;
    });

    return await loadTableTask;
}

But you still have blocking IO operations. So if your file is large than the UI will be blocked when the file will be written to disk.

Answer 2

1) await keyword is using for wait some time until the process is completed, then what is the difference b/w synchronous and asynchronous process.

synchronous: waiting blocks the thread, so locks the ui
asynchronous: waiting frees up the thread, so does not block the ui

2) If one method is declared as Async task, then all inner methods are performing as asynchronous?

Don't assume that is true. You probably want that to be the case, but it is up to you as the developer to code it that way.
The complier does not enforce this for you.

In your code above, the answer is no; your code is not asynchronous. Notice that you never use the await keyword.
You could need to access the data in an asynchronous way first (returning a Task), otherwise async/await keywords can't help you.

3) While Executing inner methods(method1,method2,method3), method3 will be depends upon method1. So, put await keyword for method1. Am i right?

Yes. If each method is returning a Task, then await them all. Typically when you starting using await in one place, it will bubble up through the rest of your code, so you keep adding more awaits as you have descibed.

Answer 3

have you tried

GetFullDataAsync().ContinueWith((table)=>{
      work.ImportDataTable(table, true, 1, 1, true);
         using (Stream stream = openDialog.OpenFile())
         {
            newbookWorkbook.SaveAs(stream);
         }

         newbookWorkbook.Close();
         excelEngine.Dispose();
})