How can I best implement async into my WPF application?

Question

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!

Replace cmd.ExecuteReader() by await cmd.ExecuteReaderAsyn(). Declare your method as async Task instead of void, and call (and await) it in an async void Click handler method. — Clemens
– Clemens, Commented Jul 3, 2020 at 12:55
Not what you were asking but I recommend taking a look at dapper. It provides extension methods on ado make it easier to move from datatables and datasets to lists of dto. — Andy
– Andy, Commented Jul 3, 2020 at 12:59
@Clemens thank you for your response! I tried to implement your suggestions as well as Johnathan Barclay's suggestion to await Task.Run(() => resultSet.Load(reader));, but the app still locks up :( can you check my new edit and help me out again? — sirius_pain
– sirius_pain, Commented Jul 3, 2020 at 13:29
@sirius_pain What are the bunch of inserts? Can you add that code? — Johnathan Barclay
– Johnathan Barclay, Commented Jul 3, 2020 at 13:32
Your still using some blocking calls ExecuteNonQuery and Commit. Also, you should be parameterising your SQL statements rather than using string.Join which is prone to SQL injection. — Johnathan Barclay
– Johnathan Barclay, Commented Jul 3, 2020 at 14:49

Johnathan Barclay · Accepted Answer · 2020-07-03 13:03:41Z

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.

Collectives™ on Stack Overflow

How can I best implement async into my WPF application?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related