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 2The 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.
