Asynchronous Command Execution in ADO.Net 2.0

In previous versions of .Net, it is not possible to execute a command against a database asynchronously but it was possible to simulate it using the ThreadPool class in previous versions. To be more clear on the previous line, the execution of the following lines of code will be synchronous,
SqlDataReader dr1 = com.ExecuteReader();
SqlDataReader dr2 = com.ExecuteReader();
ie. the thread that is executing line1 will wait until the database operations is complete and proceeds the line2 execution. With the arrival ADO.Net 2.0 it is possible to execute asynchronously by proceeding with the execution of other lines of code after start executing a database operations.

Making Async to work:
This is achieved by the BeginExecuteReader()and EndExecuteReader() method that is packed with Command Object. The BeginExecuteReader() method will return a IAsyncResult object,which is a reference that helps to detect the end of the execution of the database operation.So it is given as a parameter to EndExecuteReader() method which in turn will return the DataReader object.
IAsyncResult ar1 = com1.BeginExecuteReader(null, null);
IAsyncResult ar2 = com2.BeginExecuteReader(null, null);
//Proceed with the other execution
dr1 = com1.EndExecuteReader(ar1);
dr2 = com2.EndExecuteReader(ar2);
The thread that is executing the line1 continues executing the other lines of code(line2 and etc..)without waiting for the database operations to complete.This is called Asynchronous behavior.But the line3 and line4 will be executed synchronously ie, the thread will wait for the line3 to complete execution and proceed with the line4.Note that the callback will be called on a thread which will be in the thread pool,will be different from the one which initiated the asynchronous operation so proper synchronisation should be done.It is done using ar1.AsyncWaitHandle property.This we will see in the next article.

For the Asynchronous operation to work we have to set “Asynchronous Processing=true” in the connection string.If in a same application you are using Asysnchronous and synchronous operation, it is better to use separate connection string for both one with “Asynchronous Processing=true” and other with “Asynchronous Processing=false”.Because using the connection string with Async enabled for synchronous operation will hit the performance some what.
The following table will list the Asynchronous command that is packed with ADO.Net 2.0 .

Synchronous Methods Asynchronous Methods
Start Method End Method
ExecuteReader() BeginExecuteReader() EndExecuteReader()
EndExecuteNonQuery() BeginExecuteNonQuery() EndExecuteNonQuery()
ExecuteXmlReader() BeginExecuteXmlReader() EndExecuteXmlReader()
To be Continued….

Leave a Reply

Your email address will not be published. Required fields are marked *