July 24, 2011, 1:38 a.m.
posted by pitbull
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.
' 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.
' 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.
' 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.
' 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.
... ' 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.
' 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.
... ' 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.)
' 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.
... 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.
' 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.
' 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.
' 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.