Asynchronous Execution in ADO.NET 2.0
page 4 of 9
by Satheesh Babu
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 59804/ 110

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 an 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 the other with “Asynchronous Processing=false.” Because we are using the connection string with Async enabled for synchronous operation, it will hit the performance some what.

The connection string will be:

"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,” 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 a 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. The thread will wait for the “Sync line 1” to complete execution and proceed with the “Sync line 2.”  Download the code packed with this article and see it in action. I have used "waitfor delay '00:00:06'" in the query to demonstrate the example as an alternative of complex stored procedure that takes sometime to complete execution. Note “waitfor” statement will work only in Sql server 2005.

Thus we have learned 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.


View Entire Article

User Comments

Title: f   
Name: f
Date: 2012-09-29 7:03:06 AM
Comment:
f
Title: thank you   
Name: khoa
Date: 2011-06-12 7:21:13 PM
Comment:
very good code and article
Title: Code Monkey   
Name: King Kong
Date: 2009-10-29 12:20:15 PM
Comment:
This is a great article. Very concise and to the point.
Title: InvalidOperation exception   
Name: Ross
Date: 2009-01-29 9:36:34 AM
Comment:
Kevin - Is the DB connection still open at the point you call EndExecuteReader()?
Title: InvalidOperation exception   
Name: Kevin Burton
Date: 2008-12-18 11:42:00 PM
Comment:
I have some code that I was trying to improve by adding the new asynchronous SQL methods Begin... and End.... But when I add it to my code I get an exception:

InvalidOperationException.
{"The asynchronous operation has already completed."}

The first few frames of the stack trace look like:

at System.Data.SqlClient.SqlCommand.VerifyEndExecuteState(DbAsyncResult
dbAsyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult
asyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult
asyncResult)

In searching the internet I see that at least one other person has seen this problem. I was wondering if there is a work around? If anyone has gotten the asynchronous methods to work without this exception I would very much appreciate their tips. I would also welcome a confirmation message of simply "this is a known bug".

Comments?

Thank you.

Kevin
Title: very useful   
Name: RuneAletheia
Date: 2008-12-16 3:24:03 PM
Comment:
this article was very helpful at explaining basic implementation of async -- also, the updated link to the reference is:

http://nayyeri.net/blog/Asynchronous-command-execution-in-NET-2-0/
Title: Very Good   
Name: Sunil Dhiman
Date: 2008-07-04 12:17:38 AM
Comment:
this article helped me very much.
thanks.
Title: Very Good   
Name: Samim Mondal
Date: 2008-04-02 3:16:42 AM
Comment:
Its very good article....
Title: RE:Thank you   
Name: satheesh babu
Date: 2008-03-24 12:55:18 PM
Comment:
Thanks for the comments guys!!
Satheesh
http://www.codedigest.com
Title: Thank you   
Name: Beri
Date: 2008-03-24 12:51:21 PM
Comment:
I've been struggling to make sense of Asynchronous Transcations. This article has helped me a lot. Thank you very much Sateesh.
Title: Mrs   
Name: JayaNayagam
Date: 2008-01-07 1:14:14 AM
Comment:
The author explains quite clearly,quite useful.
Title: Nice info   
Name: Abhishek Kumar Singh
Date: 2007-12-20 4:41:16 AM
Comment:
nice one

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-12-11 7:37:54 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search