Recipe 9.1 Filling a DataSet Asynchronously

Problem

Given some database queries that return large result sets and cause the calling application to be unresponsive, you need to make the application more responsive during the fill.

Solution

Create a background thread and use it to run a query to fill a DataSet. You can also fill a DataGrid object from a background thread.

The sample code contains one event handler and two methods:

Go Button.Click

Checks whether there is an existing background thread loading the DataSet. If the DataSet is not being loaded, a new thread is created invoking the AsyncFillDataSet( ) method to fill a DataSet. Otherwise, a message is displayed stating that the DataSet is currently being filled.

AsyncFillDataSet( )

This method loads a DataSet with the Orders and Order Details tables from the Northwind database. The BindDataSetToDataGrid( ) method is called asynchronously on the form's thread to display the results. Finally, messages are displayed to indicate that the AsyncFillDataSet( ) method has started and when it has completed.

BindDataSetToDataGrid( )

This method binds the default view of the Orders table in the DataSet to the data grid on the form.

The C# code is shown in Example 9-1.

Example 9-1. File: AsynchronousFillForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Threading;
using System.Runtime.Remoting.Messaging;
using System.Data;
using System.Data.SqlClient;

private delegate void BindDataSetToDataGridDelegate(DataSet ds);

// Table name constants
private const String ORDERS_TABLE       = "Orders";
private const String ORDERDETAILS_TABLE = "OrderDetails";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
    "Orders_OrderDetails_Relation";

// Field name constants
private const String ORDERID_FIELD      = "OrderID";
private const String ORDERDATE_FIELD    = "OrderDate";

private bool isWorking = false;

//  . . . 

private delegate void BindDataSetToDataGridDelegate(DataSet ds); 

private void goButton_Click(object sender, System.EventArgs e)
{
    // Check if the DataSet is already being filled.
    if (!isWorking)
    {
        isWorking = true;
        
        // Clear the data grid.
        resultDataGrid.DataSource = null;

        // Create and start a new thread to fill the DataSet.
        Thread thread = new Thread(new ThreadStart(AsyncFillDataSet));
        thread.Start( );
    }
    else
    {
        // DataSet already being filled. Display a message.
        statusTextBox.Text += "DataSet still filling  . . . " +
            Environment.NewLine;
    }
}

private void AsyncFillDataSet( )
{
    statusTextBox.Text = "Filling DataSet  . . . " + Environment.NewLine;

    DataSet ds = new DataSet("Source");
    
    SqlDataAdapter da;

    // Fill the Order table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable orderTable = new DataTable(ORDERS_TABLE);
    da.FillSchema(orderTable, SchemaType.Source);
    da.Fill(orderTable);
    ds.Tables.Add(orderTable);

    // Fill the OrderDetails table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable orderDetailTable = new DataTable(ORDERDETAILS_TABLE);
    da.FillSchema(orderDetailTable, SchemaType.Source);
    da.Fill(orderDetailTable);
    ds.Tables.Add(orderDetailTable);

    // Create a relation between the tables.
    ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
        ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
        ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
        true);

    statusTextBox.Text += "DataSet Fill complete." + Environment.NewLine;

    // Call the BindDataSetToDataGrid method asynchronously
    // on the Form's thread.
    this.BeginInvoke(
        new BindDataSetToDataGridDelegate(BindDataSetToDataGrid),
        new object[] {ds}); 

    // Set flag indicating that the async fill is complete.
    isWorking = false;
}

private void BindDataSetToDataGrid(DataSet ds)
{
    // Bind the default view of the Orders table to the data grid.
    resultDataGrid.DataSource = ds.Tables[ORDERS_TABLE].DefaultView;
}

Discussion

When a synchronous call is made, the caller thread is blocked until the call completes. An asynchronous call returns immediately, freeing the calling thread to continue with its work while a new thread is created to run the method in parallel.

A new instance of a Thread is initialized using a constructor that takes a ThreadStart delegate argument, which references the method to be executed when the Thread starts executing. The Start( ) method of the Thread changes the state of the Thread to ThreadState.Running allowing the operating system to schedule it for execution. Once it begins executing, the ThreadStart delegate supplied in the Thread constructor invokes its method.

Windows Form or control methods, such as a DataGrid, cannot be called on any thread other than the one that created the form or control because they are based on a single-threaded apartment (STA) model. Method calls from other threads must be marshaled to the creation thread. This can be done asynchronously by calling the BeginInvoke( ) method of the form, forcing the method to be executed on the thread that created the form or control.