Asynchronous Command Execution

Asynchronous Command Execution

When accessing data in a data store such as a database using ADO or ADO.NET 1.x, each query is executed in turn, and the code waits for each one to complete before processing the next one. This allows the same connection to be used if the queries access the same data store or a different connection if they access different data stores.

The previous section of this chapter demonstrates how MARS can be used to enable a single connection to handle multiple sets of results from the same data store, but this still doesn't solve the issue of executing more than one command concurrently. Neither does it help when you want to access different data stores concurrently.

However, ADO.NET 2.0 now includes features that allow multiple commands to execute concurrently and asynchronously. This means, in essence, that code can create and start execution of several commands without having to wait for them to complete individually. However, unless you use the MARS feature described earlier in this chapter, you must provide a separate connection for each one.

The asynchronous model adopted in ADO.NET 2.0 aims to give maximum flexibility when using this feature by implementing three distinct techniques:

It's also possible to open connections to a data source asynchronously, allowing code to continue with other work while waiting for the connection to be made. The process is much the same as executing a command, and we look at this topic later in the chapter. But before we examine the actual code required to perform asynchronous command execution, the next section lists the classes, properties, and methods used. At present, this feature is implemented only for the classes in the SqlClient namespace.

Asynchronous Classes, Methods, and Properties

To a greater extent, all three asynchronous execution techniques use the same methods that are added to the SqlCommand class in ADO.NET 2.0 to start a process and then capture the results. These methods are listed in Figure.

The IAsyncResult Interface and SqlAsyncResult Class

All of the methods for the SqlCommand class listed in the previous section that start an asynchronous process return a reference to an object that exposes the IAsyncResult interface. The specific class used for asynchronous command execution is SqlAsyncResult, and the properties of this class are shown in Figure.

The AsyncCallback Delegate

The AsyncCallback delegate is a system class that is not specifically allied to ADO.NET but is used by many objects within the Framework. It is used to specify the callback routine or function that will be executed when the asynchronous process completes. The only member of this class that's important here is the constructor, shown in Figure, which is used to generate an instance of the class to pass to the BeginExecuteNonQuery, BeginExecute Reader, and BeginExecuteXmlReader methods of the Command.

See the section on the asynchronous callback model later in this chapter for more details.

The WaitHandle Class

The WaitHandle class is an abstract system class used for many purposes within the Framework. By calling the methods of the WaitHandle, you effectively tell the code to wait for the specified process to complete. If there are multiple processes involved, you can create an array of WaitHandle instances and then tell the code to "sleep" until any one of them completes. The WaitHandle class exposes a Static property, shown in Figure, for the timeout that will be allocated to the process.

There are also three methods that allow you to start the wait process and one to close and release the handle afterwards, as shown in Figure.

See the section on the asynchronous wait model later in this chapter for more details.

The Asynchronous Polling Model

The simplest approach to handling asynchronous execution of one or more commands is through the polling model. It simply involves starting off the process (in Listing 3.4 this is a SQL UPDATE statement, but it could be any statement or stored procedure that does not return rows) and then repeatedly checking the IsCompleted property of the SqlAsyncResult instance until it returns True.

Of course, the code can go off and do other things between checking to see whether the process is complete. However, this approach is not recommended unless it is a simple and "tight" loop that handles a specific required task and uses only minimal processing time. If there are large or unrelated tasks to accomplish, you should consider using the callback or wait models instead.

An Example of the Asynchronous Polling Model

' create connection and command as usual
Dim oConn As New SqlConnection("your-connection-string")
oConn.Open()
Dim oCmd As New SqlCommand("UPDATE table SET ....", oConn)

' start the command execution, and collect the AsyncResult instance
Dim oResult As SqlAsyncResult = oCmd.BeginExecuteNonQuery()

While Not oResult.IsCompleted

  ' do something else

End While

' must now be complete, so get result from AsyncResult instance
Dim iRowsAffected As Integer = oCmd.EndExecuteNonQuery(oResult)

All this assumes that you want to execute only one command and that you need to wait for it to complete. It's also possible to start off more than one Command execution process; however, in this case you must execute each one over a separate connection unless you are using the MARS feature of ADO.NET 2.0.

The Asynchronous Callback Model

The second approach to asynchronous execution involves providing a routine in the code that will act as a callback. It will be executed when the action you specify occurs, rather like the way that an event handler is called to handle a user's interaction with an application.

To specify the callback routine you create a new instance of the Async Callback class, providing the name of that routine as the parameter, and pass this AsyncCallback instance into the method you use to start execution of the command. In Listing 3.5, we use a query that returns some rows, so it is executed with the BeginExecuteReader method.

An Example of the Asynchronous Callback Model

' create connection and command as usual
Dim oConn As New SqlConnection("your-connection-string")
oConn.Open()
Dim oCmd As New SqlCommand("SELECT * FROM table", oConn)

' start the command execution, and collect the AsyncResult instance
oCmd.BeginExecuteReader(New AsyncCallback(AddressOf DisplayRows), _
                        CommandBehavior.CloseConnection)

' do something else here
' ...
' and then finish

Code can continue to do other things after the query in the Command has been executed. During this period, as soon as the query has completed, the callback handler named DisplayRows is called (on an arbitrary thread, which is unlikely to be the same thread as the rest of the code). The callback event handler receives a reference to an AsyncResult instance that is created automatically by the BeginExecutexxx method, from which it can obtain a reference to the results of the query by calling the appropriate EndExecutexxx method, as shown in Listing 3.6.

The DisplayRows Callback Event Handler

' callback handler routine to display results
Sub DisplayRows(oResult As SqlAsyncResult)

  ' must now be complete, so get result from AsyncResult instance
  Dim oReader As DataReader = oResult.EndExecuteReader(oResult)

  ' display results using oReader - which is a normal DataReader
  ' ...

End Sub

The Asynchronous Wait Model

The most complex of the asynchronous methods is also the most efficient if all you want to do is start some commands running against one or more data sources (they can all use separate connections and therefore different databases if required) and not execute other code in the meantime. You simply want to wait until one, more, or all of the commands have completed and then perhaps display some results.

In this case, you start each process in the same way as the previous examples but then use the AsyncResult to create a WaitHandle that you use to monitor each process. If there is more than one process and you want to wait for one or all of these to complete, you use an array of WaitHandle references. The code in Listing 3.7 shows the simplest case of waiting for one command to complete.

A Simple Example of the Asynchronous Wait Model

' create connection and command as usual
Dim oConn As New SqlConnection("your-connection-string")
oConn.Open()
Dim oCmd As New SqlCommand("UPDATE table SET ...", oConn)

' execute command and get back AsyncResult instance
Dim oResult As SqlAsyncResult = oCmd.BeginExecuteNonQuery()

' use AsyncResult instance to create a WaitHandle
Dim oHandle As WaitHandle = oResult.AsyncWaitHandle

' tell code to sleep here until process is complete
oHandle.WaitOne()

' process is complete, so get results
Dim iRowsAffected As Integer = com.EndExecuteNonQuery(oResult)

Checking Whether the Process Timed Out

In the code in Listing 3.7, there is no check to see whether the process completed successfully or failed through a timeout. It's easy enough to specify the timeout you want and then to detect whether the process actually did complete within that period, as shown in Listing 3.8.

Waiting for a Single Process to Complete or Time Out

...
' use AsyncResult instance to create a WaitHandle
Dim oHandle As WaitHandle = oResult.AsyncWaitHandle

' set the timeout to 10 seconds
oHandle.WaitTimeout = 10000

' tell code to sleep here until the process is complete
' on return, check the status of the process
If oHandle.WaitOne() = True Then

  ' process is complete, so get results
  Dim iRowsAffected As Integer = oCmd.EndExecuteNonQuery(oResult)

Else

  ' process timed out
  lblError.Text = "Process failed to complete within 10 seconds"

End If

Using Multiple Wait Handles

If you are executing more than one process at a time, you use an array of WaitHandle instances to detect when one or all of the processes have completed. First you need to create the separate Connection and Command instances (unless you are using MARS over a single connection), as shown in Listing 3.9.

An Example of Multiple Wait Handles

' create three connections and three commands, and open connections
Dim oConnA As New SqlConnection("connection-string-A")
Dim oCmdA As New SqlCommand("SELECT * FROM tableA", oConnA)
oConnA.Open()

Dim oConnB As New SqlConnection("connection-string-B")
Dim oCmdB As New SqlCommand("UPDATE tableB SET ...", oConnB)
oConnB.Open()

Dim oConnC As New SqlConnection("connection-string-C")
Dim oCmdC As SqlCommand
oCmdC = New SqlCommand("SELECT * FROM tableC FOR XML AUTO", oConnC)
oConnC.Open()
...

Now the code can start the three processes running, collecting an AsyncResult instance for each one (Listing 3.10). These AsyncResult instances are queried to get the three WaitHandles, and they are assigned to an array named aHandle. In Listing 3.10, the code will wait for all three processes to complete because it uses the static WaitAll method of the WaitHandle class and passes in the array of three WaitHandle instances. At the point where all are complete, the code "wakes up" and retrieves the three results using the appropriate EndExecutexxx methods.

Executing the Commands with Multiple Wait Handles

...
' execute commands and get back AsyncResult instances
Dim oResultA As SqlAsyncResult = oCmdA.BeginExecuteReader()
Dim oResultB As SqlAsyncResult = oCmdB.BeginExecuteNonQuery()
Dim oResultC As SqlAsyncResult = oCmdC.BeginExecuteXmlReader()

' use all three AsyncResult instances to create WaitHandle array
Dim aHandle(2) As WaitHandle
aHandle(0) = oResultA.AsyncWaitHandle
aHandle(1) = oResultB.AsyncWaitHandle
aHandle(2) = oResultC.AsyncWaitHandle

' tell code to sleep here until all the processes are complete
WaitHandle.WaitAll(aHandle)

' processes are all complete, so get results
Dim oDataReader As DataReader = oCmdA.EndExecuteNonQuery(oResultA)
Dim iRowCount As Integer = oCmdB.EndExecuteNonQuery(oResultB)
Dim oXmlReader As XmlTextReader = oCmdC.EndExecuteXmlReader(oResultC)

If you want to test whether any of the processes did not complete because of a timeout, you just check the returned value from the WaitAll method. If it's False, at least one of the processes timed out. And if you want to specify the timeout, you can do so in the call to the WaitAll method. The following code sets the timeout to 10 seconds:

WaitHandle.WaitAll(aHandle, 10000, False)

Handling Multiple Processes as Each One Completes

Rather than waiting for all processes to complete, you can improve your code efficiency even more by using the WaitAny method. This wakes up your code as soon as any one of the processes completes. You handle the results of this process and then put the code back to sleep again until the next process completes. This must be repeated until all the processes in the array of WaitHandle instances are complete or have timed out.

The code in Listing 3.11 shows how this works. The code that creates the three connections and three commands, executes these commands, and creates the array of WaitHandle instances is not repeated here. All this is the same as in Listings 3.9 and 3.10.

What differs is that the code calls the WaitAny method this time. The Integer value this method returns is either the index within the WaitHandle array of the process that completed successfully or the value of the timeout if one of the processes timed out before it was complete. Note that the order in which processes complete or time out will probably not be the same as the order of their WaitHandle instances within the array.

To be sure of handling every one of the commands that are executing asynchronously, you must force the code to call the WaitAny method once for every command that is executing. If you call WaitAny only once, you'll get only one indication of a process completion. Trying to access all the results at this point is likely to cause an error because the rest of the commands may not have completed when you call the respective End Executexxx method.

So, in Listing 3.11, a For..Next loop executes the WaitAny method three times. As each process completes, the code checks the index value to see which process just finished and calls the appropriate EndExecutexxx method. (The results can also be displayed at this point to take maximum advantage of the asynchronous processing model.)

Handling Completion of Multiple Asynchronous Processes

' code as before to create and execute three commands
...

' code here to create array of WaitHandle instances
...

' following code is executed once for each WaitHandle instance
' so that all three end up being handled at some point
For iLoop As Integer = 1 To 3

  ' tell code to sleep here until any one of the processes completes
  ' collect the index of the next one to complete successfully
  ' NB: this will be the (Static) timeout value if one times out
  Dim iIndex As Integer = WaitHandle.WaitAny(aHandle, 5000, False)

  ' one of the processes has completed or timed out
  Select Case iIndex

    Case 0:
      ' command A completed successfully
      Dim oDataReader As DataReader
      oDataReader = oCmdA.EndExecuteReader(oResultA)

    Case 1:
      ' command B completed successfully
      Dim iRowCount As Integer
      iRowCount = oCmdB.EndExecuteNonQuery(oResultB)

    Case 2:
      ' command C completed successfully
      Dim oXmlReader As XmlTextReader
      oXmlReader = oCmdC.EndExecuteXmlReader(oResultC)

    Case WaitHandle.WaitTimeout:
      ' this one timed out - could use Case Else here instead
      lblError.Text &= "One process has timed out"

  End Select

Next

Canceling Processing for Asynchronous Commands

There may be an occasion where a process is executing asynchronously and you want to provide the user with the opportunity to cancel it partway through. To do so, all you have to do is call the Cancel method of the Command instance, as shown in Listing 3.12.

Canceling Asynchronous Command Execution

...
Dim oResult As SqlAsyncResult = oCmd.BeginExecuteNonQuery()

' ... do something else here ...

If (some-condition-is-met) Then
  oCmd.Cancel()
End If

Asynchronously Opening Connections

As well as executing a Command asynchronously, ADO.NET 2.0 also allows you to open connections asynchronously. This is useful, of course, when you are accessing more than one database at a time so that you can execute multiple asynchronous commands. The principle for asynchronous connections is the same as that we looked at for commands, with the same three options of using the polling, callback, or wait approaches. And, like the asynchronous command execution feature, asynchronous connection opening applies only to the SqlClient namespace classes—in this case, SqlConnection.

The SqlConnection class exposes a couple of properties that are useful when working asynchronously, as shown in Figure.

There is also a method that allows you to start the process of opening the connection and one to complete the process, as shown in Figure.

Asynchronous Connection Examples

The code in Listings 3.13, 3.14, and 3.15 demonstrates the three asynchronous models for opening a connection. The polling model, shown in Listing 3.13, collects the AsyncResult instance from the BeginOpen method. Then it repeatedly tests the connection state until the connection is open and calls the EndOpen method with the AsyncResult instance.

Opening a Connection Using the Asynchronous Polling Model

' create a new connection and open asynchronously
Dim oConn As New SqlConnection("your-connection-string")
Dim oResult As SqlAsyncResult = oConn.BeginOpen()

While Not oConn.State = ConnectionState.Open

  ' do something else

End While

oConn.EndOpen(oResult)

The callback model, shown in Listing 3.14, creates a new AsyncCallback instance that defines the name of the callback routine and passes this to the BeginOpen method to start opening the connection. Once the connection is open, the callback routine (named ConnectionOpened in this example) is called.

Opening a Connection Using the Asynchronous Callback Model

' create a new connection and open asynchronously
Dim oConn As New SqlConnection("your-connection-string")
oConn.BeginOpen(New AsyncCallback(AddressOf ConnectionOpened))

' do something else here
' ...
' and then finish
'----------------

' callback handler routine
Sub ConnectionOpened(oResult As SqlAsyncResult)

  oConn.EndOpen(oResult)

End Sub

Finally, the wait model, shown in Listing 3.15, creates a new WaitHandle for the process from the AsyncResult instance and calls its WaitOne method to "sleep" the code until the connection is open, then calls the EndOpen method.

Opening a Connection Using the Asynchronous Wait Model

' create a new connection and open asynchronously
Dim oConn As New SqlConnection("your-connection-string")
Dim oResult As SqlAsyncResult = oConn.BeginOpen()

' use AsyncResult instance to create a WaitHandle
Dim oHandle As WaitHandle = oResult.AsyncWaitHandle

' tell code to sleep here until process is complete
oHandle.WaitOne()

' process is complete
oConn.EndOpen(oResult)

Catching Asynchronous Processing Errors

One thing that the examples of asynchronous processing do not demonstrate is handling any errors that might occur. Connection errors are particularly common in data access applications, and you will probably decide to use a Try..Catch construct and error handling code in every case.

However, remember that in asynchronous processing situations, the error will be raised somewhere between calling the Beginxxx and Endxxx methods—rather than at the specific point where you call the Open method (for a Connection) or the Executexxx method (for a Command) when using synchronous processing. This means that you must enclose the complete section of code in a Try..Catch construct and then use smaller nested constructs to catch specific errors as and where required.