CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ADO Article » ADO.Net 2.0 Features - Asynchronous SqlCommand Execution   You are not logged in.
Search
 

Technologies
 

Sponsors
 

CodeDigest Navigation
 

Technology News
 

Community News
No News Feeds available at this time.
 
ADO.Net 2.0 Features - Asynchronous SqlCommand Execution

By Satheesh babu
Posted On Dec 04,2008
Article Rating: (Login)
Average Rating: 5
No of Ratings: 1
No of Comments: 4
Print this article.
Category: ADO.Net 2.0

Subscribe to our feed!

ADO.Net 2.0 Features - Asynchronous SqlCommand Execution

 

Introduction

My Previous articles discussed about the new features that was packed in ADO.Net 2.0. In this article, we will understand how to implement asynchronous operation in ado.net. In previous versions of .Net, it is not possible to execute a command asynchronously against a database. Using this feature, we can execute bulk database operations asynchronously against a database. To understand asynchronous programming we should first understand synchronous operation which is the general paradigm in ADO.Net.

 

Synchronous Database Execution

Consider the below listing of code (Listing 1 - Synchronous), which is purely synchronous.

Listing 1 - Synchronous

SqlDataReader dr1 = com.ExecuteReader();

//Other lines of codes
SqlDataReader dr2 = com.ExecuteReader();

 

Synchronous means, the thread that is executing line1 will wait until the database operations is complete and proceed with the execution of line2. If the database operation is really huge, then the execution will not proceed with the other lines of code until these huge database operations are completed. This makes the application to respond slower and therefore the users might feel the application is not performing well. This degradation can be mitigated with the new extensions of ADO.Net 2.0 which is called Asynchronous ADO.Net commands. With this, it is possible to execute massive database operations asynchronously in a separate thread by proceeding current execution of other lines of code without waiting for the massive database operations to complete. We will see this in detail in coming sections.

 

Beginning Asynchronous Database Execution

The command object of ADO.Net 2.0 is packed with the required methods to make this new feature available. The main operations that can be performed through command object are,

Listing 2 - Command methods

ExecuteNonQuery()

ExecuteReader()

ExecuteScalar()

ExecuteXmlReader()

Asynchronous model is supported for all the above operations except ExecuteScalar() method which will return the first row’s first column value. For implementing asynchronous operation, command object has a begin method and an end method for each of their synchronous counterparts. The begin method will be called to start the execution while the end method will be called when the execution is completed. Refer the figure (Figure 1 - Available Asynchronous methods) for Asynchronous counterparts of the above operations.

Figure 1 - Available Asynchronous methods

 

Async in action

I will implement asynchronous execution with DataReader object in this example.

 

Prerequisites

For the Asynchronous operation to work we have to set “Asynchronous Processing=true” or “Async=true” in the connection string. Without this attribute set in connection string, the ADO.Net will give the following error.

"This command requires an asynchronous connection. Set "Asynchronous Processing=true" in the connection string."

 If in a same application you are using both Asynchronous and synchronous operation it is better to use separate connection string i.e. 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 connection string will be like,

"Data Source=.\SQLEXPRESS;Asynchronous Processing=true;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;"

 

Listing 3 - Async in action

con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

con1.Open();

com1 = new SqlCommand("SELECT emp_id, fname, lname, job_id, hire_date FROM [employee] ORDER BY emp_id, fname", con1);

 

con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

con2.Open();

com2 = new SqlCommand("waitfor delay '00:00:06';SELECT * FROM Authors", con2);

 

 IAsyncResult ar1 = com1.BeginExecuteReader(); // Async line 1

 IAsyncResult ar2 = com2.BeginExecuteReader(); // Async line 2

 //Perform some execution here

 //Perform some execution here

 dr1 = com1.EndExecuteReader(ar1); //Sync line 1         

 dr2 = com2.EndExecuteReader(ar2); //Sync line 2

 

The thread that is executing the “Async line 1” continues executing the other lines of code(“Async line 2” and etc..) in the above code(Listing 3 – Async in action) without waiting for the database operations to complete. It means the database operation is given to different thread and the current thread follows executing next line i.e. “Async line 2”. This is called Asynchronous behavior. But the “Sync line 1” and “Sync line 2” will be executed synchronously i.e., the thread will wait for the “Sync line 1” to complete execution and proceed with the “Sync line 2”.  I have used "waitfor delay '00:00:06'"in the query to demonstrate the example as an alternative to complex stored procedure which takes sometime to complete execution. Note “waitfor” statement will work only in Sql server 2005.

Thus we have learnt to use the Asynchronous operations at a beginner level. Moving forward we will leverage more features that are packed with asynchronous operations that can be used in some complicated scenarios.

 

Advanced scenarios with Asynchronous operations

Sometimes, we may need to populate data in different grids from different tables that may take sometime to complete because it is bulk. If we begin to populate the data in traditional way, we need to fetch the data one by one synchronously i.e. waiting for one database operations to complete to proceed with the other. We can consider using asynchronous model in these types of scenarios to bind the grid and thus we can prevent the waiting time for binding the grids.

 

To bind the grid we will be requiring the resultset which will be available only if the database operation is complete i.e. if it is datareader the result will be available only by calling EndExecuteReader() if database operation is complete. We need a mechanism where we can detect the end of database operation and hence bind the grid with the resultset.

 

This can be achieved by 3 ways

1.      Through WaitHandle class

2.      Through Callback delegate

3.      Polling

 

Calling End method without detecting the complete signal will make the execution wait there until it is completed.

 



ASP.Net Hosting

Recent Articles

WaitHandle class

WaitHandle class in System.Threading namespace comes to our rescue in these scenarios to detect the completion signal from the asynchronous thread and complete the data binding.

 

WaitHandle class has 2 methods that take array of WaitHandle objects,

Listing 4 - WaitHandle methods

WaitHandle.WaitAll()

WaitHandle.WaitAny()

WaitHandle.SignalAndWait()

 

The first method WaitAll() will wait for all the asynchronous operation to complete and return a Boolean indicating the completion of all the opeartion while next method WaitAny() will gives us any one index of WaitHandle array indicating that it is completed. SignalAndWait() method signals one WaitHandle object and waits on another, as an atomic operation. Most commonly used methods are WaitAll() and WaitAny().

 

 For making this work we need the WaitHandle object of each asynchronous operation. The IAsyncResult object which is returned by Begin method has a property called AsyncWaitHandle that will give us WaitHandle object to know the completion of the current asynchronous operation. Refer the code below.

Listing 5 - WaitHandle methods

IAsyncResult ar1 = com1.BeginExecuteReader();

WaitHandle handles = ar1.AsyncWaitHandle;

 

The next section will explain the use of WaitHandle object in detail.

 

Using WaitHandle Object

Refer the example code with this article to have a better understanding.

Listing 5 - WaitHandle implementation

  try

        {

            con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            con1.Open();    

            com1 = new SqlCommand("SELECT emp_id, fname, lname, job_id, hire_date FROM employee ORDER BY emp_id, fname",          con1);

            

            con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            con2.Open();

            com2 = new SqlCommand("waitfor delay '00:00:06';SELECT * FROM Authors", con2);

                     

            con3 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            con3.Open();

            com3 = new SqlCommand("waitfor delay '00:00:06';SELECT * from Department", con3);

 

            IAsyncResult ar1 = com1.BeginExecuteReader();

            IAsyncResult ar2 = com2.BeginExecuteReader();

            IAsyncResult ar3 = com3.BeginExecuteReader();

 

            WaitHandle[] handles = new WaitHandle[3];

            handles[0] = ar1.AsyncWaitHandle;

            handles[1] = ar2.AsyncWaitHandle;           

            handles[2] = ar3.AsyncWaitHandle;

           

            for (int results = 0; results < handles.GetLength(0); results++)

            {

                // wait for any handle, then process results as they come

                int index = WaitHandle.WaitAny(handles, 8000, false); // 8 secs

               

                if (WaitHandle.WaitTimeout == index)

                    throw new Exception("Timeout Exception");

                if (index == 0)

                {

                    dr1 = com1.EndExecuteReader(ar1);

                    gvEmployee.DataSource = dr1;

                }

                else if (index == 1)

                {

                    dr2 = com2.EndExecuteReader(ar2);

                    gvAuthors.DataSource = dr2;

                }

                else if (index == 2)

                {

                    dr3 = com3.EndExecuteReader(ar3);

                    gvDepartment.DataSource = dr3;

                }               

            }         

            Page.DataBind();         

 

        }

        catch

        {

            throw;

        }

        finally

        {

            dr1.Close();

            com1.Dispose();

            con1.Close();

            dr2.Close();

            com2.Dispose();

            con2.Close();

            dr3.Close();

            com3.Dispose();

            con3.Close();

        }

 

The above code (Listing 5 - WaitHandle implementation) uses WaitHandle.WaitAny() method and populates data asynchronously and binds 3 gridviews. If we see this line,

Listing 6 - WaitHandle in action

int index = WaitHandle.WaitAny(handles, 8000, false);

 

Here 8000 indicates that after 8000 milli seconds if no completed signal is received then it is said to be reached timeout. WaitHandle.WaitTimeout property will gives us the index of the WaitHandle object in the array indicating that it has reached the timeout.

 

Callback delegate

One of the overload of begin method will accept a delegate argument with a state object to detect the completion. Once the database operation is complete ADO.Net will call the delegate and the state can be accessed by IAsyncResult object.

Listing 7 - Callback delegate

BeginExecuteReader(AsyncCallback callback, object state)

 

The call back method should accept IAsyncResult as argument and call the End method.

 

Visit the Keyvan’s blog in reference section to see the implementation of callback delegate.

 

Polling

The completion signal can also detected by a method called IsCompleted() in IAsyncResult which returns a Boolean indicating that the operation is completed.

 

Cancel the execution

For some reasons or business condition if we like to cancel the operation we can call Cancel() method packed with the command object.

 

Things to consider

Since the database operation is executed in a separate thread if an exception occurs the operation is signaled as complete and the actual exception will be thrown if we call End method. So we have to make sure that we are having proper exception handling code in this scenario.

 

Downloads

Download Sample

 

Reference

Callback Delegate

 

Conclusion

In this article, I have discussed one of the useful features in ADO.Net 2.0 which helps us to increase the scalability and performance of the database application drastically without much effort. Download the source with this article and see it in action.

Happy Coding!!!

 

 

Similar Articles
  • You can contribute to CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments
    Synchronous and asynchronous command execution in c#.net
    In this blog I will tell you that what is synchronous command execution and what is asynchronous command execution. Normally whatever command we will execute in ado.net that will be executed synchronously that means until the process of execution of current command is begin we cannot move for next line to be executed. We know that command are executed in SQL server and If it will take about 10 minutes to complete its process the we have to wait 10 minutes then we move to execute next statement. In this situation we need concept of asynchronous programming where we don’t need to wait to finish execution of current command.
    for full implementation of Synchronous and asynchronous command check here: http://www.mindstick.com/blog/178/Synchronous%20and%20asynchronous%20command%20execution%20in%20c%20net
    Excellent article
    informativer and explained in very simple manner, keep it up buddy...........
    Master of the Universe
    Nicely done. This works like a champ!

    I'm curious to know if there are any "gotchas" or other considerations before I put it in production...
    Excellent
    Satesh , I should must say , you have art of teaching. Very simple and approch is really excellent.

    Its first time i am interacting with Async code and has really bite by doughts.