devxlogo

Executing Database Commands Asynchronously with ADO.NET 2.0

Executing Database Commands Asynchronously with ADO.NET 2.0

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.

Executing Asynchronous SQL Commands
This example shows how to execute a command asynchronously using the callback feature. To start, create a VB.NET Windows Forms application named AsyncFeatures using Visual Studio 2005, and rename the default form Form1.vb to AsyncCallbackExample.vb. Place a command button on the form. Now modify the code in the AsyncCallbackExample.vb file to look like Listing 1.

The code in Listing 1 declares two private variables to hold the SqlConnection and SqlCommand objects. The Button1_Click event contains the meat of the code, and starts by defining a variable to hold the connection string. Note that the async attribute is set to true in the connection string. Then the code creates instances of the SqlConnection and SqlCommand objects, passing in appropriate parameters to their constructors. Finally, the code invokes the BeginExecuteReader method of the SqlCommand object, passing in the callback method named QueryCallback as an argument. When the SQL command has finished executing, ADO.NET will automatically invoke the QueryCallbackMethod, passing the corresponding IAsyncResult object as a parameter.

The QueryCallbackMethod invokes the SqlCommand object’s EndExecuteReader method to obtain the query execution output?a SqlDataReader object, in this case. Then the code simply displays the number of records returned from the query in a message box.

Executing Multiple Queries in Parallel
So far, you have seen how to execute a single command asynchronously using the callback feature. In this section, you’ll see how to execute multiple commands at the same time by taking advantage of the asynchronous features of ADO.NET.

This example executes two different queries in parallel to retrieve author and employee details from two different databases. For the purposes of this example, add a Windows Form named AsyncQueryExample.vb to the project and place a command button on the form. In the button’s Click event code, invoke a method named ExecuteAsyncQueries, shown in Listing 2.

In this example, you create two different SqlConnection objects that point to the Northwind and Pubs databases respectively. Then the code invokes two different BeginExecuteReader methods to fire off SQL select queries against the authors table in the Pubs database and the employees table in the Northwind database. After that, the code retrieves the query results by calling the EndExecuteReader methods. Finally, it displays the results in two list boxes. Note that the call to the EndExecuteReader method is a blocking call?meaning that if the query hasn’t finished executing, the code will be blocked until the query completes.

Executing a SQL Command Asynchronously from an ASP.NET Page
This section demonstrates how to execute a SQL command asynchronously from within an ASP.NET page. If you make an asynchronous SQL command invocation from within an ASP.NET application, and then return immediately within your code, you may not have the opportunity to include the results of the query execution in the data returned to the user?in other words, the page may complete before the query does. However, you can overcome this shortcoming by not releasing the currently executing thread. To accomplish this, you use the WaitHandle object, which lets you do some processing after making a SQL command execution call, and then block until the command has completed.

To start, create a new ASP.NET Web site named AsyncFeatures using VB.NET as the language. Rename the default page from Default.aspx to AsyncExample.aspx. Modify the code in the AsyncExample.aspx file to look like Listing 3.

?
Figure 3. AsyncExample Web Form Output: The figure shows the output produced by the example asynchronous command execution code shown in Listing 3.

The code in Listing 3 uses the WaitHandle object’s WaitOne method to wait for the command execution to complete. The WaitHandle class also contains other static methods such as WaitAll and WaitAny. These two static methods take arrays of WaitHandles as parameters, and return when either all the calls have completed, or as soon as any of the calls have completed, depending on the method that you call. For example, if you are making three separate command execution calls, you can call each asynchronously; place the WaitHandle for each in an array, then call the WaitAll method until they are finished. Doing that lets all three commands execute at the same time. It’s also important to note that the WaitOne, WaitAll, and WaitAny methods all optionally accept a timeout parameter value. Using the timeout option, you can specify the amount of time that you want to wait for a command to return. If the methods timeout, they will return a value of False. Figure 3 shows the output produced by Listing 3.

As you can see, asynchronous execution opens up a raft of new opportunities and interesting scenarios that you can use to increase the performance and responsiveness of your .NET applications.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist