ADO.NET 3.5 Cookbook, 2nd Edition

10.3. Executing Simultaneous SQL Statements Asynchronously

Problem

You need to execute multiple SQL statements simultaneously and process results when each statement completes or once all of the statements have completed.

Solution

Use the WaitAny() or the WaitAll() method of the WaitHandle class monitor. Both solutions are shown here.

The first solution creates an array of 10 Command objects that each execute a T-SQL WAITFOR statement that instructs SQL Server to wait for a random number of seconds between 1 and 10. The BeginExecuteNonQuery() method of the Command class is used to asynchronously execute each command in the array. The WaitHandle object for each Command object is retrieved using the AsyncWaitHandle property of the IAsyncResult returned by the BeginExecuteNonQuery() method. Each WaitHandle object is added to an array of WaitHandle objects corresponding to the Command array.

A loop is set up to process each of the 10 commands as they complete. Each iteration of the loop executes the WaitAny() method of the static WaitHandle class—the WaitAny() method takes a WaitHandle array argument of objects to wait to complete. When any command in the array completes, the EndExecuteNonQuery() method of that command is called to complete the command execution. The number of the command in the array and the number of records affected (0 in all cases for this example) are output to the console. Finally, the connection for the command is closed. The loop ends when all Command objects in ...