Using the Asynchronous Data Access Methods

  • Article

One of the most common factors that can affect performance in applications is accessing a database, and most system designs aim to minimize database round trips. Simply calling across a network to a database can cause significant delays, and the additional delay while the database processes a query can be particularly troublesome in interactive applications where the delay can cause the UI to become unresponsive. To resolve this, you may consider using asynchronous methods to access your database.

The Data Access Application Block contains versions of four of the data access methods that you can execute asynchronously. These methods rely on the features of the underlying ADO.NET data provider, and are therefore available only for the SqlDatabase implementation of the Database class in this release. The Database class exposes a method named SupportsAsync that each database-specific class (such as SqlDatabase, OracleDatabase, and SqlCeDatabase) implements to return true or false. The three pairs of asynchronous methods available are the following:

  • BeginExecuteReader and EndExecuteReader. These methods are used to obtain a DataReader populated with data from the database.
  • BeginExecuteScalar and EndExecuteScalar. These methods are used to obtain a single value from the database.
  • BeginExecuteNonQuery and EndExecuteNonQuery. These methods are used to execute a query that does not return data, but returns only a count of the number of rows affected.

In addition, when using SqlDatabase directly, an additional pair of asynchronous methods is available:

  • BeginExecuteXmlReader and EndExecuteXmlReader. These methods are used to obtain an XmlReader populated with data from the database.

The Begin and End versions of the methods operate in the same way as those available when you code directly against the ADO.NET provider. When using the Data Access Application Block, you pass to the methods the same parameters as for the non-asynchronous versions, such as a DbCommand and (optionally) a transaction reference. The methods return an instance of the IAsyncResult interface, which you then use as the parameter in a call to the End version of the method to obtain the result. Your code waits until the method completes. However, you can use an array of the standard Windows WaitHandle class to start multiple data access operations, and the code will return when they all complete or time out.

If you want to execute code in your application while the data access call is executing, you can use a lambda expression or a separate callback that is executed when data access is complete. If you use a separate callback, you pass a reference to your callback method to the Begin method, and optionally an object containing state information that you want to make available in the callback method. A common use of this object is to pass a reference to the Database object so that you can call the appropriate End method inside the callback handler. If you use a lambda expression, it will have access to the objects (such as the Database instance) that you use in the data access code.

As the methods for performing asynchronous execution match those available for the ADO.NET SqlClient class, the full range of options and approaches to performing asynchronous data access are not included in this topic. For more information, see Asynchronous Command Execution in ADO.NET 2.0.

Asynchronous Accessor Execution

Asynchronous execution is also possible when using accessors to retrieve data as objects for client-side querying. While the general technique is the same as when using the methods described in the previous section, there are some differences in the way that you create and execute the queries. For more information, see Returning Data as Objects for Client Side Querying and Executing Accessor Queries Asynchronously.

Tips for Using the Asynchronous Data Access Methods

If you decide to use the asynchronous data access methods in the Data Access Application Block, keep in mind the following points:

  • In this release, you can only use asynchronous data access operations when you are using the SqlDatabase class. They are not available for the other database classes such as OracleDatabase and SqlCeDatabase. Your code can check the SupportsAsync property to see if asynchronous operations are available in the implementation of the Database class you are using.
  • 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, so you should only use asynchronous operations where there is a clear benefit, as when you must maintain a responsive UI. Other 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 lightweight, non-blocking code while awaiting completion of the data access call.
  • The callback function or lambda expression 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.
  • Using asynchronous data access with the multiple active results set (MARS) feature of ADO.NET may produce unexpected behavior, and should generally be avoided.
  • Always ensure you call the appropriate EndExecute method when you use asynchronous data access, even if you do not actually require access to the results. You should also include in the section of code that calls the EndExecute method additional code that closes any open connections. Failing to do so can cause memory leaks and may consume additional system resources.
  • Asynchronous data access is only available if the database is SQL Server 7.0 or later.
  • If you are using SQL Server 7.0 or SQL Server 2000, you must use a TCP connection to the database, and not shared memory. To ensure that TCP is used for SQL Server 7.0 and SQL Server 2000, use localhost, **tcp:**server_name, or **tcp:**ip_address for the server name in the connection string.