ADO.NET 3.5 Cookbook, 2nd Edition

10.6. Canceling an Asynchronous Query

Problem

Given a command running asynchronously on a background thread, you want to give the user the option to cancel the query if it is taking too long.

Solution

Use the Cancel() method of the Command object.

The solution first counts the number of records in the Sales.SalesOrderHeader table in AdventureWorks and outputs that number to the console. Next the solution creates a DataAdapter to fill a DataTable with the records from the Sales.SalesOrderHeader table in AdventureWorks. A new thread is created that invokes the IssueCommand() method when the thread starts, which in turn calls the Fill() method of the DataAdapter. A second thread is created that invokes the CancelMethod() when the thread starts, which in turn calls the Cancel() method of the Command object after a delay of 500 ms—enough time for a few records to fill the DataTable. The number of rows filled before the fill is canceled is output to the console.

The C# code in Program.cs in the project CancelCommand is shown in Example 10-9.

Example 10-9. File: Program.cs for CancelCommand solution

using System; using System.Data; using System.Data.SqlClient; using System.Threading; namespace CancelCommand { class Program { private static SqlDataAdapter da; private static DataTable dt = new DataTable(); private static SqlCommand command; static void Main(string[] args) { Console.WriteLine("(Press any key to exit.)"); string sqlConnectString = "Data Source=localhost;Integrated Security=SSPI;" ...