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.
