RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Executing Database Commands Asynchronously with ADO.NET 2.0

Microsoft's upcoming ADO.NET 2.0 gives you the ability to execute SQL commands asynchronously, sidestepping the blocking effect of making lengthy database calls and letting you make multiple queries simultaneously, using polling, WaitHandles, or delegate notification to know when query execution completes.


ith the release of.the NET Framework 2.0, Microsoft is enhancing the feature set by baking the ability to execute SQL commands asynchronously into the ADO.NET Class Library. Using this new feature, you can asynchronously execute commands against a SQL Server database without waiting for the command execution to finish, which can be very handy in situations where you are trying to execute long-running database commands from a Windows Forms or an ASP.NET client application. Asynchronous execution lets you enhance the overall performance and responsiveness of your application. This article explores the new asynchronous command execution support provided by ADO.NET and demonstrates how to take advantage of this new feature.

Synchronous vs. Asynchronous Command Execution
Synchronous operations consist of component or function calls that operate in lockstep. A synchronous call blocks a process until the operation completes. Only then will the process execute the next line of code. Figure 1 shows the steps involved in executing a command synchronously against the database.

In Figure 1, the client application starts by creating a SqlCommand object and initializing various properties of the SqlCommand object with the appropriate values. Next, the client invokes any of the synchronous methods of the SqlCommand object, such as ExecuteNonQuery, ExecuteReader, ExecuteXmlReader, and so on through that SqlCommand object. Finally, the client waits until the database server either completes the query execution, or if there is no response for a given period of time the client times out raising an error. Only after the method call returns is the client free to continue processing the next line of code.

Now that you've seen the steps involved executing a command synchronously, contrast them with the steps involved in asynchronous execution (see Figure 2).

Figure 2. Asynchronous Execution: Executing commands asynchronously enables parallelism by letting the client application continue executing commands while it's waiting for the response from a previously issued command.
Figure 1. Synchronous Execution: Executing commands synchronously results in a sequential execution, where each command must complete before the next command begins executing.
Although Figure 2 looks similar to Figure 1, it's worth walking through the differences.

For an asynchronous operation, the client creates a SqlCommand object and initializes various properties of the SqlCommand object with the appropriate values. But in the asynchronous operation the client application also sets the async attribute in the connection string to true. Next, the client invokes any of the asynchronous methods such as BeginExecuteNonQuery, BeginExecuteReader, or BeginExecuteXmlReader through the SqlCommand object. Note that for this release of ADO.NET 2.0 these are the only asynchronous methods available.

After invoking the SQL command, the client code immediately moves onto the next line of code without waiting for a response from the database. This means instead of waiting the client code can perform some other operations while the database server is executing the query.

Overview of Asynchronous Command Execution
In .NET, to be able to invoke a method named XXX asynchronously, you need to ensure that the method has both BeginXXX and EndXXX variations. The BeginXXX method initiates an asynchronous operation and returns immediately, returning a reference to an object that implements the IAsyncResult interface. Your code needs to access that interface to monitor the progress of the asynchronous operation. When the asynchronous operation completes, you call the EndXXX method to obtain the result and clean up any supporting resources.

There are four common ways to use BeginXXX and EndXXX to make asynchronous calls. In all cases, you invoke BeginXXX to initiate the call. After that, you can do one of the following:

  1. Do some work and then call EndXXX. If the asynchronous operation is not finished, EndXXX will block until it completes.
  2. Using a WaitHandle obtained from the IAsyncResult.AsyncWaitHandle property, call the WaitOne method to block until the operation completes. Then call EndXXX.
  3. Poll the IAsynResult.IsCompleted property to determine when the asynchronous operation has completed. Then call EndXXX.
  4. Pass a delegate for a callback function that you supply (of type IAsyncCallback) to BeginXXX. That callback function will execute when the asynchronous operation completes. Code in the callback function calls EndXXX to retrieve the result.
This article will demonstrate asynchronous execution using all but the third technique.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date