Asynchronous Oracle Query Execution in .NET

dotConnect for Oracle 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 trust 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.

Understanding Asynchronous Methods

dotConnect for Oracle provides you with asynchronous methods for most of the time-consuming operations: connecting, data retrieving, and execution of DML statements. 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
OracleConnection	Open	BeginOpen	EndOpen
OracleCommand	ExecuteReader	BeginExecuteReader	EndExecuteReader
OracleCommand	ExecuteNonQuery	BeginExecuteNonQuery	EndExecuteNonQuery
OracleDataTable	Fill	BeginFill	EndFill

A typical usage of asynchronous methods looks like the following code.

[C#]

IAsyncResult myResult = myCommand.BeginExecuteNonQuery(null, null);
...
rowsAffected = myCommand.EndExecuteNonQuery(myResult);

[Visual Basic]

Dim myResult As IAsyncResult = myCommand.BeginExecuteNonQuery(Nothing, Nothing)
...
Dim rowsAffected As Int32 = myCommand.EndExecuteNonQuery(myResult)

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.

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 the table 1 accept at least two parameters. For example, DbCommandBase.BeginExecuteReader method is declared in the following way:

[C#]

public IAsyncResult BeginExecuteReader(
   AsyncCallback callback,
   object stateObject,
   CommandBehavior behavior
);

[Visual Basic]

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 callback parameter, along with (optionally) a user-defined state object stateObject. When this approach is used, dotConnect for Oracle 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 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.

Transparent Asynchronous Fetch

The OracleDataTable component provides even more features in the area of asynchronous execution. In addition to BeginFill and EndFill methods, it allows you to pause (SuspendFill), continue (BeginFill again), or stop (CancelFetch) the fetch process. Moreover, the component has NonBlocking property, which turns common fill into 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.

Halting Execution

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

Because OracleCommand.Cancel() method uses server-side technology it is impossible to stop opening 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 connection that is used in operation to halt and invoke Cancel() method in another thread.

Samples

A sample is provided below for each of the signaling methods.

Polling

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.

[C#]

using System;
using Devart.Data.Oracle;

namespace MyNamespace
{
  class Class1
  {
    static OracleCommand myCommand = new OracleCommand(
	  "UPDATE Emp SET Sal = Sal + 500 WHERE Sal < 2000");
    static OracleConnection myConnection = new OracleConnection(
	  "User Id=Scott;Password=tiger;Data Source=Ora");

    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();
    }
  }
}

[Visual Basic]

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

Synchronization objects

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.

[C#]

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 < 3000", 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();
    }
  }
}

[Visual Basic]

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 < 3000", 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

Callback

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 stateObject parameter. This allows a delegate to know what connection has finished negotiation with servers 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.

[C#]

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();
    }
  }
}

[Visual Basic]

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

Back to list