Performing Asynchronous Data Access

This scenario demonstrates how you can use the asynchronous version of the ExecuteReader method to read data from a data store asynchronously, and then access the results using a callback that indicates when the method completes. You do this using the BeginExecutereader and EndExecuteReader methods of the Database class.

Note

Asynchronous data access is not supported by all ADO.NET data providers. The Database class exposes a Boolean property named SupportsAsync that you can test to check at run time if asynchronous operations are supported. If this property returns false, any asynchronous methods you call will throw an InvalidOperationException. In Enterprise Library 5.0, the only database type that supports asynchronous operation is the SqlDatabase class.

Typical Goals

When you access data in a data store that responds relatively slowly, or when you want to access data from more than one data store, you generally have one or more of the following goals:

• You want to access data as a DataReader, an XmlReader, or as a single value; or you want to execute a query that does not return any results.
• You want to allow the application to continue to be responsive, or execute other lightweight code, while waiting for one or more data access operations to complete.
• You want to access multiple data stores asynchronously to minimize wait time, and you can allow the code to wait until they all complete.
• You want to retrieve results from one of several data stores that hold copies of the same data, and you can wait until the first one responds and then cancel the remaining requests.

Solution

Use the BeginExecuteReader method to initiate an asynchronous call to a database. Create a callback handler that is executed when the call completes, and call the EndExecuteReader method inside this callback to retrieve the populated DataReader. This allows code to execute while the query is running.

Note

You can also use this approach with the BeginExecuteXmlReader and EndExecuteXmlReader, BeginExecuteScalar and EndExecuteScalar, and BeginExecuteNonQuery and EndExecuteNonQuery methods. The return values for these methods are, respectively, an XmlReader, a single value, and the number of rows affected.

An alternative approach to using a callback or a lambda expression, if you want your code to halt execution until one of more data access operations complete, is to use a WaitHandle class (from the System.Threading namespace). For more information, see Asynchronous Command Execution in ADO.NET 2.0.

Using BeginExecuteReader and EndExecuteReader with a Callback

The following code shows how to use the BeginExecuteReader and EndExecuteReader methods with a callback that obtains the populated DataReader. The code uses a connection string containing the Asynchronous Processing=true (or just async=true) name/value pair, and passes a reference to the callback handler and a reference to the Database object to the BeginExecuteReader method. In the callback handler, it obtains a reference to the database from the IAsyncResult and uses this to call the EndExecuteReader method.

String connectionString 
  = @"server=(local); database=Northwind; Integrated Security=true; Asynchronous Processing=true";
Database db = new SqlDatabase(connectionString);
DbCommand cmd = db.GetStoredProcCommand("Some Procedure Name");

try
{
  IAsyncResult result = db.BeginExecuteReader(cmd, MyEndExecuteCallback, db);
}
catch
{
  // ...
  // handle any execution initiation errors here
}

//================================================

// callback handler that executes when call completes
public void MyEndExecuteCallback(IAsyncResult result) 
{
  try 
  {
    // obtain the results from the database instance in the AsyncState
    Database db = (Database)result.AsyncState;
    using (IDataReader reader = db.EndExecuteReader(result))
    {
      // ...
      // use the DataReader here as required
    }
  }
  catch
  {
    // ...
    // handle any execution completion errors here
  }
}

'Usage
Dim connectionString As String _
  = "server=(local); database=Northwind; Integrated Security=true; Asynchronous Processing=true"
Dim db As Database = New SqlDatabase(connectionString)
Dim cmd As DbCommand = db.GetStoredProcCommand("Some Procedure Name")

Try
  Dim result As IAsyncResult = db.BeginExecuteReader(cmd, _
                               AddressOf MyEndExecuteCallback, db)
Catch
  ' ...
  ' handle any execution initiation errors here
End Try

'================================================

' callback handler that executes when call completes
Public Sub MyEndExecuteCallback(result As IAsyncResult)
  Try
    ' obtain the results from the database instance in the AsyncState
    Database db = CType(result.AsyncState, Database)
    Using reader As IDataReader = db.EndExecuteReader(result)
      ' ...
      ' use the DataReader here as required
    End Using
  Catch
    ' ...
    ' handle any execution completion errors here
  End Try
End Sub

Alternatively, you can specify the callback function as a lamda expression written inline in the call to the BeginExecuteReader method.

Usage Notes

Consider the following when you use the BeginExecuteReader and EndExecuteReader method overloads:

• As with all asynchronous processing in .NET, you must always call the EndExecuteReader method eventually, either in the callback method or after the wait object has signaled. Failing to do this will mean that resources such as the connection are not released.
• The BeginExecuteReader method does not accept a CommandBehavior parameter. By default, the method will automatically set the CommandBehavior property on the underlying reader to CloseConnection unless you specify a transaction when you call the method. If you do specify a transaction, it does not set the CommandBehavior property.
• When using SQL Server, you must add the name/value pair Asynchronous Processing=true (or just async=true) to your connection string to turn on asynchronous access through the ADO.NET SqlClient provider. You should only include this in the connection string when you actually are using asynchronous access as it has a distinct effect on performance. Consider using two separate connection strings and SqlDatabase instances if only some of your code requires asynchronous data access capabilities.
• Asynchronous data access will generally perform more slowly than the equivalent synchronous method call, and so you should only use asynchronous operations where there is a clear benefit. Examples are when data stores are slow to respond and you require data from more than one data store, where you only require a result from one of a possible series of data stores, or where you use callbacks and can execute light and non-blocking code while awaiting completion of the data access call.
• When you use the callback approach, the callback function will run on a different thread from the code that initiated the operation. In desktop applications built with Windows Forms and Windows Presentation Foundation (WPF), you cannot update the interface directly from a different thread.
• You can cancel a pending or executing command by calling the Cancel method of the command you used to initiate the operation.