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 : Page 2

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.

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.

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