Asynchronous Query Execution
On this page
dotConnect for QuickBooks Online supports a very useful feature: asynchronous execution of methods that
take most of the time in database applications. Traditionally, the main thread of the
application was blocked while opening a connection, transferring large blocks of
data, and other similar operations. Now you can entrust the execution of a query to a
background thread and concentrate on performing other operations, for example,
updating user interface elements or preparing another query for execution.
Understanding Asynchronous Methods
dotConnect for QuickBooks Online provides you with asynchronous methods for most of the time-consuming
operations: connecting, data retrieval, and DML statement execution. For every
synchronous method, two asynchronous methods are implemented, with the prefixes "Begin"
and "End" respectively. The table below summarizes the
new methods in the command object:
Table 1. Asynchronous methods
Class |
Synchronous method |
Asynchronous methods |
| Begin part |
End part |
| QuickBooksConnection |
Open |
BeginOpen |
EndOpen |
| QuickBooksCommand |
ExecuteReader |
BeginExecuteReader |
EndExecuteReader |
| QuickBooksCommand |
ExecuteNonQuery |
BeginExecuteNonQuery |
EndExecuteNonQuery |
A typical use of asynchronous methods looks like the following code:
|
|---|
IAsyncResult myResult = myCommand.BeginExecuteNonQuery(null, null);
...
rowsAffected = myCommand.EndExecuteNonQuery(myResult);
|
|
|---|
Dim myResult As IAsyncResult = myCommand.BeginExecuteNonQuery(Nothing, Nothing)
...
Dim rowsAffected As Int32 = myCommand.EndExecuteNonQuery(myResult)
|
First, the application calls the "Begin" method and passes it all the parameters
needed to accomplish the task. This method returns an IAsyncResult object that
represents the operation invoked and is used for later processing. After the
process has been invoked, the application can proceed to do its job.
Later, when the query has finished its execution, the application calls the "End"
method, passing it the IAsyncResult object returned by the "Begin" method that has
been invoked earlier.
If something goes wrong with the query, an exception is thrown only when
the application calls the "End" method.
Completion Signaling
The common synchronous method calls do not return until the operation is finished.
In the asynchronous cases, the begin call returns immediately, so there is a need
to determine when the operation is actually complete. dotConnect for QuickBooks Online provides you
with three ways to catch the query termination.
All of the methods listed in Table 1 accept at least two parameters. For
example, the DbCommandBase.BeginExecuteReader method is declared in the following way:
|
|---|
public IAsyncResult BeginExecuteReader(
AsyncCallback callback,
object stateObject,
CommandBehavior behavior
);
|
|
|---|
Public Function BeginExecuteReader( _
ByVal callback As AsyncCallback, _
ByVal stateObject As Object, _
ByVal behavior As CommandBehavior _
) As IAsyncResult
|
The way you get signaled about query termination depends on what values you pass to the "Begin" method:
- Callback: pass a delegate as the callback parameter, along with (optionally) a user-defined
state object stateObject. When this approach is used, dotConnect for QuickBooks Online will call that passed-in delegate
and make the state object available through the IAsyncResult object (passed as the second parameter to the delegate). To avoid calling a delegate, pass null (Nothing in Visual Basic) to the callback parameter.
- Synchronization objects: the IAsyncResult objects returned by the begin methods have a WaitHandle property that contains an event object. The event object can be used in synchronization primitives such as WaitHandle.WaitAny and WaitHandle.WaitAll. This allows the calling code to wait for multiple pending operations and be notified either when one or all of them finish.
- Polling: the IAsyncResult object also has an IsCompleted Boolean property. This property will change to true when the operation completes, so it can be used by code that needs to perform some continuous activity; that code can periodically check the property and, if it changes, process the results.
In any case, you must call the corresponding "End" method to continue using the connection that serves the operation. If you try to start another asynchronous operation on a connection that is already serving a "Begin" method, an exception will be thrown. If you do not call the corresponding "End" method, some system resources may leak. Note that calling the "End" method without waiting for the operation to complete is allowed. In this case, the method will block the current thread until the operation finishes.
Every asynchronous method that invokes an operation has a stateObject parameter. You can pass it any object you find useful, thus providing the callback routine with any information it may require. The value of stateObject is accessible through the IAsyncResult.AsyncState property.
Samples
A sample is provided below for each of the signaling methods.
The following sample starts an asynchronous execution of an UPDATE statement, then
periodically checks whether the operation is complete, while performing some
operations (it writes a dot to the console). Once the query is done, the routine returns
and the application is ready to close.
|
|---|
using System;
using Devart.Data.QuickBooks;
namespace MyNamespace
{
class Class1
{
static QuickBooksConnection myConnection = new QuickBooksConnection(
"Company Id=1440905870;Refresh Token=AB11590759756UCO8O6CpRQcAMP00sIANafgS8Y4RN10rpLJNM;Token Server=file://D:\\temp\\QuickBooksToken.txt");
static QuickBooksCommand myCommand = new QuickBooksCommand(
"UPDATE Customer SET Title = 'sample title 1' WHERE DisplayName = 'sample name 1'");
static void PerformAsync()
{
myCommand.Connection = myConnection;
myConnection.Open();
int rowsAffected;
try
{
IAsyncResult myResult = myCommand.BeginExecuteNonQuery(null, null);
Console.Write("In progress...");
while (!myResult.IsCompleted)
{
Console.Write(".");
//Perform any operation you need here
}
rowsAffected = myCommand.EndExecuteNonQuery(myResult);
Console.WriteLine();
Console.WriteLine("Operation complete. Rows Affected: " + rowsAffected);
}
catch
{
Console.WriteLine("Error during execution.");
}
finally
{
myConnection.Close();
}
}
static void Main(string[] args)
{
PerformAsync();
Console.WriteLine("About to exit.");
Console.ReadLine();
}
}
}
|
|
|---|
Imports Devart.Data.QuickBooks
Imports System
Module Module1
Dim myConnection As New QuickBooksConnection( _
"Company Id=1440905870;Refresh Token=AB11590759756UCO8O6CpRQcAMP00sIANafgS8Y4RN10rpLJNM;Token Server=file://D:\\temp\\QuickBooksToken.txt")
Dim myCommand As QuickBooksCommand = New QuickBooksCommand( _
"UPDATE Customer SET Title = 'sample title 1' WHERE DisplayName = 'sample name 1'")
Public Sub PerformAsync()
myCommand.Connection = myConnection
myConnection.Open()
Dim rowsAffected As Int32
Try
Dim myResult As IAsyncResult = myCommand.BeginExecuteNonQuery(Nothing, Nothing)
Console.Write("In progress...")
While Not myResult.IsCompleted
Console.Write(".")
'Perform any operation you need here
End While
rowsAffected = myCommand.EndExecuteNonQuery(myResult)
Console.WriteLine()
Console.WriteLine("Operation complete. Rows Affected: " & rowsAffected)
Catch
Console.WriteLine("Error during execution.")
Finally
myConnection.Close()
End Try
End Sub
Sub Main()
PerformAsync()
Console.WriteLine("About to exit.")
Console.ReadLine()
End Sub
End Module
|
This method is very effective when several database operations can be executed
simultaneously. The sample code opens two connections, then starts two asynchronous
executions and waits for either to finish. Once an operation is signaled complete,
the code processes the results of the operation. Afterwards, the application finishes the
second operation. Using this method, we reduce query execution time from the sum of the two
operations to the time of the slowest operation.
|
|---|
using System;
using Devart.Data.QuickBooks;
namespace MyNamespace
{
public class Class1
{
static void Main(string[] args)
{
//Set up objects required to execute the queries
QuickBooksConnection[] myConnections = new QuickBooksConnection[2];
QuickBooksCommand[] myCommands = new QuickBooksCommand[2];
IAsyncResult[] aResults = new IAsyncResult[2];
string conStr = "Company Id=1440905870;Refresh Token=AB11590759756UCO8O6CpRQcAMP00sIANafgS8Y4RN10rpLJNM;Token Server=file://D:\\temp\\QuickBooksToken.txt";
myConnections[0] = new QuickBooksConnection(conStr);
myCommands[0] = new QuickBooksCommand(
"UPDATE Customer SET Title = 'sample title 1' WHERE DisplayName = 'sample name 1'", myConnections[0]);
myConnections[1] = new QuickBooksConnection(conStr);
myCommands[1] = new QuickBooksCommand(
"UPDATE Customer SET Title = 'sample title 2' WHERE DisplayName = 'sample name 2'", myConnections[1]);
try
{
//Open the connections
myConnections[0].Open();
myConnections[1].Open();
//Invoke execution of both queries
aResults[0] = myCommands[0].BeginExecuteNonQuery(null, null);
aResults[1] = myCommands[1].BeginExecuteNonQuery(null, null);
Console.WriteLine("Waiting for operations to complete...");
//Wait for any of the queries to finish
int ind = System.Threading.WaitHandle.WaitAny(new System.Threading.WaitHandle[] {
aResults[0].AsyncWaitHandle,
aResults[1].AsyncWaitHandle });
//Process results of the query that finished first
Console.WriteLine("Result #{0}: {1}", ind,
myCommands[ind].EndExecuteNonQuery(aResults[ind]));
//Process results of another query
int anotherIndex = 1 - ind;
Console.WriteLine("Result #{0}: {1}", (anotherIndex),
myCommands[anotherIndex].EndExecuteNonQuery(aResults[anotherIndex]));
}
catch
{
Console.WriteLine("Error during a query.");
}
finally
{
myConnections[0].Close();
myConnections[1].Close();
}
Console.WriteLine("About to exit.");
Console.ReadLine();
}
}
}
|
|
|---|
Imports System
Imports Devart.Data.QuickBooks
Module Module1
Sub Main()
'Set up objects required to execute the queries
Dim myConnections(1) As QuickBooksConnection
Dim myCommands(1) As QuickBooksCommand
Dim aResults(1) As IAsyncResult
Dim conStr As String = "Company Id=1440905870;Refresh Token=AB11590759756UCO8O6CpRQcAMP00sIANafgS8Y4RN10rpLJNM;Token Server=file://D:\\temp\\QuickBooksToken.txt"
myConnections(0) = New QuickBooksConnection(conStr)
myCommands(0) = New QuickBooksCommand( _
"UPDATE Customer SET Title = 'sample title 1' WHERE DisplayName = 'sample name 1'", myConnections(0))
myConnections(1) = New QuickBooksConnection(conStr)
myCommands(1) = New QuickBooksCommand( _
"UPDATE Customer SET Title = 'sample title 2' WHERE DisplayName = 'sample name 2'", myConnections(1))
Try
'Open the connections
myConnections(0).Open()
myConnections(1).Open()
'Invoke execution of both queries
aResults(0) = myCommands(0).BeginExecuteNonQuery(Nothing, Nothing)
aResults(1) = myCommands(1).BeginExecuteNonQuery(Nothing, Nothing)
Console.WriteLine("Waiting for operations to complete...")
Dim waitHandles() As System.Threading.WaitHandle = { _
aResults(0).AsyncWaitHandle, aResults(1).AsyncWaitHandle}
'Wait for any of the queries to finish
Dim ind As Int32 = System.Threading.WaitHandle.WaitAny(waitHandles)
'Process results of the query that finished first
Console.WriteLine("Result #{0}: {1}", ind, _
myCommands(ind).EndExecuteNonQuery(aResults(ind)))
'Process results of another query
Dim anotherIndex As Int32 = 1 - ind
Console.WriteLine("Result #{0}: {1}", (anotherIndex), _
myCommands(anotherIndex).EndExecuteNonQuery(aResults(anotherIndex)))
Catch
Console.WriteLine("Error during a query.")
Finally
myConnections(0).Close()
myConnections(1).Close()
End Try
Console.WriteLine("About to exit.")
Console.ReadLine()
End Sub
End Module
|
This method is probably the most effective in many situations. The sample code initiates
establishment of two connections. A connection itself is passed as the
stateObject parameter. This allows a delegate to know which connection has finished
negotiation with user accounts and to end the asynchronous operation. Using this method, we
obtain the results of an operation once it is complete, without having to wait for the slower
operations that might precede it.
|
|---|
using System;
using Devart.Data.QuickBooks;
namespace MyNamespace
{
class Class1
{
static string conStr = "Company Id=1440905870;Refresh Token=AB11590759756UCO8O6CpRQcAMP00sIANafgS8Y4RN10rpLJNM;Token Server=file://D:\\temp\\QuickBooksToken.txt";
static QuickBooksConnection myConnection1 = new QuickBooksConnection(conStr);
static QuickBooksConnection myConnection2 = new QuickBooksConnection(conStr);
static void OperationComplete(IAsyncResult ar)
{
try
{
QuickBooksConnection currentConnection = (QuickBooksConnection)ar.AsyncState;
currentConnection.EndOpen(ar);
Console.WriteLine("Connection string: "+currentConnection.ConnectionString +
", server version: "+currentConnection.ServerVersion);
}
catch
{
Console.WriteLine("Error during opening.");
}
}
static void PerformAsync()
{
myConnection1.BeginOpen(new AsyncCallback(OperationComplete), myConnection1);
myConnection2.BeginOpen(new AsyncCallback(OperationComplete), myConnection2);
Console.WriteLine(
"Waiting for operations to complete. Hit Enter when both connections are established.");
Console.ReadLine();
myConnection1.Close();
myConnection2.Close();
}
static void Main(string[] args)
{
PerformAsync();
}
}
}
|
|
|---|
Imports System
Imports Devart.Data.QuickBooks
Module Module1
Dim connStr = "Company Id=1440905870;Refresh Token=AB11590759756UCO8O6CpRQcAMP00sIANafgS8Y4RN10rpLJNM;Token Server=file://D:\\temp\\QuickBooksToken.txt"
Dim myConnection1 As New QuickBooksConnection(connStr)
Dim myConnection2 As New QuickBooksConnection(connStr)
Public Sub OperationComplete(ByVal ar As IAsyncResult)
Try
Dim currentConnection As QuickBooksConnection = CType(ar.AsyncState, QuickBooksConnection)
currentConnection.EndOpen(ar)
Console.WriteLine("Connection string: " & currentConnection.ConnectionString & _
", server version: " & currentConnection.ServerVersion)
Catch
Console.WriteLine("Error during opening.")
End Try
End Sub
Public Sub PerformAsync()
myConnection1.BeginOpen(AddressOf OperationComplete, myConnection1)
myConnection2.BeginOpen(AddressOf OperationComplete, myConnection2)
Console.WriteLine( _
"Waiting for operations to complete. Hit Enter when both connections are established.")
Console.ReadLine()
myConnection1.Close()
myConnection2.Close()
End Sub
Sub Main()
PerformAsync()
End Sub
End Module
|