Choose an Appropriate Overload
Next you choose the appropriate overload, based on your needs. In this case, the code uses BeginExecuteReader
. The first parameter is an AsyncCallBack reference that invokes a registered method when query execution completes. The second parameter is the stateObject
parameter (see Table 1) which will be available as AsyncState in the callback method. The third is the familiar CommandBehavior
value. The call to BeginExecuteReader
returns control back to the caller immediately, so that tasks on the current thread are not blocked.
Dim IAsync As IAsyncResult = cmd.BeginExecuteReader( _
callback, cmd, CommandBehavior.CloseConnection)
From the client end the AsyncCall
method invokes the server's asynchronous method as seen below:
Dim objDataStore As New DataStore
Dim asyncCustomerContext As IAsyncResult = objDataStore.PopulateDataAsynchronously( _
"Select * from Customers", _
Dim asyncProcessContext As IAsyncResult = _
"Select * from Orders", AddressOf ProcessOrders)
IsAsync = True
The code makes two asynchronous data calls to retrieve the Customers and Orders tables. Each call includes the appropriate callback method which ADO will invoke via delegates. Here's an example that shows how to bind the list of customers.
Private Sub ProcessCustomers(ByVal asyncCustomer _
IsAsync = True
Dim dt As New DataTable()
' Always check this;
' otherwise you'll run into trouble
'gets the state object
Dim commandObj As SqlCommand = _
'do the endinvoke
Dim reader As SqlDataReader = _
'callback and bind on the orginal thread
Dim BindCustomerCallback As BindData
BindCustomerCallback = New BindData( _
Catch ex As Exception
Dim throwError As New ErrorMessage( _
IsAsync = False
method gets an IAsyncResult instance as a parameter and uses that to retrieve the SqlCommand object that executes the EndExecuteReader
method, passing in the IAsyncResult reference. That call returns a SqlDataReader object which you can load into a DataTable using the Load
is an overloaded method that takes an IDataReader parameter. If the DataTable is already bound to data then it will merge the new incoming rows with the existing data; however, you can control how the DataTable merges data using the overloaded Load
version that accepts a LoadOption
parameter. After loading, you can use the DataTable as the data source for the DataGridView.
|Figure 3. Fetching Data Asynchronously: The figure shows how the form continues to respond, displaying a status message and letting users continue to interact with the Customer grid on the form even while the code performs an asynchronous data call to get the Orders data. |
The issue you now have is that the callback method now runs on a worker thread while a different thread owns the DataGrid control. You must ensure that controls on your form are accessed only from the thread which created them. Fortunately, one easy way to do that is to use the Me.Invoke
accepts a delegate that it calls in the control's execution contextwhich means you can now bind the DataTable to the DataGridView by using the following delegate declaration in the form.
Private Delegate Sub BindData(ByVal dt As DataTable)
Private Delegate Sub ErrorMessage( _
ByVal message As String)
In addition to the BindData
delegate the class declares another delegate, called ErrorMessage
. The ErrorMessage
delegate accepts a string parameter used to display error messages. Both delegates point to appropriate method references to bind the data or show the error messages. The sample code uses a similar model to bind Order information to a DataGridView and handle error messages.
For comparison, the SyncCall
method in the sample code implements a synchronous mechanism to load the DataGridView. You should run both the asynchronous and synchronous models to get a feel for the difference in the interface's responsiveness. In asynchronous mode, users will see a friendly status bar message as the first DataGridView loads (see Figure 3
). However, using the synchronous mode, the user window stops responding until both the DataGridViews are bound. Further, in synchronous mode you can't use the status bar, because the form can't display status messages until the execution context returns.
variable prevents the asynchronous task from being re-executed while an existing task is pending completion. For complex database operations you need to make sure you're using proper locking techniques while using the SqlCommand.BeginInvoke
methods. Also note that you must always call EndInvoke
on any SqlCommand object instance where you've already called BeginInvoke
call before using the same SqlCommand object for a different query.