Executing Accessor Queries Asynchronously

You can execute accessors asynchronously if you wish, in much the same was as you can execute many of the other methods of the Data Access Application Block. However, this is only possible where the underlying database supports asynchronous operations (in other words, the Database.SupportsAsync property is true).

The SprocAccessor and SqlStringAccessor classes expose the BeginExecute method, which takes as parameters a reference to a callback method that will execute when the operation ends, a parameter that contains state you want to pass to the callback method, and an object array containing the parameter values the accessor will use when it executes the query. Typically you will pass a reference to the accessor as the asynchronous state parameter so that you can call the EndExecute method on it within the callback method.

The accessors expose only a single overload of the BeginExecute method. You can create the accessor using any of the techniques described in the topic Creating and Using Accessors.

When the query operation completes, the code in the callback handler executes and you can retrieve the accessor and call the EndExecute method to retrieve the IEnumerable sequence as the result. The following code shows an example of using asynchronous operations with an accessor. Notice that the connection string specifies a connection that supports asynchronous operations. In addition, keep in mind that the connection is not closed until the entire list is processed. You must force complete evaluation of the result sequence to extract all of the data when you call the EndExecute method. For example, call the ToList method, as shown in the following code extract.

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

try
{
  // Create the accessor. This example uses the simplest overload.
  var accessor = db.CreateSprocAccessor<Customer>("Top Ten Customers");

  // Execute the accessor asynchronously, passing in the callback handler, 
  // the existing accessor as the AsyncState, and the parameter values.
  IAsyncResult async = accessor.BeginExecute(MyEndExecuteCallback, accessor, 2009, "WA");
}
catch
{
  // ...
  // handle any execution initiation errors here
}

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

// callback handler that executes when call completes
public void MyEndExecuteCallback(IAsyncResult async) 
{
  try
  {
    // obtain the results from the accessor
    DataAccessor accessor = async.AsyncState as DataAccessor<Customer>;
    var customers = accessor.EndExecute(async).ToList();
    // ... 
    // use the results here
    // ... 
  }
  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 New SqlDatabase(connectionString)
Dim cmd As DbCommand = db.GetStoredProcCommand("Some Procedure Name")

Try

  ' Create the accessor. This example uses the simplest overload.
  Dim accessor = db.CreateSprocAccessor(Of Customer)("Top Ten Customers")

  ' Execute the accessor asynchronously, passing in the callback handler, 
  ' the existing accessor as the AsyncState, and the parameter values.
  Dim async As IAsyncResult _
      = accessor.BeginExecute(AddressOf MyEndExecuteCallback, accessor, 2009, "WA")
Catch
  ' ...
  ' handle any execution initiation errors here
End Try

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

' callback handler that executes when call completes
Public Sub MyEndExecuteCallback(async As IAsyncResult)
  Try
    ' obtain the results from the accessor
    Dim accessor As DataAccessor = TryCast(async.AsyncState, DataAccessor(Of Customer))
    Dim customers = accessor.EndExecute(async).ToList()
    ' ... 
    ' use the results here
    ' ... 
  Catch
    ' ...
    ' handle any execution completion errors here
  End Try
End Sub

An alternative approach to coding the callback is to use a lambda function declared within the call to the BeginExecute method of the Accessor.

Note

There are some limitations on using asynchronous data operations, and several issues you should be aware of. For more details, see Using the Asynchronous Data Access Methods.

In addition, when you execute an accessor asynchronously, you can only iterate over the result set once. If you attempt to iterate over it again, the block will raise an exception. For this reason, you may need to use an approach such as the ToList method to force complete evaluation of the result sequence.