BackgroundWorker to read database and update GUI

Question

I am trying to keep my indeterminate ProgressBar animation running smoothly as I query database and updating DataGrid in DoWork in BackgroundWorker. I understand that I cannot update UIThread in DoWork, so I use Dispatcher.BeginInvoke to access DataGrid, yet I still receive the owned-by-another-thread exception.

Question 1

Why is this happening? DoWork should be on BackgroundWorker's thread owned by UIThread, and Dispatcher should allow my DoWork to access my GUI.

Looking further into BackgroundWorker suggest UI operation should be handled in ProgressChanged(). But if I move all database/GUI operation to ProgressChanged() and put a Thread.Sleep(5000) on DoWork() to emulate something to work on to lend enough time for ProgressChanged() to run, the GUI is not being updated, although the ProgressBar continue its indeterminate smooth animation.

Question 2

I called a Thread.Sleep(5000) on BackgroundWorker thread. ProgressChanged() should spend 5 seconds querying database and updating GUI. Why not?

XAML:

<DataGrid x:Name="myDataGrid" ScrollViewer.CanContentScroll="True" 
          EnableRowVirtualization="True" EnableColumnVirtualization="True"
          VirtualizingPanel.IsContainerVirtualizable="True" 
          VirtualizingPanel.IsVirtualizing="True"
          VirtualizingPanel.IsVirtualizingWhenGrouping="True"
          Height="500" MaxHeight="500" />

<ProgressBar x:Name="myProgressBar" Height="20" Width="400"
             IsIndeterminate="True" Visibility="Visible" />

<Button x:Name="mySearch" Click="btnSearch_Click">Search</Button>

C#

private BackgroundWorker bgw = new BackgroundWorker();

private void btnSearch_Click(object sendoer, RoutedEventArgs e)
{
    bgw.WorkerReportsProgress = true;
    bgw.ProgressChanged += ProgressChanged;
    bgw.DoWork += DoWork;
    bgw.RunWorkerCompleted += BGW_RunWorkerCompleted;

    bgw.RunWorkerAsync();
}

private void DoWork(object sender, DoWorkEventArgs e)
{
    // Thread.Sleep(5000);
    using (SqlConnection conn = new SqlConnection(connStr))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM " + MyTableName, conn))
        {
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    Dispatcher.BeginInvoke(new Action(() =>
                    {
                        myDataGrid.Items.Add(new
                        {
                            Id = rdr.GetInt32(0),
                            Name = rdr.GetString(1).ToString(),
                        });
                    }));
                }
            }
        }
    }
}

private void ProgressChanged(object sender, ProgressChangedEventArgs e)
{
   // Paste code from DoWork and uncomment Thread.Sleep(5000)
}

private void BackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
}

Answer 1

Create a collection of items in your DoWork event handler and then set the ItemsSource property of the DataGrid to this one in the RunWorkerCompleted event handler once you are done fetching the data on a background thread:

private void btnSearch_Click(object sendoer, RoutedEventArgs e)
{
    bgw.WorkerReportsProgress = true;
    bgw.ProgressChanged += ProgressChanged;
    bgw.DoWork += DoWork;
    bgw.RunWorkerCompleted += BGW_RunWorkerCompleted;

    myProgressBar.Visibility = Visibility.Visible;
    bgw.RunWorkerAsync();
}

private void DoWork(object sender, DoWorkEventArgs e)
{
    List<object> results = new List<object>();
    using (SqlConnection conn = new SqlConnection(connStr))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM " + MyTableName, conn))
        {
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    results.Add(new
                    {
                        Id = rdr.GetInt32(0),
                        Name = rdr.GetString(1).ToString(),
                    });
                }
            }
        }
    }

    e.Result = results;
}

private void BackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    myDataGrid.ItemsSource = e.Result as List<object>;
    myProgressBar.Visibility = Visibility.Collapsed;
}

You need to call the ReportProgress method of the BackgroundWorker for any progress to be reported but it is pretty meaningless to report progress from a database retrieval operation since you have no clue about the actual progress anyway. You'd better just use an indeterminate ProgressBar and show it when you start the operation and hiding it when the operation has completed as demonstrated in the sample code above.

And Thread.Sleep will block the current thread. If you block the UI thread, your UI, including your ProgressBar, cannot be updated so you don't want to do this.