Fetching Rows Asynchronously

One of the more challenging aspects of working with ADO.NET is overcoming the shortcomings when it comes to asynchronous operations. ADO.NET still does not support the asynchronous Connection Open method or asynchronous rowset population. Fortunately, the 2.0 Framework does include a few more classes to make this more manageable. To illustrate how this is done, I've included an example (a complete program, actually) that handles a number of tasks asynchronouslyincluding fetching a rowset after the DataReader has returned from an asynchronous query. Remember, the asynchronous BeginExecuteReader simply returns a handle to an open DataReaderit does not return a single row of datathat's up to you. I've shown this example at a dozen conferences. It's designed to help use the MSDN subscription index. (You know, the one that takes an eon to locate anything.) The program is used to illustrate how to set up and execute a BulkCopy operation using the new SqlBulkCopy class included in ADO.NET 2.0. The application uploads the data (from a JET database on the MSDN Index CD) to a local SQL Server database (of your choosing). It then permits you to execute rather complex queries against that data. The search UI is shown in Figure 11.18.

Figure 11.18. The MSDN Search Index Utility.

Once the user initiates a search, the application executes the query asynchronously, and while waiting, it slowly exposes a "Please wait" dialog that fades into view over a period of 5 seconds or so. If the query does not take a long time to execute, the user might never see the dialogjust the results. When the asynchronous portion of the query is complete, the application switches to phase twofetching the rows (rowset population).

The rowset is fetched within a BackgroundWorker thread (new for the 2.0 Framework). While it's beyond the scope of this book to get into the details of how this is managed (and the numerous pitfalls), suffice it to say that the code needed to create and manage the thread is not for the feint of heartbut it's far better than the alternative as implemented by earlier versions of the Framework. The code example shown in Figure 11.19 illustrates the core functionality.

Figure 11.19. Starting a BackgroundWorker thread to complete rowset population.

The work executed by the BackgroundWorker thread is executed by the DoWork event. This was constructed for me automatically (in Visual Basic .NET) by declaring the bgWorker variable using the WithEvents option, as shown in Figure 11.20.

Figure 11.20. Declaring the bgWorker variable WithEvents.

The DoWork event handler (shown in Figure 11.21) looks like the other rowset population code I've used before. Just remember that this code is running on another thread. This means it can't reference any object that was not created by the threadthis includes any of the UI elements (TextBox, ProgressBar, or any other Form control). The BackgroundWorker thread handles this issue by setting up a callback routine of its ownthe ProgressChanged event, which runs on the UI Form's thread. It's invoked on demand by the ReportProgress method, which accepts a "percent complete" value (between 0 and 100) to indicate how much work has been done so far. Since I don't really know how many rows can result from the query, I can either fudge a number or go to great lengths to provide a more accurate count. The ProgressChanged event is an opportunity to bump your ProgressBar or otherwise inform the user that the application is still running.

Figure 11.21. The DoWork event handler.

When the asynchronous load DoWork routine is complete, the BackgroundWorker thread is notified so you'll have an opportunity to tear down and dispose of the thread.

IMHO

This all seems like a lot of trouble to do something that the Framework should have implemented in the first place....