Three standard approaches available to achieve this are:
- The Poll approach,
- The Wait approach &
- The Callback approach.
To enable asynchronous data access, we would need to add “Asynchronous Processing=true;” in the connection string.
The asynchronous access is triggered by the command methods prefixed by “Begin” and the result is retrieved through their “End” counterparts as below:
Synchronous Methods | Asynchronous methods | |
| Start process returns IAsunchResult | End process returns data | |
| ExecuteNonQuery | BeginExecuteNonQuery | EndExecuteNonQuery |
| ExecuteReader | BeginExecuteReader | EndExecuteReader |
| ExecuteXmlReader | BeginExecuteXmlReader | EndExecuteXmlReader |
The WaitHandle is an abstract class which provides a handle to the asynchronous execution. It exposed methods such as WaitOne(), WaitAny() and WaitAll() notifying the status of the execution. To process more than one databases commands, we can simply create an array containing wait handles for each process and wait till the WaitAll() or WaitAny() responds.
AsyncCallback is a delegate, used to specify which method to be called once the Asynchronous process is over.
For the next few examples we’ll be using the following modal and it’s constructor. Passing the reader is not the best way to initiate a class in many scenarios but it works for our examples.
public class Customer {
public Customer() {
}
public Customer(SqlDataReader reader) {
CustomerID = reader["CustomerID"].ToString();
CompanyName = reader["CompanyName"] == null ? string.Empty : reader["CompanyName"].ToString();
ContactName = reader["ContactName"] == null ? string.Empty : reader["ContactName"].ToString();
Phone = reader["Phone"] == null ? string.Empty : reader["Phone"].ToString();
}
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string Phone { get; set; }
}The Poll Approach:
In the poll approach, we make a call to the database, executing the required command and keep polling the status or the command to check if the execution is complete. Here’s the method that does just that:
public List<Customer> GetAllCustomers() {
List<Customer> customers = new List<Customer>();
IAsyncResult asyncResult = null;;
SqlDataReader sqlReader = null;
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandText = "SELECT top 10 * FROM [Customers]";
sqlCommand.CommandType = CommandType.Text;
try{
sqlConnection.Open();
//Start Async process
asyncResult = sqlCommand.BeginExecuteReader();
while (!asyncResult.IsCompleted) {
//Wait till Async process is executing
Thread.Sleep(10);
}
//Retrieve result from the Async process
sqlReader = sqlCommand.EndExecuteReader(asyncResult);
while (sqlReader.Read()) {
customers.Add(new Customer(sqlReader));
}
sqlConnection.Close();
}
catch(Exception ex){
}
return customers;
}The Wait Approach
Using this approach we can start multiple asynchronous processes and wait till they are complete. The real benefit of the wait process doesn’t come into the picture unless you have to make multiple database calls in the same or make calls to different databases to get the same entity.
Imagine we need to fetch customers as well as employees using the following employee model
public class Employee {
public Employee() { }
public Employee(SqlDataReader reader) {
EmployeeID = (int)reader["EmployeeID"];
LastName = reader["LastName"] == null ? string.Empty : reader["LastName"].ToString();
FirstName = reader["FirstName"] == null ? string.Empty : reader["FirstName"].ToString();
Title = reader["Title"] == null ? string.Empty : reader["Title"].ToString();
}
public int EmployeeID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public string Title { get; set; }
}In order to enable the connection to support multiple result sets, as in this case, we would need to set the MultipleActiveResultSets property of the connection string to true. To do that add “MultipleActiveResultSets = true;” in the connection string. After those setting taken care of, we fire multiple commands using the wait approach:
List<Customer> customers = new List<Customer>();
List<Employee> employees = new List<Employee>();
SqlDataReader customerSqlReader = null;
SqlDataReader employeeSqlReader = null;
IAsyncResult customerAsyncResult = null;
IAsyncResult employeeAsyncResult = null;
WaitHandle[] waitHandles = new WaitHandle[2];
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlCommand customerSqlCommand = new SqlCommand();
customerSqlCommand.Connection = sqlConnection;
customerSqlCommand.CommandText = "SELECT top 10 * FROM [Customers]";
customerSqlCommand.CommandType = CommandType.Text;
SqlCommand employeeSqlCommand = new SqlCommand();
employeeSqlCommand.Connection = sqlConnection;
employeeSqlCommand.CommandText = "SELECT top 10 * FROM [Employees]";
employeeSqlCommand.CommandType = CommandType.Text;
try{
sqlConnection.Open();
//Start Async processes
customerAsyncResult = customerSqlCommand.BeginExecuteReader();
employeeAsyncResult = employeeSqlCommand.BeginExecuteReader();
waitHandles[0] = customerAsyncResult.AsyncWaitHandle;
waitHandles[1] = employeeAsyncResult.AsyncWaitHandle;
WaitHandle.WaitAll(waitHandles);
//Retrieve result from the Async process
customerSqlReader = customerSqlCommand.EndExecuteReader(customerAsyncResult);
while (customerSqlReader.Read()) {
customers.Add(new Customer(customerSqlReader));
}
employeeSqlReader = employeeSqlCommand.EndExecuteReader(employeeAsyncResult);
while (employeeSqlReader.Read()) {
employees.Add(new Employee(employeeSqlReader));
}
sqlConnection.Close();
}
catch(Exception ex){
}In the above code snippet we wait for all the database calls to complete before processing it. An improvement that can be made here is processing data as we receive it. To achieve that, we would have to use the WaitAny() instead of the WaitAll() method and running a loop for each execution. Here’s how: List<Customer> customers = new List<Customer>();
List<Employee> employees = new List<Employee>();
SqlDataReader customerSqlReader = null;
SqlDataReader employeeSqlReader = null;
IAsyncResult customerAsyncResult = null;
IAsyncResult employeeAsyncResult = null;
WaitHandle[] waitHandles = new WaitHandle[2];
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlCommand customerSqlCommand = new SqlCommand();
customerSqlCommand.Connection = sqlConnection;
customerSqlCommand.CommandText = "SELECT top 10 * FROM [Customers]";
customerSqlCommand.CommandType = CommandType.Text;
SqlCommand employeeSqlCommand = new SqlCommand();
employeeSqlCommand.Connection = sqlConnection;
employeeSqlCommand.CommandText = "SELECT top 10 * FROM [Employees]";
employeeSqlCommand.CommandType = CommandType.Text;
try{
sqlConnection.Open();
//Start Async processes
customerAsyncResult = customerSqlCommand.BeginExecuteReader();
employeeAsyncResult = employeeSqlCommand.BeginExecuteReader();
waitHandles[0] = customerAsyncResult.AsyncWaitHandle;
waitHandles[1] = employeeAsyncResult.AsyncWaitHandle;
for (int i = 0; i < 2; i++) {
int waitHandleIndex = WaitHandle.WaitAny(waitHandles);
switch (waitHandleIndex) {
case 0:
customerSqlReader = customerSqlCommand.EndExecuteReader(customerAsyncResult);
while (customerSqlReader.Read()) {
customers.Add(new Customer(customerSqlReader));
}
break;
case 1:
employeeSqlReader = employeeSqlCommand.EndExecuteReader(employeeAsyncResult);
while (employeeSqlReader.Read()) {
employees.Add(new Employee(employeeSqlReader));
}
break;
}
}
sqlConnection.Close();
}
catch(Exception ex){
}The Callback approach:
This wait approach it great will give the best solution in all the practical scenarios that you will come across. Please refer to the next blog post for the callback approach.
No comments:
Post a Comment