As we already know that the object command run synchronously as default query processing. In ADO.NET 2.0 Microsoft has been added the feature to fulfill the requirement for the asynchronous processing. The process signed with the BeginExecute...and EndExecute...method, for example if we want to execute the query for the Select statement via DataReader we should use BeginExecuteReader and EndExecuteReader method instead of ExecuteReader.
There are some steps we have to do for doing asynchronous processing. One of the step is add the "Asynchronous Processing=True" attribute in the connection string. The .net runtime environment will knows what he has to do. The other steps are creating the delegate to create the object from the delegate for passing into the Invoke method as the parameter in the final asynchronous process. Calling the Invoke method is the must. It moves the result of async processing from the thread to the windows main thread. The Invoke method takes two argument to passed by. The first argument is the delegate object, the second is the array of objects to pass to the method. If the method contains no parameter or argument then the value of the object array can be set to null.
In this article i will show you the simple example to do the async processing of the command object. The scenario is executing the query that takes long time to finished. We can create a tricky procedure to accomplish this by writing the stored procedure contains the "WAITFOR DELAY" function to stop the executing query for a moment. For briefly, let us consider the whole following steps below :
1. Create the stored procedure that takes long time to finished :
It simply stop the execution just for 10 second, after that the query continue processing.
2. Create a simple windows application project. Put a button and datagridview control onto the form. The scenario is we want to execute the query from the stored procedure above via SqlDataReader asynchronously. The SqlDataReader will be used by DataTable as datasource for DataGridView control for Binding. Let's see the code fragment :
1: Imports System.Data.SqlClient
2:
3: Public Class frmAsyncCommand
4:
5: Private sqlCon As SqlConnection
6: Private sqlConnBuilder As SqlConnectionStringBuilder
7: Private sqlCmd As SqlCommand
8:
9: Private Delegate Sub GetDataDelegate(ByVal YourTable _
10: As DataTable)
I use the new SqlConnectionStringBuilder class for giving us a strongly typed connection string. The GetDataDelegate sub created as Delegate that will be used by the Invoke method as parameter.
1: Private Sub GetDataFromOrderDetails(ByVal YourTable _
2: As DataTable)
3: Me.DataGridView1.DataSource = Nothing
4: Me.DataGridView1.DataSource = YourTable
5: End Sub
the GetDataFromOrderDetails sub is the procedure for passing into the Invoke method as the delegate object parameter. Consider that the signature has the same with the delegate sub created before. It just simply do the data binding for DataGridView control.
1: Private Sub EndOfAsyncProc(ByVal ar As IAsyncResult)
2: Dim sqlCmd As SqlCommand = CType(ar.AsyncState, _
3: SqlCommand)
4: Dim sqlDr As SqlDataReader = sqlCmd.EndExecuteReader(ar)
5:
6: Dim dtOrderDetails As New DataTable
7: dtOrderDetails.Load(sqlDr)
8:
9: sqlCmd.Dispose()
10: sqlDr.Close()
11: sqlCon.Close()
12:
13: Me.Invoke(New GetDataDelegate(AddressOf _
14: GetDataFromOrderDetails), New Object() {dtOrderDetails})
15: End Sub
the main duty in the EndOfAsyncProc procedure is converting async result to the previous object, that is SqlCommand, then finishing the ExecuteReader with call EndExecuteReader. The Invoke method take the final step to move the process from the thread back into the windows main thread. It give us no meaningfull process if we do not call the Invoke method due to the separating thread.
The last is a procedure to create asynchronous processing. We only just add the "AsynchronousProcessing" property to the connection string via SqlConnectionStringBuilder class and set the value to true. The async processing begin with calling BeginExecuteReader method from command object and passing the procedure as the argument to the method that will be executed when the thread is finish...
1: Private Sub btnAsyncCommand_Click(ByVal sender As _
2: System.Object, ByVal e As System.EventArgs) _
3: Handles btnAsyncCommand.Click
4: sqlConnBuilder = New SqlConnectionStringBuilder
5: With sqlConnBuilder
6: .DataSource = ".\SQLDEV2K5"
7: .InitialCatalog = "Northwind2K5"
8: .IntegratedSecurity = True
9: .AsynchronousProcessing = True
10: End With
11: sqlCon = New SqlConnection(sqlConnBuilder.ToString)
12:
13: If sqlCon.State <> ConnectionState.Open Then sqlCon.Open()
14:
15: Using sqlCmd As New SqlCommand
16: sqlCmd.CommandType = CommandType.StoredProcedure
17: sqlCmd.Connection = sqlCon
18: sqlCmd.CommandText = "LongRunningQuery"
19: sqlCmd.BeginExecuteReader(AddressOf EndOfAsyncProc, _
20: sqlCmd, CommandBehavior.CloseConnection)
21: End Using
22: End Sub
23: End Class