How to show asynchronous processing of database records on a WinForm UI using Multithreading

In this article you will learn to show current row on a WinForm User Interface that is being accessed from a SQL Server database

Introduction:

We have an Employee table of 50000 records in a SQL Server database. We want to load all employees in a ComboBox on a WinForm. By default Windows Form works on a single Thread. So when we will try to load all the data in the ComboBox, it will hang the form and we cannot use any control on the form till all the data is loaded.

To avoid this and make our Form more responsive, we will use a BackgroundWorker control that will take care of loading employee’s data in the ComboBox on a separate thread. We will use two Label controls to show the name of the current employee being added to the ComboBox and number of rows processed.

Step 1:

Create a new Windows Forms Application and add one ComboBox, two Label, one Button and one BackgroundWorker Control to the Form1 as below

Step 2:

  • Add following namespace in Form1 code behind file (Form1.cs)
using System.Data.SqlClient;
using System.Configuration;
using System.Threading;
  • Add reference to System.Configuration to access App.config file
  • Declare following variables inside class Form1
string ConString, CmdString;
SqlConnection con;
SqlCommand cmd;
SqlDataReader reader;
  • Write following code to get Connection String from App.config file
ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
  • Set WorkerReportsProgress and WorkerSupportsCancellation properties of BackgroundWorker to True
  • Add a class named MyWorkerClass in Form1 class that will be used to interact with the User Interface
public class MyWorkerClass
{
     public string EmployeeName;
     public int EmployeeID;
}

Step 3:

  • Code for Button Click event
private void button1_Click(object sender, EventArgs e)
{
     cbEmployee.Items.Clear();
     MyWorkerClass obj = new MyWorkerClass();
     if (!backgroundWorker1.IsBusy)
     {
          backgroundWorker1.RunWorkerAsync(obj);
          btnLoadData.Enabled = false;
          btnLoadData.Text = "Loading...";
     }
}

Here, if BackgroundWorker is not busy then RunWorkerAsync method is called. This method causes DoWork event of the BackgroundWorker to fire which loads data from the database on a separate thread. An object of the MyWorkerClass is passed to communicate between these threads.

  • Add codes for DoWork, ProgressChanged and RunWorkerCompleted event of the BackgroundWorker control
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
     MyWorkerClass obj = (MyWorkerClass)e.Argument;
     LoadData(obj);
}

Here, LoadData method is called to load data from the database.

private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
     if (!backgroundWorker1.CancellationPending)
     {
          MyWorkerClass obj = (MyWorkerClass)e.UserState;
          cbEmployee.Items.Add(obj.EmployeeName);
          cbEmployee.SelectedIndex = e.ProgressPercentage;
          lblEmployeeName.Text = obj.EmployeeName;
          lblTotalRows.Text ="Rows processed : " +e.ProgressPercentage.ToString();
     }
}

ProgressChanged event is fired when ReportProgress method is called. Here, it is called from LoadData method for every record processed. ReportProgress method passes MyWorkerClass object with for every record. This object contains EmployeeName that is shown in the lblEmpoyeeName Label control and it is added in the ComboBox item collection. ProgressChangedEventArgs.ProgressPercentage returns an integer as percentage that is passed in ReportProgress method.

Note: We cannot access a UI control directly from the DoWork event of BackgroundWorker control. So we use a class object for communication between DoWork and ProgressChanged event like our MyWorkerClass

private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
     lblEmployeeName.Text = "All data loaded!";
     btnLoadData.Enabled = true;
     btnLoadData.Text = "Load Data";
}

RunWorkerCompleted event is fired after the BackgroundWorker has completed its task or cancelled or an exception has occurred.

  • Code for LoadData method
public void LoadData(MyWorkerClass obj)
{
     try
     {
          using (con = new SqlConnection(ConString))
          {
               CmdString = "SELECT EmployeeID, FirstName + ' ' + LastName AS Name FROM Employees";
               cmd = new SqlCommand(CmdString, con);
               con.Open();
               reader = cmd.ExecuteReader();
               int i = 0;
               while (reader.Read())
               {
                    obj.EmployeeName = reader["Name"].ToString();
                    obj.EmployeeID =int.Parse(reader["EmployeeID"].ToString());
                    backgroundWorker1.ReportProgress(i, obj);
                    i++;
                    Thread.Sleep(10);
               }
               con.Close();
          }
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
}

Here we are simply accessing data from Employees table using a DataReader. We are using ReportProgress method to report progress of every record accessed from the database and passing a counter for ProgressPercentage and an object of MyWorkerClass with current employee’s Name and EmployeeID. ReportProgress method will raise ProgressChanged event where EmployeeName is accessed via passed object of the MyWorkerClass. Thread.Sleep method is called to pause the background thread for 10 ms so that current processing record is visible in the ComboBox and Label control on our UI.

Finally when we click on the LoadData button, we can see Employees being added to the ComboBox and lblEmployeeName Label. In the other Label we can see the number of rows processed.

One thought on “How to show asynchronous processing of database records on a WinForm UI using Multithreading

Leave a Reply

Your email address will not be published. Required fields are marked *