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
method or asynchronous rowset population. Fortunately, the 2.0 Framework does include a few more classes to make this more manageable. To
how this is done, I've included an example (a complete program, actually) that handles a number of
asynchronouslyincluding fetching a rowset after the
has returned from an asynchronous query. Remember, the asynchronous
simply returns a handle to an open
it does not return a single row of datathat's up to you. I've shown this example at a
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
operation using the new
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
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
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
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
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
Figure 11.19. Starting a BackgroundWorker thread to complete rowset population.
The work executed by the
thread is executed by the
event. This was
for me automatically (in Visual Basic .NET) by declaring the
variable using the
option, as shown in Figure 11.20.
Figure 11.20. Declaring the bgWorker variable WithEvents.
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 (
, or any other
thread handles this issue by setting up a callback routine of its ownthe
event, which runs on the UI Form's thread. It's invoked on demand by the
method, which accepts a "percent complete" value (between 0 and 100) to
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
event is an opportunity to bump your
the user that the application is still running.
Figure 11.21. The DoWork event handler.
When the asynchronous load
routine is complete, the
thread is notified so you'll have an opportunity to tear down and dispose of the thread.
This all seems like a lot of trouble to do something that the Framework should have implemented in the first place....