This step-by-step article shows you how to query a database on a background thread and use databinding to display the results in a DataGrid object.

When large queries to a database are executed, the application may become unresponsive for a long period of time. To avoid this behavior and decrease the waiting time of the user, the query can be executed on a background thread, releasing the application for other tasks until the data is returned from the database and databinding is performed.

Back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

Back to the top

Background

By design, Windows Form or Control methods cannot be called on a thread other than the one that created the form or control. If you attempt to do this, an exception is thrown. Depending on the exception handling implemented in your code, this exception may cause your application to terminate. If no exception handling is implemented, the following error message is displayed:

The exception is raised because Windows Forms are based on a single-threaded apartment (STA) model. Windows Forms can be created on any thread; after they are created, however, they cannot be switched to a different thread. In addition, the Windows Form methods cannot be accessed on another thread; this means that all method calls must be executed on the thread that created the form or control.

Method calls that originate outside the creation thread must be marshaled (executed) on the creation thread. To do this asynchronously, the form has a BeginInvoke method that forces the method to be executed on the thread that created the form or control. The synchronous method call is done with a call to the Invoke method.

Back to the top

Build the Windows Forms application

This section describes how to create a Windows Form application that queries a database on a background thread and uses the BeginInvoke method to perform databinding on a DataGrid.

   using System.Threading;
            using System.Data.SqlClient;

   static Form1 MyForm;
            Thread UpdateThread;
            ThreadStart UpdateThreadStart = new ThreadStart(QueryDataBase);
            static MethodInvoker CallDataBindToDataGrid = new MethodInvoker(DataBindToDataGrid);
            static DataSet MyDataSet;
            static SqlDataAdapter MyDataAdapter;
            static string MyQueryString = "SELECT [Order Details].*, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate FROM [Order Details] CROSS JOIN Orders";
            static SqlConnection MyConnection = new SqlConnection("data source=localhost;initial catalog=Northwind;integrated security=SSPI;");

   UpdateThread = new Thread(UpdateThreadStart);
            UpdateThread.Name = "Update Thread";
            UpdateThread.IsBackground = true;
            UpdateThread.Start();

   QueryDataBase();

   // Sub routine that is to be executed on Form's thread.
            static void DataBindToDataGrid()
            {
            MyForm.dataGrid1.DataSource = MyDataSet;
            MyForm.dataGrid1.DataMember = "MyTable";
            MyDataSet = null;
            MyDataAdapter = null;
            }
            // Sub routine used by the background thread to query database.
            static void QueryDataBase()
            {
            MyDataSet = new DataSet();
            MyConnection.Open();
            MyDataAdapter = new SqlDataAdapter(MyQueryString, MyConnection);
            MyForm.label1.Text = "Filling the DataSet";
            MyDataAdapter.Fill(MyDataSet, "MyTable");
            MyConnection.Close();
            MyForm.label1.Text = "DataSet Filled";
            MyForm.BeginInvoke(CallDataBindToDataGrid);
            }

   MyForm = new Form1();
            Application.Run(MyForm);

Back to the top

Demonstration

To see the benefit that is gained by using a background thread to query the database, follow these steps:

Back to the top

Complete code Listing

   using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Threading;
using System.Data.SqlClient;
namespace DataGridThread
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.TextBox textBox1;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button1 = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.button2 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(8, 8);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(736, 208);
this.dataGrid1.TabIndex = 0;
//
// button1
//
this.button1.Location = new System.Drawing.Point(16, 232);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(120, 23);
this.button1.TabIndex = 1;
this.button1.Text = "Query on Thread";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// label1
//
this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 15F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.label1.Location = new System.Drawing.Point(16, 276);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(720, 24);
this.label1.TabIndex = 2;
//
// button2
//
this.button2.Location = new System.Drawing.Point(144, 232);
this.button2.Name = "button2";
this.button2.Size = new System.Drawing.Size(112, 23);
this.button2.TabIndex = 3;
this.button2.Text = "Query on Form";
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(268, 232);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(472, 20);
this.textBox1.TabIndex = 4;
this.textBox1.Text = "";
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(752, 330);
this.Controls.AddRange(new System.Windows.Forms.Control[] { this.textBox1, this.button2, this.label1, this.button1, this.dataGrid1});
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
///
static Form1 MyForm;
Thread UpdateThread;
ThreadStart UpdateThreadStart = new ThreadStart(QueryDataBase);
static MethodInvoker CallDataBindToDataGrid = new MethodInvoker(DataBindToDataGrid);
static DataSet MyDataSet;
static SqlDataAdapter MyDataAdapter;
static string MyQueryString = "SELECT Products.* FROM [Order Details] CROSS JOIN Products";
static SqlConnection MyConnection = new SqlConnection("data source=localhost;initial catalog=Northwind;integrated security=SSPI;");
[STAThread]
static void Main()
{
MyForm = new Form1();
Application.Run(MyForm);
}
private void Form1_Load(object sender, System.EventArgs e)
{
}
private void button1_Click(object sender, System.EventArgs e)
{
UpdateThread = new Thread(UpdateThreadStart);
UpdateThread.Name = "Update Thread";
UpdateThread.IsBackground = true;
UpdateThread.Start();
}
private void button2_Click(object sender, System.EventArgs e)
{
QueryDataBase();
}
static void DataBindToDataGrid()
{
MyForm.dataGrid1.DataSource = MyDataSet;
MyForm.dataGrid1.DataMember = "MyTable";
MyDataSet = null;
MyDataAdapter = null;
}
static void QueryDataBase()
{
MyDataSet = new DataSet();
MyConnection.Open();
MyDataAdapter = new SqlDataAdapter(MyQueryString, MyConnection);
MyForm.label1.Text = "Filling the DataSet";
MyDataAdapter.Fill(MyDataSet, "MyTable");
MyConnection.Close();
MyForm.label1.Text = "DataSet Filled";
MyForm.BeginInvoke(CallDataBindToDataGrid);
}
}
}