Asynchronous Processing of Database Records on a WinForm UI Using Multithreading

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 try to load all the data in the ComboBox, it will hang the form and we cannot use any control on the form until 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 the employee data into the ComboBox using 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 the following namespace in the Form1 code file (Form1.cs):

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

• Add a reference to System.Configuration to access App.config file
• Declare the following variables inside class Form1:
  
  string ConString, CmdString;
  SqlConnection con;
  SqlCommand cmd;
  SqlDataReader reader;
   
• Write the following code to get the Connection String from the App.config file:
  
  ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
   
• Set the WorkerReportsProgress and WorkerSupportsCancellation properties of the BackgroundWorker to True
• Add a class named MyWorkerClass in the 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 the BackgroundWorker is not busy then the RunWorkerAsync method is called. This method causes the 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 code for the DoWork, ProgressChanged and RunWorkerCompleted events of the BackgroundWorker control:

  private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
  {
      MyWorkerClass obj = (MyWorkerClass)e.Argument;
      LoadData(obj);
  }

  Here, the 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();
      }
  }

  The ProgressChanged event is fired when the ReportProgress method is called. Here, it is called from the LoadData method for every record processed. The ReportProgress method passes the MyWorkerClass object 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 a BackgroundWorker. So we use a class object for communication between the DoWork and ProgressChanged events like our MyWorkerClass.
  
  private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
  {
      lblEmployeeName.Text = "All data loaded!";
      btnLoadData.Enabled = true;
      btnLoadData.Text = "Load Data";
  }

  The 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 the Employees table using a DataReader. We are using the ReportProgress method to report progress of every record accessed from the database and passing a counter for the ProgressPercentage and an object of MyWorkerClass with current employee's Name and EmployeeID. The ReportProgress method will raise the ProgressChanged event where EmployeeName is accessed via the passed object of the MyWorkerClass. The Thread.Sleep method is called to pause the background thread for 10 ms so that the 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 the Employees being added to the ComboBox and lblEmployeeName Label. In the other Label we can see the number of rows processed.