There are times when a tight coupling exists between user interaction with an application and database calls. That is, a user initiates an action that requires a database call, the database call executes, the call to the database returns, the user responds, and the cycle continues in fairly rapid succession. However, there are also times when a user may initiate an action that requires a lengthy database call. Of course, lengthy is a relative term and would be defined within your own environment. What is lengthy to one may not be lengthy to another. Something of a judgement call on that one.
If a lengthy database call is initiated from an application you may desire the capability to continue to do other work within that application while the database call executes. However, as current production versions of the Oracle Data Provider for .NET (ODP.NET) do not offer BeginXXX and EndXXX methods (such as BeginExecuteNonQuery() for example), you may think offering this functionality in your ODP.NET application is not possible or is difficult. Again, depending on your application specifics, offering such asynchronous behavior may give your application better responsiveness and increase the end-user experience positively.
One method of achieving this goal (calling a synchronous method asynchronously) is by using delegates. If you are not familiar with delegates, I suggest reviewing the Delegates topic in the online version of the C# Programming Guide on Microsoft's MSDN site. In addition to the standard reference information, there is also a tutorial (again located on MSDN) which can be found here. Of course these links are subject to change in the future.
In order to implement this technique you will need two methods (other than the Main method of course). One method will perform the synchronous database call and the other method will be invoked when that call is complete. A delegate is created for the method that performs the database call. This is the key to the technique. By creating a delegate for this synchronous method it becomes possible to invoke it asynchronously via the BeginInvoke() method exposed by the delegate. The delegate keyword actually causes the compiler to expand the declaration into a class which contains a BeginInvoke() method (among others).
The sample code below is a simple implementation of this technique using a console application. In this application a "do nothing" call to the database occurs. This call does nothing more than sleep inside the database for a specified period of time. As noted in the source code comments, the database user must have the "execute" privilege on the dbms_lock PL/SQL package for the code to work. Of course a real application would not normally connect to the database only to sleep for a period of time and return; however, this is a sufficient method for illustrating that the call is asynchronous in relation to the main thread.
The basic flow is as follows:
- Create the delegate
- Invoke the delegate to execute asynchronously from the main thread
- Continue processing in the main thread while the database call is in progress
- When database call is complete, invoke the OnCallComplete() method
- Work in the main thread continues for a short period of time
- Along the way prompt information is displayed and a final prompt is provided
It is necessary to cause the main thread to have a longer duration than the thread performing the database call because if the main thread ended before the database call thread the entire console application would terminate.
When the application is executed you should see output similar to the following:
Asynchronous call in progress.
Will sleep in database for 10 seconds.
Doing work in Main thread...
Enter LongDatabaseCall
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Exit LongDatabaseCall
Enter OnCallComplete
Perform post-database call actions...
Exit OnCallComplete
Doing work in Main thread...
Doing work in Main thread...
Doing work in Main thread...
Press ENTER to continue...
As you can see, the basic flow above is represented here and work in the main thread continues after the LongDatabaseCall method has been invoked via the delegate.
Here's the (hopefully) well-commented code to produce the above:
using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace AsynchronousDemo
{
class Program
{
// change as necessary for your environment
// the user must have execute privilege on dbms_lock
const string constr = "User Id=hr;" +
"Password=hr;" +
"Data Source=orademo;" +
"Enlist=false";
// the delegate for the function that makes the db call
internal delegate void LongCallDelegate(int sleepSeconds);
static void Main(string[] args)
{
// the amount of time the call to the database will sleep
int sleepSeconds = 10;
// write marker text to console to indicate beginning of process
Console.WriteLine("Asynchronous call in progress.");
Console.WriteLine(" Will sleep in database for {0} seconds.", sleepSeconds.ToString());
// used to call the function
LongCallDelegate ldc = new LongCallDelegate(LongDatabaseCall);
// call BeginInvoke to make call asynchronous
// the method to invoke when the call has completed is passed
// as the second parameter
ldc.BeginInvoke(sleepSeconds, OnCallComplete, ldc);
// cause main thread to "pause" while the database call is in progress
// add an arbitrary number (5) to the sleepSeconds to help show that
// the database call is "independent" of the main thread
for (int i = 1; i < sleepSeconds + 5; i++)
{
Console.WriteLine(" Doing work in Main thread...");
Thread.Sleep(1000);
}
// prevent console from automatically closing as when run from VS in debug mode
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
}
static void LongDatabaseCall(int sleepSeconds)
{
// this is the function that makes the database call
// it will be called from the delegate to make the call
// asynchronous in relation to the main thread
// marker text
Console.WriteLine(" Enter LongDatabaseCall");
// create and open connection to database
OracleConnection con = new OracleConnection(constr);
con.Open();
// create command object
// will call dbms_lock to sleep for x seconds in database
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "begin dbms_lock.sleep(:1); end;";
// parameter for the sleep duration
OracleParameter p_sleepSeconds = new OracleParameter("1",
OracleDbType.Decimal,
sleepSeconds,
ParameterDirection.Input);
// add parameter to the collection for the command object
cmd.Parameters.Add(p_sleepSeconds);
// make the database call
cmd.ExecuteNonQuery();
// clean up
p_sleepSeconds.Dispose();
cmd.Dispose();
con.Dispose();
// marker text
Console.WriteLine(" Exit LongDatabaseCall");
}
static void OnCallComplete(IAsyncResult ar)
{
// marker text
Console.WriteLine(" Enter OnCallComplete");
// get the delegate from the IAsyncResult object
LongCallDelegate ldc = (LongCallDelegate)ar.AsyncState;
// Must call EndInvoke to pair with BeginInvoke
ldc.EndInvoke(ar);
// marker text
Console.WriteLine(" Perform post-database call actions...");
Console.WriteLine(" Exit OnCallComplete");
}
}
}
