" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
|
Sometimes, we have requirements in which we have to retrieve data from different tables at one time that take too long time to execute because of bulk data. In that case we have to wait for execution of first query to complete to start the second one. This makes a long waiting time and hence poor performance.
With ADO.Net 2.0, there is a new feature introduced for executing commands asynchronously. In ADO.NET 1.1 and prior versions, we have to wait for one query to finish the execution, then to execute another query. But now we can execute multiple queries asynchronously without waiting.
To implement this we have to provide an additional setting in the connection string and that is to set “Asynchronous Processing=true”. In case you have a requirement to use both synchronous and asynchronous operation then use two separate connection strings, one with “Asynchronous Processing=true” and other with “Asynchronous Processing=false” for better performance.
|
|
Asynchronous methods available are
BeginExecuteReader() – Initiates the asynchronous execution of the SQL statement or stored procedure that is described in the SqlCommand, and retrieves one or more result sets from the server.
EndExecuteReader() – Completes the asynchronous execution of a SQL statement and returns the requested SqlDataReader.
BeginExecuteNonQuery() – Initiates the asynchronous execution of the SQL statement or stored procedure that is described in the SqlCommand.
EndExecuteNonQuery()- Completes the asynchronous execution of a SQL statement.
BeginExecuteXmlReader() – Initiates the asynchronous execution of the SQL statement or stored procedure that is described in the SqlCommand and returns results as an XmlReader object.
EndExecuteXmlReader() – Completes the asynchronous execution of a SQL statement and returns the requested data as XML.
Cancel – Cancel the execution of a SqlCommand.
We will see how to execute multiple queries asynchronously using following example.
SqlCommand command1, command2;
string connectionString1 =”Server=192.168.10.2;Database=DB1;Trusted_Connection=true;
Asynchronous Processing = true”; SqlConnection connection1 = new SqlConnection(connectionString1);
command1 = new SqlCommand(“SELECT * FROM table1”, connection1);
string connectionString2 =”Server=192.168.10.2;Database=DB2;Trusted_Connection=true;
Asynchronous Processing=true”; SqlConnection connection2 = new SqlConnection(connectionString2);
command2 = new SqlCommand(“SELECT * FROM table2”, connection2);
// Asynchronous example
IAsyncResult iar1 = command1.BeginExecuteReader();
IAsyncResult iar2 = command2.BeginExecuteReader();
SqlDataReader dr1 = command1.EndExecuteReader(iar1);
SqlDataReader dr2 = command2.EndExecuteReader(iar2);
The above code shows tha Asynchronous behavior of query excution. iar1 and iar2 executing queries in different threads independent of each other, without waiting for another queries database operations to complete.
This is only the beginning concept of Asynchronous operation. I will explain advance features of Asynchronous behavior for handling complex requirements in next tips.
|