dotConnect for Adobe Commerce Documentation
dotConnect for Adobe Commerce supports a very useful feature - asynchronous execution of methods that take most of time in database applications. Traditionally, the main thread of the application went blocked during opening a connection, transferring large blocks of data, and other operations alike. Now you can entrust execution of a query to a background thread and concentrate on performing other operations, for example, an update of user interface elements, or on a preparation of another query for execution.
The article consists of following sections:
dotConnect for Adobe Commerce provides you with asynchronous methods for most of the time-consuming operations: connecting, data retrieving, and DML statement execution. For every synchronous method two asynchronous methods are implemented, with 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 | ||
| MagentoConnection | Open | BeginOpen | EndOpen |
| MagentoCommand | ExecuteReader | BeginExecuteReader | EndExecuteReader |
| MagentoCommand | ExecuteNonQuery | BeginExecuteNonQuery | EndExecuteNonQuery |
A typical usage of asynchronous methods looks like the following code.
First, application calls the "Begin" method and passes it all the parameters
needed to accomplish the task. This method returns IAsyncResult object that
represents the operation invoked and is used for later processing. After the
process has been invoked, application can proceed to do its job.
Later, when the query has finished its execution, application calls the "End"
method, passing it IAsyncResult object returned by the "Begin" method that has
been invoked earlier.
If something goes wrong with the query, the exception is thrown only when
application calls "End" method.
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 Adobe Commerce provides you with three ways to catch the query termination.
All of the methods listed in the table 1 accept at least two parameters. For example, DbCommandBase.BeginExecuteReader method is declared in the following way:
The way you get signalled about query termination depends on what values you pass to the "Begin" method.
In either case you must call corresponding "End" method to continue using the connection that serves the operation. If you try to start another asynchronous operation on connection that already serves 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 stateObject parameter. You can pass it any object you find useful thus providing the callback routine any information it may require. The value of stateObject is accessible through IAsyncResult.AsyncState property.
A sample is provided below for each of the signalling methods.
The following sample starts an asynchronous execution of UPDATE statement, then periodically checks whether the operation is complete, while performing some operations (writes a dot to console). Once query is done, the routine returns and application is ready to close.
using System;
using Devart.Data.Magento;
namespace MyNamespace
{
class Class1
{
static MagentoConnection myConnection = new MagentoConnection(
"domain=192.168.10.68/magento;user=Test;apikey=testpassword;");
static MagentoCommand myCommand = new MagentoCommand(
"UPDATE Customers SET Email = '[email protected]' WHERE LastName = '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 here any operation you need
}
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.Magento
Imports System
Module Module1
Dim myConnection As New MagentoConnection( _
"domain=192.168.10.68/magento;user=Test;apikey=testpassword;")
Dim myCommand As MagentoCommand = New MagentoCommand( _
"UPDATE Customers SET Email = '[email protected]' WHERE LastName = '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 here any operation you need
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 results of the operation. Afterwards the application finishes the second operation. Using this method, we reduce query execution time from sum of two operations to the time of the slowest operation.
using System;
using Devart.Data.Magento;
namespace MyNamespace
{
public class Class1
{
static void Main(string[] args)
{
//Set up objects required to execute the queries
MagentoConnection[] myConnections = new MagentoConnection[2];
MagentoCommand[] myCommands = new MagentoCommand[2];
IAsyncResult[] aResults = new IAsyncResult[2];
string conStr = "domain=192.168.10.68/magento;user=Test;apikey=testpassword;";
myConnections[0] = new MagentoConnection(conStr);
myCommands[0] = new MagentoCommand(
"UPDATE Customers SET Email = '[email protected]' WHERE LastName = 'sample name 1'", myConnections[0]);
myConnections[1] = new MagentoConnection(conStr);
myCommands[1] = new MagentoCommand(
"UPDATE Customers SET Email = '[email protected]' WHERE LastName = '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.Magento
Module Module1
Sub Main()
'Set up objects required to execute the queries
Dim myConnections(1) As MagentoConnection
Dim myCommands(1) As MagentoCommand
Dim aResults(1) As IAsyncResult
Dim conStr As String = "domain=192.168.10.68/magento;user=Test;apikey=testpassword;"
myConnections(0) = New MagentoConnection(conStr)
myCommands(0) = New MagentoCommand( _
"UPDATE Customers SET Email = '[email protected]' WHERE LastName = 'sample name 1'", myConnections(0))
myConnections(1) = New MagentoConnection(conStr)
myCommands(1) = New MagentoCommand( _
"UPDATE Customers SET Email = '[email protected]' WHERE LastName = '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 stateObject parameter. This allows a delegate to know what connection has finished negotiation with user accounts and to end the asynchronous operation. Using this method, we obtain results of an operation once it is complete, without having to wait for the slower operations that might precede it.