Returning from a task without blocking UI thread

Question

I have a method that returns a datatable. I need for all the sql stuff to run in a thread and then be able to pass back a datatable without it blocking the UI thread. From my understanding, when you call Task.Result it blocks the UI thread until the task has completed. How would I get around this. I read about using await and async but I haven't quite figured out how to use that with the task yet.

public static DataTable LaunchLocationMasterListReport(ObservableCollection<string> BuiltConditionsList, ObservableCollection<string> BuiltSortList, ObservableCollection<ListBoxCheckBoxItemModel> ColumnsForReport,
    bool LocationNotesCheckBox, ref string reportQuery, ref string reportQueryforSave, ref string reportView, ref string queryCondtions)
{
    queryCondtions = BuildConditionAndSorts(queryCondtions, BuiltConditionsList, BuiltSortList);
    reportQueryforSave = "SELECT * FROM LocationMasterReportView";
    reportView = "LocationMasterReportView";
    reportQuery = "SELECT * FROM LocationMasterReportView " + queryCondtions;

    return LaunchReport(reportQuery, ColumnsForReport).Result;
}

async private static Task<DataTable> LaunchReport(string reportQuery, ObservableCollection<ListBoxCheckBoxItemModel> ColumnsForReport)
{
    SqlConnection myConn = new SqlConnection(Settings.Default.UltrapartnerDBConnectionString);
    DataTable dt = new DataTable();

    string rq = reportQuery;

    Task<DataTable> task = Task.Factory.StartNew(() =>
    {
        using (SqlCommand comm = new SqlCommand(rq, myConn))
        {
            myConn.Open();
            dt.Load(comm.ExecuteReader());
            myConn.Close();
        }

        if (dt.Rows.Count == 0)
        {
            MessageBox.Show("Contains No Results");
            return null;
        }

        foreach (ListBoxCheckBoxItemModel lbc in ColumnsForReport)
        {
            if (!lbc.IsSelected)
            {
                dt.Columns.Remove(lbc.Name.ToString());
            }
        }

        return dt;

    }, CancellationToken.None, TaskCreationOptions.LongRunning, TaskScheduler.Default);

    return await task;
}

Answer 1

I agree that using async/await is the best approach here. As noted, when you await an async method, then even though the declared return type is a Task<T>, the compiler translates this into an implicit return type of T.

The gotcha is that all async methods must return void, Task, or Task<T>. So once you start using them, you have to "bubble up" the "async" method attribute until you get to a point where you can either block on the result or your method can be void or Task (i.e. you've consumed the actual result).

Please see this simple UI-based example:

/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();
    }

    private async void Button_Click(object sender, RoutedEventArgs e)
    {
        statusText.Text = "Running";
        statusText.Text = await _ComputeText(true);
        statusText.Text = await _ComputeText(false);
    }

    private static async Task<string> _ComputeText(bool initialTask)
    {
        string result = await Task.Run(() =>
            {
                Thread.Sleep(2000);
                return initialTask ? "Task is done!" : "Idle";
            });

        return result;
    }
}

Note that the button event handler, "Button_Click", is simply declared as "void" return. But I can do that because I consume the asynchronous result in that method.

In your case, the returned DataTable is not available until the asynchronous task has completed. So you have to declare each method as "async" all the way back to whatever method is actually doing to do something with the DataTable. Even there, the method will need to be declared as async, but you won't be returning the DataTable, and so that method can have a return type of "void" or "Task". A common scenario is for this method to be a UI event handler, so "void" should be fine there (and will be required for use in an event handler delegate); your code isn't calling it anyway. But it's technically more correct to use "Task" instead, so if in your context that works, you should do it that way instead.

Without a concise-but-complete example, it's hard to offer anything more specific than that.