dotConnect for Oracle Documentation
On this page
    Asynchronous Query Execution
    On this page

    dotConnect for Oracle 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 Oracle 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
    OracleConnection Open BeginOpen EndOpen
    OracleCommand ExecuteReader BeginExecuteReader EndExecuteReader
    OracleCommand ExecuteNonQuery BeginExecuteNonQuery EndExecuteNonQuery
    OracleDataTable Fill BeginFill EndFill

    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 Oracle 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:

    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.

    Transparent Asynchronous Fetch

    The OracleDataTable component provides even more features in the area of asynchronous execution. In addition to the BeginFill and EndFill methods, it allows you to pause (SuspendFill), continue (BeginFill again), or stop (CancelFetch) the fetch process. Moreover, the component has the NonBlocking property, which turns a common fill into a transparent asynchronous operation. To capture the end moment of the operation in a non-blocking fetch, watch for the SyncRoot property or handle the FetchFinished event.

    For more information on the OracleDataTable component, see OracleDataTable Advanced Features.

    Halting Execution

    dotConnect for Oracle allows you to stop query execution with the OracleCommand.Cancel method. The method instructs the Oracle server to halt any request from the connection associated with the command. This method returns without errors, while all stopped executions raise the exception "ORA-01013: user requested cancel of current operation". Like any other exception, this one is thrown only when you invoke the EndExecuteNonQuery or EndExecuteReader methods. Once the server halts execution, completion is signaled: the callback delegate is invoked, the synchronization object is triggered, and the IsCompleted property is true.

    Because the OracleCommand.Cancel() method uses server-side technology, it is impossible to stop opening a connection with BeginOpen()...EndOpen(). This method is available for Oracle server 9 and newer.

    Note that, unlike all other methods, OracleCommand.Cancel() can be called in the middle of asynchronous execution. Moreover, this is a thread-safe method, so you can stop a common (synchronous) execution. To do this, attach an OracleCommand object to the connection that is used in the operation to halt, and invoke the Cancel() method in another thread.

    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.Oracle;
    
    namespace MyNamespace
    {
      class Class1
      {
        static OracleConnection myConnection = new OracleConnection(
          "User Id=Scott;Password=tiger;Data Source=Ora;");
        static OracleCommand myCommand = new OracleCommand(
          "UPDATE Emp SET Sal = Sal + 500 WHERE Sal < 2000");
    
        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.Oracle
    Imports System
    
    Module Module1
    
      Dim myConnection As New OracleConnection( _
          "User Id=Scott;Password=tiger;Data Source=Ora;")
      Dim myCommand As OracleCommand = New OracleCommand( _
        "UPDATE Emp SET Sal = Sal + 500 WHERE Sal < 2000")
    
      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.Oracle;
    
    namespace MyNamespace
    {
    
      public class Class1
      {
    
        static void Main(string[] args)
        {
          //Set up objects required to execute the queries
          
          OracleConnection[] myConnections = new OracleConnection[2];
          OracleCommand[] myCommands = new OracleCommand[2];
          IAsyncResult[] aResults = new IAsyncResult[2];
          string conStr = "User Id=Scott;Password=tiger;Data Source=Ora;";
          myConnections[0] = new OracleConnection(conStr);
          myCommands[0] = new OracleCommand(
    	    "UPDATE Dept SET Loc='LA' WHERE DeptNo=10", myConnections[0]);
          myConnections[1] = new OracleConnection(conStr);
          myCommands[1] = new OracleCommand(
    	    "UPDATE Emp SET Sal = Sal + 500 WHERE Sal < 2000", 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.Oracle
    
    Module Module1
    
      Sub Main()
        'Set up objects required to execute the queries
        Dim myConnections(1) As OracleConnection
        Dim myCommands(1) As OracleCommand
        Dim aResults(1) As IAsyncResult
        Dim conStr As String = "User Id=Scott;Password=tiger;Data Source=Ora;"
        myConnections(0) = New OracleConnection(conStr)
        myCommands(0) = New OracleCommand( _
    	  "UPDATE Dept SET Loc='LA' WHERE DeptNo=10", myConnections(0))
        myConnections(1) = New OracleConnection(conStr)
        myCommands(1) = New OracleCommand( _
    	  "UPDATE Emp SET Sal = Sal + 500 WHERE Sal < 2000", 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 with different servers. A connection itself is passed as the stateObject parameter. This allows a delegate to know which connection has finished negotiation with servers 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.Oracle;
    
    namespace MyNamespace
    {
      class Class1
      {
        static OracleConnection myConnection1 = new OracleConnection(
            "User Id=Scott;Password=tiger;Data Source=Ora");
        static OracleConnection myConnection2 = new OracleConnection(
            "User Id=Scott;Password=tiger;Data Source=Ora_Another");
    
        static void OperationComplete(IAsyncResult ar)
        {
          try
          {
            OracleConnection currentConnection = (OracleConnection)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.Oracle
    
    Module Module1
    
      Dim myConnection1 As New OracleConnection( _
         "User Id=Scott;Password=tiger;Data Source=Ora")
      Dim myConnection2 As New OracleConnection( _
         "User Id=Scott;Password=tiger;Data Source=Ora_Another")
    
      Public Sub OperationComplete(ByVal ar As IAsyncResult)
        Try
          Dim currentConnection As OracleConnection = CType(ar.AsyncState, OracleConnection)
          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
    

    See Also

    OracleCommand Class  | OracleConnection Class  | OracleDataTable Class