Asynchronous Processing in ADO.NET 2.0

Asynchronous Processing in ADO.NET 2.0

Posted by Rickie Lee, http://rickie.cnblogs.com

Support for asynchronous processing would have allowed users to make multiple, unrelated updates to the database in a parallel fashion. With the release of ADO.NET 2.0, now we are able to process database commands asynchronously.

Please note the following requirements with the use of asynchronous processing in ADO.NET 2.0:

1. MDAC 9.0 is installed on the machine.

2. Set Asynchronous Processing=True in the connection string.

Or manually set SqlConnectionStringBuilder.AsynchronousProcessing = true in your code snippet.

Generally, there are at least three approaches to execute asynchronous processing in ADO.NET 2.0. Let’s discuss them one by one in the following content.

1. Polling the IAsyncResult object

…………………………………………………………

// Start the asynchronous processing

IAsyncResult result = command.BeginExecuteNonQuery();

while (!result.IsCompleted)

{

Thread.Sleep(100);

……………………………………

}

// Retrieve result from the asynchronous process

int result = command.EndExecuteNonQuery(result);

…………………………………………………

2. Callback method - AsyncCallback

The Callback approach enables you to handle the result of a command execution at a different method. This feature is useful in cases where the command execution takes a long time to finish and you want to respond to users without waiting for the command execution to finish.

AsyncCallback callback = new AsyncCallback(HandleCallback);

command.BeginExecuteReader(callback, command);

Although it’s not required that we pass the SqlCommand object as the second parameter in the BeginExecuteReader call, doing so makes it easier to call EndExecuteReader in the callback procedure.

The code snippet from Callback procedure is as follows.

SqlCommand command = (SqlCommand)result.AsyncState;

SqlDataReader reader = command.EndExecuteReader(result);

3. Wait Approach - WaitHandle

The most elegant of these three approaches is the wait approach, which is to associate a wait handler with the asynchronous process. Using this approach, we can start all the asynchronous processing and then wait for al or any of them to finish so that we can process them accordingly.

int index = System.Threading.WaitHandle.WaitAny(waitHandles);

Using the above method causes the execution to wait for any of the asynchronous processes that have been started and whose wait handles are in the passed array. So the WaitAny method must be called repeatedly in order to process each WaitHandle in the array.

The return value from the WaitAny method is the array index of the Wait Handle that just finished running.

Get more detail information from the reference book or MSDN.

References:

1. Professional ASP.NET 2.0, by Bill Evjen, Scott Hanselman, Farhan Muhammad, Srinivasa Sivakumar, Devin Rader. Wrox - Wiley Publishing Company 2005