How can I best implement async into my WPF application?

I’m working on a WPF application that runs SQL queries and returns a data grid of the results when pressed. Currently, the structure of my methods as I try to figure out async is as follows:

public static async Task GenerateImportDataGrid( TextBox TBImportData )
{
    using( SqlConnection conn = new SqlConnection( sqlConnStr ) )
    {
        SqlTransaction transaction = conn.BeginTransaction( "InsertFromFile" );
        using( var package = new ExcelPackage( new FileInfo( TBImportData.Text ) ) )
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
                int rows = worksheet.Dimension.Rows - 1;
                int columns = worksheet.Dimension.Columns - 1;
                int rowOffset = 4; //offset due to headers

                for( int i = 1; i <= rows - rowOffset; i++ ) //loop through each row
                {
                    SqlCommand insert = new SqlCommand(
                        string.Format(
                            "INSERT INTO ##COA ( mailPreparerName, barcodeEdoc, errorType, errorCode, errorDescription, errorData, jobID, mailClassEdoc, processingCategoryEdoc, mailOwnerName, scanTime) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}')",
                            worksheet.Cells[ i + rowOffset, 2 ].Value, worksheet.Cells[ i + rowOffset, 4 ].Value,
                                worksheet.Cells[ i + rowOffset, 8 ].Value, worksheet.Cells[ i + rowOffset, 9 ].Value,
                                worksheet.Cells[ i + rowOffset, 10 ].Value, worksheet.Cells[ i + rowOffset, 11 ].Value,
                                worksheet.Cells[ i + rowOffset, 13 ].Value, worksheet.Cells[ i + rowOffset, 18 ].Value,
                                worksheet.Cells[ i + rowOffset, 19 ].Value, worksheet.Cells[ i + rowOffset, 34 ].Value,
                                worksheet.Cells[ i + rowOffset, 39 ].Value ),
                            conn, transaction );
                    insert.ExecuteNonQuery();
                }
                transaction.Commit();
            }
                    
            SqlCommand cmd = new SqlCommand( "EXEC Export", conn );
            SqlDataReader reader = await cmd.ExecuteReaderAsync();
    
            DataTable resultSet = new DataTable();
            await Task.Run( () => resultSet.Load( reader ) );
    
            DataDisplay results = new DataDisplay( resultSet ); //DataDisplay is just a window containing a DataGrid
            results.Show();
    }
}

private async void Run()
{
    ...
    PB_ProcessProgress.IsIndeterminate = true;
    await Reporting.GenerateImportDataGrid( TBImportData );
}

private void BtnView_Click( object sender, RoutedEventArgs e )
{
      ...
      Run();
}

Depending on the query ran, my application can get for anywhere between 30 seconds to 20 minutes. I have a progressbar on my application that I would really like to make use of while the numbers are being crunched, but I am having a hard time figuring out how to best implement async and await, whether it’s in my function or at the point that it is being called.

I would really appreciate any help!

Answer

ADO.NET has async equivalents of Commit and ExecuteReaderAsync.

If you make your method async you can use and await these:

public static async Task GenerateImportDataGrid(TextBox TBImportData)
{
    using (SqlConnection conn = new SqlConnection(sqlConnStr))
    using (SqlTransaction transaction = conn.BeginTransaction("InsertFromFile");
    {
        //bunch of inserts are executed as part of the transaction
        await transaction.CommitAsync();
            
        using (SqlCommand cmd = new SqlCommand("EXEC Export", conn))
        {
            SqlDataReader reader = await cmd.ExecuteReaderAsync();
        }

        DataTable resultSet = new DataTable();
        resultSet.Load(reader);

        DataDisplay results = new DataDisplay(resultSet); //DataDisplay is just a window containing a DataGrid
        results.Show();
    }
}

Doing so will release your UI thread whilst the asynchronous work happens, keeping your application responsive.

---

If you find that resultSet.Load(reader) is also expensive, this may also lock up your application.

As this is CPU-bound work, you will have to mitigate this by running on the thread pool:

await Task.Run(() => resultSet.Load(reader));

Which will also free-up the UI thread.