ADO.NET 3.5 Cookbook, 2nd Edition

10.4. Creating a DataReader Asynchronously

Problem

You need to create a DataReader asynchronously.

Solution

Use the BeginExecuteReader() and EndExecuteReader() methods of the Command object.

The solution demonstrates two techniques to asynchronously create a DataReader and process it once its creation is complete. The first method uses a callback procedure to process the DataReader once it's complete. The second checks the status of the asynchronous operation and processes the DataReader once the status indicates it is complete.

The first solution is a Windows console application that uses an asynchronous data reader to get a result set containing all rows in the Person.Contact table in the AdventureWorks database. A WAITFOR T-SQL statement is used to delay the processing of the SELECT statement for five seconds to demonstrate the background processing of the query. After five seconds, the program executes the T-SQL statement to retrieve all rows into a DataReader object, and then calls the HandleCallback() callback to output the number of rows.

The C# code in Program.cs in the project AsyncDataReaderCallback is shown in Example 10-6.

Example 10-6. File: Program.cs for AsyncDataReaderCallback solution

using System; using System.Data.SqlClient; namespace AsyncDataReaderCallback { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=localhost;Integrated Security=SSPI;" + "Initial Catalog=AdventureWorks;Asynchronous Processing=true"; string sqlSelect = "WAITFOR ...