Asynchronous Execution in ADO.NET 2.0
page 5 of 9
by Satheesh Babu
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 59804/ 113

Advanced scenarios with Asynchronous operations

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.


View Entire Article

User Comments

Title: f   
Name: f
Date: 2012-09-29 7:03:06 AM
Comment:
f
Title: thank you   
Name: khoa
Date: 2011-06-12 7:21:13 PM
Comment:
very good code and article
Title: Code Monkey   
Name: King Kong
Date: 2009-10-29 12:20:15 PM
Comment:
This is a great article. Very concise and to the point.
Title: InvalidOperation exception   
Name: Ross
Date: 2009-01-29 9:36:34 AM
Comment:
Kevin - Is the DB connection still open at the point you call EndExecuteReader()?
Title: InvalidOperation exception   
Name: Kevin Burton
Date: 2008-12-18 11:42:00 PM
Comment:
I have some code that I was trying to improve by adding the new asynchronous SQL methods Begin... and End.... But when I add it to my code I get an exception:

InvalidOperationException.
{"The asynchronous operation has already completed."}

The first few frames of the stack trace look like:

at System.Data.SqlClient.SqlCommand.VerifyEndExecuteState(DbAsyncResult
dbAsyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult
asyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult
asyncResult)

In searching the internet I see that at least one other person has seen this problem. I was wondering if there is a work around? If anyone has gotten the asynchronous methods to work without this exception I would very much appreciate their tips. I would also welcome a confirmation message of simply "this is a known bug".

Comments?

Thank you.

Kevin
Title: very useful   
Name: RuneAletheia
Date: 2008-12-16 3:24:03 PM
Comment:
this article was very helpful at explaining basic implementation of async -- also, the updated link to the reference is:

http://nayyeri.net/blog/Asynchronous-command-execution-in-NET-2-0/
Title: Very Good   
Name: Sunil Dhiman
Date: 2008-07-04 12:17:38 AM
Comment:
this article helped me very much.
thanks.
Title: Very Good   
Name: Samim Mondal
Date: 2008-04-02 3:16:42 AM
Comment:
Its very good article....
Title: RE:Thank you   
Name: satheesh babu
Date: 2008-03-24 12:55:18 PM
Comment:
Thanks for the comments guys!!
Satheesh
http://www.codedigest.com
Title: Thank you   
Name: Beri
Date: 2008-03-24 12:51:21 PM
Comment:
I've been struggling to make sense of Asynchronous Transcations. This article has helped me a lot. Thank you very much Sateesh.
Title: Mrs   
Name: JayaNayagam
Date: 2008-01-07 1:14:14 AM
Comment:
The author explains quite clearly,quite useful.
Title: Nice info   
Name: Abhishek Kumar Singh
Date: 2007-12-20 4:41:16 AM
Comment:
nice one

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-12-11 7:44:40 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search