New Features of ADO.NET 2.0

Asynchronous Operations

ADO.NET 1.1 and earlier required your data access operations to be synchronous. ADO.NET 2.0 includes new methods for the Connection and Command classes that permit you to execute asynchronous operations.

In a multithreaded middle code the key factor for increasing throughput is doing more than one thing at the same time. An asynchronous application programming interface (API) has been added to ADO.NET 2.0 to allow an application to continue execution without waiting for the database to complete the operation.

Asynchronous operations have been made possible through a set of Begin and End methods for an operation, as well as a method for synchronous operation. SqlClient provides built-in SqlCommand methods that provide asynchronous execution.

Methods that support asynchronous execution and their synchronous counterparts are:

Synchronous Method	Asynchronous Methods
ExecuteNonQuery	BeginExecuteNonQuery, EndExecuteNonQuery
ExecuteReader	BeginExecuteReader, EndExecuteReader
ExecuteXmlReader	BeginExecuteXmlReader, EndExecuteXmlReader

In the asynchronous pattern, the Begin method takes all the input parameters, and the End method provides all the output parameters, as well as the return value. For example, here is what an asynchronous invocation of ExecuteReader looks like.

In the previous sample you can see that BeginExecuteReader doesn’t take any parameters (mapped to the ExecuteReader overload that doesn’t take any parameters), and that EndExecuteReader returns a SqlDataReader, just like ExecuteReader does.

• The Connection String Keyword. In order to use asynchronous commands, the connections on which the commands will be executed must be initialized with async=true in the connection string. An exception will be thrown if any of the asynchronous methods are called on a command with a connection that doesn’t have async=true in its connection string.

  Note

  Execution of synchronous operations on connections that have asynchronous operations enabled will have increased resource utilization.Using the synchronous methods in connections opened with async=true can lead to a small performance degradation.

• Completion Signaling. A fundamental element of an asynchronous API is the completion signaling mechanism. The begin call returns immediately making it necessary to detect when the operation is actually complete.

  • Callback. All the begin methods have overloads that take a delegate as a parameter, along with a user-defined state object. When this overload is used, ADO.NET calls that passed-in delegate, and makes the state object available through the IAsyncResult object (passed as a parameter to the delegate).

  • Synchronization objects. The IAsyncResult objects returned by the begin methods have a WaitHandle property that contains an event object. Using this event object in synchronization primitives allows the calling code to wait for multiple pending operations, and be notified either when one finishes or when all of them finish.

  • Polling. The IAsyncResult object also has an IsCompleted boolean property. This property will change to true when the operation completes, so it can be used by code that needs to perform some continuous activity; that code can periodically check the property and, if it changed, process the results.

  In all three cases, after the operation is signaled as completed, the caller must call the corresponding End method for the Begin method that initiated the asynchronous command. Failure to call the end method that matches the begin method may result in ADO.NET leaking system resources. Also, the call to the End method will make the results of the operation available to the caller.