AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1519&pId=-1
Asynchronous Execution in ADO.NET 2.0
page
by Satheesh Babu
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 59813/ 118

Introduction

ADO.NET 2.0 is packed with many new features that make the developers' life easier. One such feature is executing bulk database operations asynchronously against a database. This article will help a developer to start programming asynchronous commands in ADO.NET 2.0. In previous versions of .NET it is not possible to execute a command asynchronously against a database. Well, this is mitigated with the arrival of new extensions in ADO.NET with 2.0 releases. To understand asynchronous programming we should first understand synchronous operation which is the common paradigm in ADO.NET.

Synchronous Model

Consider the listing of code below, which is purely synchronous.

Listing 1 - Synchronous

SqlDataReader dr1 = com.ExecuteReader();
//Other lines of codes
 SqlDataReader dr2 = com.ExecuteReader();

Synchronous means, the thread that is executing line 1 will wait until the database operations is complete and proceed with the execution of line 2. If the database operation is really massive, then the execution will not proceed with the other lines of code until the massive database operation is completed. This makes the application respond slower and the end user might feel the application is not performing well. This degradation can be mitigated with the new extensions of ADO.NET 2.0 which is called Asynchronous ADO.NET commands. With this, it is possible to execute massive database operations asynchronously in a separate thread by proceeding current execution of other lines of code without waiting for the massive database operations to complete. We will see this in detail in coming sections.

Asynchronous Model

The command object of ADO.NET 2.0 is packed with the required methods to make this new feature available. The main operations that can be performed through command object are:

Listing 2 - Command methods

ExecuteNonQuery()
ExecuteReader()
ExecuteScalar()
ExecuteXmlReader()

Asynchronous model is supported for all the above operations except ExecuteScalar() method, which is going to return the first row’s first column value. For programming the above operations asynchronously, command object has one begin method and one end method for each of their synchronous counterparts. The begin method will be called to start the execution while end method will be called when the execution completes. Refer to the Figure 1 for Asynchronous counterparts of the above operations.

Figure 1 - Available Asynchronous methods

Async in action

I will implement asynchronous execution with DataReader object in this example.

Prerequisites

For the Asynchronous operation to work we have to set “Asynchronous Processing=true” or “Async=true” in the connection string. Without this attribute set in connection string, the ADO.Net will give an error.

"This command requires an asynchronous connection. Set 'Asynchronous Processing=true' in the connection string."

 If in a same application you are using both Asynchronous and synchronous operation, it is better to use separate connection string i.e. one with “Asynchronous Processing=true” and the other with “Asynchronous Processing=false.” Because we are using the connection string with Async enabled for synchronous operation, it will hit the performance some what.

The connection string will be:

"Data Source=.\SQLEXPRESS;Asynchronous Processing=true;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;"

Listing 3 - Async in action

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); 
 
 IAsyncResult ar1 = com1.BeginExecuteReader(); // Async line 1
 IAsyncResult ar2 = com2.BeginExecuteReader(); // Async line 2
 //Perform some execution here
 //Perform some execution here
 dr1 = com1.EndExecuteReader(ar1); //Sync line 1          
 dr2 = com2.EndExecuteReader(ar2); //Sync line 2

The thread that is executing the “Async line 1” continues executing the other lines of code (“Async line 2,” etc.) in the above code (Listing 3 – Async in action) without waiting for the database operations to complete. It means the database operation is given to a different thread and the current thread follows executing next line i.e. “Async line 2.” This is called Asynchronous behavior. But the “Sync line 1” and “Sync line 2” will be executed synchronously. The thread will wait for the “Sync line 1” to complete execution and proceed with the “Sync line 2.”  Download the code packed with this article and see it in action. I have used "waitfor delay '00:00:06'" in the query to demonstrate the example as an alternative of complex stored procedure that takes sometime to complete execution. Note “waitfor” statement will work only in Sql server 2005.

Thus we have learned to use the Asynchronous operations at a beginner level. Moving forward, we will leverage more features that are packed with asynchronous operations that can be used in some complicated scenarios.

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.

Points to consider

Since the database operation is executed in a separate thread, if an exception occurs then the operation is signaled as complete and the actual exception will be thrown if we call End method. So we have to make sure that we are having proper exception handling code in this scenario.

Downloads
References
Conclusion

In this article I have discussed one of the useful features in ADO.NET 2.0 which helps us to increase the scalability and performance of the database application drastically without much effort. Download the source with this article and see it in action.

Happy Coding!!!


Product Spotlight
Product Spotlight 

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