Tuesday, March 30, 2010

Asynchronous Data Access using ADO.NET

ADO.NET 2.0 enabled the infrastructure required for accessing data in asynchronous mode.
Three standard approaches available to achieve this are:
  • The Poll approach,
  • The Wait approach &
  • The Callback approach.
The approach to choose will depend upon the scenario. Between the Poll and Wait approaches, the Wait approach is better. It provides a great deal of flexibility and efficiency with a bit more complexity than the other. The Callback approach is extreme and runs more in parallel to the multi threading and other asynchronous process paradigm. It helps creating asynchronous/multithreaded data access mechanism even outside SQL/ADO.NET. Drivers and Frameworks which do not come with asynchronous infrastructure.
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
ExecuteNonQueryBeginExecuteNonQueryEndExecuteNonQuery
ExecuteReaderBeginExecuteReaderEndExecuteReader
ExecuteXmlReaderBeginExecuteXmlReaderEndExecuteXmlReader
Each of these asynchronous execution methods return  IAsyncResult. The IAsyncResult allows us to control and read into the asynchronous call with it properties - IsCompleted, AsyncState,  CompletedSynchronously & AsyncWaitHandle. The AsyncWaitHandle property returns the WaitHandle object.
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