Sometimes we may need to populate data in different grids from different tables that may take sometime to complete because it is bulk. If we begin to populate the data in a traditional way, we need to fetch the data one by one synchronously i.e. waiting for one database operation to complete to proceed with the other. We can consider using asynchronous model in these types of scenarios to bind the grid and thus we can prevent the waiting time for binding the grids.

To bind the grid we will be requiring the resultset which will be available only if the database operation is complete i.e. if it is datareader the result will be available only by calling EndExecuteReader() if the database operation is complete. We need a mechanism where we can detect the end of database operation and bind the grid with the resultset.

This can be achieved by 3 ways.

·         Through WaitHandle class

·         Through Callback delegate

·         Polling

Calling End method without detecting the complete signal will make the execution wait there until it is completed.

WaitHandle class

WaitHandle class in System.Threading namespace comes to our rescue in these scenarios to detect the completion signal from the asynchronous thread and complete the data binding.

WaitHandle class has 2 methods that take array of WaitHandle objects.

Listing 4 - WaitHandle methods

WaitHandle.WaitAll() 
WaitHandle.WaitAny()
WaitHandle.SignalAndWait()

The first method WaitAll() will wait for all the asynchronous operation to complete and return a Boolean indicating the completion of all the opeartion while the next method WaitAny() will give us any one index of WaitHandle array indicating that it is completed. SignalAndWait() method signals one WaitHandle object and waits on another, as an atomic operation. The most commonly used methods are WaitAll() and WaitAny().

For making this work we need the WaitHandle object of each asynchronous operation. The IAsyncResult object which is returned by Begin method has a property called AsyncWaitHandle that will give us WaitHandle object to know the completion of the current asynchronous operation. Refer to the code below.

Listing 5 - WaitHandle methods

IAsyncResult ar1 = com1.BeginExecuteReader();
WaitHandle handles = ar1.AsyncWaitHandle;

The next section will explain the use of WaitHandle object in detail.

Using WaitHandle Object

Refer to the example code with this article to have a better understanding.

Listing 5 - WaitHandle implementation

try
{
  con1 = new SqlConnection(ConfigurationManager.ConnectionStrings[
    "ConnectionString"].ConnectionString);
  con1.Open();
  com1 = new SqlCommand(
    "SELECT emp_id, fname, lname, job_id, hire_date FROM employee ORDER BY emp_id, fname"
    , con1);
 
  con2 = new SqlConnection(ConfigurationManager.ConnectionStrings[
    "ConnectionString"].ConnectionString);
  con2.Open();
  com2 = new SqlCommand("waitfor delay '00:00:06';SELECT * FROM Authors", con2);
 
  con3 = new SqlConnection(ConfigurationManager.ConnectionStrings[
    "ConnectionString"].ConnectionString);
  con3.Open();
  com3 = new SqlCommand("waitfor delay '00:00:06';SELECT * from Department",
    con3);
 
  IAsyncResult ar1 = com1.BeginExecuteReader();
  IAsyncResult ar2 = com2.BeginExecuteReader();
  IAsyncResult ar3 = com3.BeginExecuteReader();
 
  WaitHandle[]handles = new WaitHandle[3];
  handles[0] = ar1.AsyncWaitHandle;
  handles[1] = ar2.AsyncWaitHandle;
  handles[2] = ar3.AsyncWaitHandle;
 
  for (int results = 0; results < handles.GetLength(0); results++)
  {
    // wait for any handle, then process results as they come
    int index = WaitHandle.WaitAny(handles, 8000, false); // 8 secs
 
    if (WaitHandle.WaitTimeout == index)
      throw new Exception("Timeout Exception");
    if (index == 0)
    {
      dr1 = com1.EndExecuteReader(ar1);
      gvEmployee.DataSource = dr1;
    }
    else if (index == 1)
    {
      dr2 = com2.EndExecuteReader(ar2);
      gvAuthors.DataSource = dr2;
    }
    else if (index == 2)
    {
      dr3 = com3.EndExecuteReader(ar3);
      gvDepartment.DataSource = dr3;
    }
  }
  Page.DataBind();
 
}
 
catch
{
  throw;
}
 
finally
{
  dr1.Close();
  com1.Dispose();
  con1.Close();
  dr2.Close();
  com2.Dispose();
  con2.Close();
  dr3.Close();
  com3.Dispose();
  con3.Close();
}

The above code uses WaitHandle.WaitAny() method and populates data asynchronously and binds 3 gridviews. If we see this line:

Listing 6 - WaitHandle in action

int index = WaitHandle.WaitAny(handles, 8000, false);

Here 8000 indicates that after 8000 milli seconds if no completed signal is received then it is said to have reached timeout. WaitHandle.WaitTimeout property will gives us the index of the WaitHandle object in the array indicating that it has reached the timeout.

Callback delegate

One of the overloads of begin method will accept a delegate argument with a state object to detect the completion. Once the database operation is complete, ADO.Net will call the delegate and the state can be accessed by IAsyncResult object.

Listing 7 - Callback delegate

BeginExecuteReader(AsyncCallback callback, object state)

The call back method should accept IAsyncResult as an argument and call the End method.

Visit the Keyvan's blog in the references section to see the implementation of callback delegate.

Polling

The completion signal can also be detected by a method called IsCompleted() in IAsyncResult which returns a Boolean indicating that the operation is completed.

Cancel the execution

For some reason or business condition, if we like to cancel the operation, we can call Cancel() method packed with the command object.