Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

The ADO.NET proudly rolled out its new asynchronous execute functions with the 2.0 version. Actually, you've been able to execute virtually all ADO.NET operations asynchronously from the beginning: All you had to do was figure out how to set up and execute threadsany average rocket scientist could do it. Of course, these are the same rocket scientists that got meters and feet mixed up with one of their Mars-bound spacecraft. The 2.0 Framework also makes the threading approach easier by exposing the BackgroundWorker thread class. In this section, I'll show you the SqlCommand functions that can be executed asynchronously and how to start, stop, and check to see if they are done. Why bother? Well, if you have users with the patience of Noah, you don't have to worry about providing progress indicators to show that your application is still workingand not locked up (again). For the rest of us, those who have users that start pressing buttons after about 15 seconds while staring at an hourglass cursor, I think you'll find the asynchronous execute functions just what you need.

Do asynchronous functions make sense for ASP.NET or XML Web Services? Sure, when you want to overlap processing parts of the page logic, asynchronous functions make a lot of sense. This way, you can get several queries running, and while they are executing, you can run other code to get ready for the results. Yes, you'll probably end up blocking at the end of the routine waiting for all of the sub-threads to execute, but your page or end-result is built faster than it would be if you executed the queries or performed the other operations serially.

Before I get started, I need to make one thing perfectly clear: Handling threads can be difficult and dangerous. When you start a new thread of execution, you're launching code that executes independently of the code that started the threadwell, almost. Except on multiprocessor systems (or hyper-threading CPUs), a single processor executes all of the threads in the systemyours, Windows, the kernel, and that application you run in the background playing your favorite MPG file. This means all threads are executed serially, but the OS and the processor can interrupt that thread at any time to share CPU time with other threads that need to run. The OS spends considerable time and energy switching context from one thread to another and back again often many millions of times a second.

One of the (many) side effects of the Windows threading architecture is that you won't be able to directly access Windows controls created by another thread. This is a very common mistake. For example, suppose you execute a SqlCommand asynchronously. A new thread is created to do the work, and your current thread (the thread that executed the SqlCommand) continues to run. When ADO.NET signals that the asynchronous thread is done (and your data is ready to retrieve), the second (asynchronous) thread gets the CPU's attention and you're permitted to process the returning rows. What you won't be able to do is set properties on other controls owned by/hosted by the calling threadnot without a Delegate. I'll show you how to use Delegates to get around this problemjust don't go running off into that nearby pasture and set up a threading love-in until I show you where the cows have been.

No, asynchronous operations don't always (not even usually) make sense. They are important for those situations where you don't know how long a command or other operation is going to take place and you need to retain control over your application while this extended process is proceeding. Asynchronous operations can also make sense when you can segment the logic in your routine so operations can be executed in parallel instead of serially. Just use them wisely. Threads, like other system resources, are a precious commodity.

Tip

The MSDN Search sample application found on the book's DVD illustrates use of asynchronous operations.

Coding the Asynchronous SqlCommand Functions

Consider that the asynchronous execute functions call the same synchronous functions behind the scenes, but they do so on their own independent thread, so you don't have to block while you wait for them to complete. When you're informed that the asynchronous query has completed, you'll have an opportunity to fetch data from any rowsets or OUTPUT parameters in exactly the same way, as discussed earlier in this chapter. The fundamental difference is that when executing these functions, you'll have to pass additional arguments to manage and fetch thread status.

The SqlDataReader returned by BeginExecuteReader does only half of the work needed to get your rowset. While it asynchronously executes the query, it signals that it's "done" before a single row is fetched. Unless you pay attention to the later parts of this section, you won't discover how to fetch those rows asynchronouslyI'll show you how to execute the Load (or Fill) function (or any function) asynchronously.

Before you can execute any SqlCommand function asynchronously, you'll need to add another key/value pair to the ConnectionString: ConnectionString Asynchronous Processing =true. This enables the asynchronous features of ADO.NET.

If there is a syntax error deep in the T-SQL code you execute with an asynchronous function, you won't throw the exception in your application until the query completesthis might mean you won't see the error for some time.

Let's walk through a sample application that executes a query that takes a long time to executeI call the stored procedure WaitForALongTime, which includes a WAIT FOR DELAY statement that accepts a parameter to set the delay time. First, I need to build a SqlConnection object that supports asynchronous operations. Figure 11.22 illustrates code to do just this. It also sets up the SqlCommand to call the stored procedure. Note that I set an upper limit of 6 minutes on the SqlCommand, so ADO.NET won't let it run any longer than a quick trip to the fridge for another Diet Pepsi.

The next block of code sets up the IAsyncResult object, which is used to manage asynchronous operations throughout the Framework. I need to take a closer look at this interface before I move on. IAsyncResult is implemented by any Framework class that can operate asynchronously. Actually, you can call any Framework method or function asynchronously, and I'll have to discuss that later in this chapterfor now, all you need to know is that the IAsyncResult interface exposes several properties that indicate what the thread is doing and has done, and to control its operation. I'll demonstrate their use a bit later in this section.

• AsyncState: This property returns a user-defined object specified when the asynchronous operation is started. It can be used for anything you need it for. Generally, your code sets or uses this value as it leaves the called asynchronous routine to indicate success, failure, or how its stomach is feelingor anything else it needs to return.

• AsyncWaitHandle: This property contains a handle created (in this case) by the asynchronous SqlCommand functions. They can be used to wait for the asynchronous operation to complete instead of polling.

• CompletedSynchronously: This property changes state when the asynchronous operation completes synchronously. I don't recommend using this property to poll the asynchronous operation, as it does not seem to return anything except False.

• IsCompleted: This property can be used to poll the asynchronous operation addressed by the IAsyncResult interface to see if it's done. Once IsCompleted changes to True, it's safe to tear down any resources used by the thread and to collect the results.

So, we're ready to walk through the first steps of setting up the asynchronous SqlCommand execute function. Figure 11.23 illustrates how this is coded. Notice that I create a new IAsyncResult object and populate it by executing BeginExecuteReader. Your current application thread continues to execute, so you can continue with other work as the new thread executes the CommandText T-SQL. In this case, I'm simply entertaining the user with a progress barthat's what the ShowProgress routine does. Note that I do not simply keep looping as I wait for the asynchronous thread to complete. While that's not a fatal mistake, it would be like starting a race horse out of the gate with a 10-foot rope tied to his tailthe other end of which is tied to your '57 Chevy's bumper. The trick here is to tell Windows that you want it to go off and handle other (more important) operations for a while. In this case, I call the Thread.Sleep function and tell it to let Windows do its thing for 500ms (½ second). Sure, the granularity could be larger, but a user can sense a ½-second difference. A lesser time interrupts Windows more frequently than necessary. Consider that this approach is kinda like that last trip to the lake with your kids. As you drive on, that 4-year-old kid in the back asks, "Daddy, are we there yet?" in a seemingly endless loop (using a pseudo-random number generator to compute the time between queries). After the 119th time, Bill usually says, "Yes! we're here! Just a minute and we'll stop here on the freeway and you can get out and...." That's why his wife goes with him on those trips.

When the IArResult.IsCompleted finally changes to True, it's time to fetch the results. No, the SqlDataReader has not returned a single row at this point. This means you'll need to fetch the SqlDataReader stream using the EndExecuteReader function, which accepts the IAsyncResult object created when you first began the asynchronous operation and returns the SqlDataReader stream.

IMHO

I'm of the opinion that you should never wait forever for anythingexcept a nice piece of carrot cake. So, what if IsCompleted never changes state? A best practice would be to include another counter or watchdog timer in your code to ensure that you eventually exit from the loopyou can't depend on your spouse to nag you.

Waiting for Godot or Your Asynchronous Operation to Complete

There are at least three different strategies you can use to wait for your asynchronous operation to complete. These include:

• Polling: I already illustrated this approach in Figure 11.23. In this case, you test the IAsyncResult object IsCompleted property until it turns true or you give up waiting. This approach can consume CPU time on your client that might be better spent elsewhere. Just remember not to poll too frequently and spend your time wisely between polls.

• Callback: This approach is a bit more code-intensive and requires a far more in-depth understanding of how the Framework handles asynchronous operations. Basically, when you start the asynchronous operation, you provide the address of a routine (a Delegate) that is to be called when the operation is done. This approach is not that tough to set up and wastes no time at all while the asynchronous operation is running. I'll illustrate that point next.

• Waiting: In this approach, you use the ability of the Framework to handle one or several threads at once by synchronizing asynchronous operations using WaitHandle objects. Using another variation of the asynchronous SqlCommand function, you set up a thread-specific WaitHandle that can be passed to the WaitAny or WaitAll primitives to notify you and execute a selected routine when the thread is done. This approach is illustrated later in this section.

Using the Callback Technique to Manage Asynchronous Operations

Since I already illustrated the polling technique, let's move on to callback. This approach requires that you provide a subroutine or function that's invoked (called) when the thread executing the query is done or ready to return the first row. Be carefulthis routine runs on its own thread, independent from the thread that manages your form, so you won't be able to reference any of the controls or other routines owned by the form. This means if you want to display the rowset returned by the query or simply change a component property, you'll throw an exception unless you create a Delegate to do so.

IMHO

Do people think that an application is faster if the progress bar increments more quickly?

This Delegate must be defined with the same signature as the routine you want to call. Okay, if you don't know what "delegates" or "signatures" are, you aren't alone. A Delegate is simply a way to give a name to a subroutine or function declaration and assign it the address of another routine (subroutine or function). This way, when the Delegate is called, it really calls the addressed routinesort of like setting up call forwarding, but without the monthly fee. The Delegate is created with the same number of input and output arguments (its "signature").

Let's say you have a subroutine called "Fred" and you want to call it from another thread. You could declare a Delegate "DelegateFred" and an instance of DelegateFred and point it to the "Fred" function's address, as shown in Figure 11.24.

Need a bit more detail? Sure, let's walk through an implementation of the application I just explained, but this time, I'll set up a callback to a routine that extracts the data from the SqlDataReader and builds a DataTable to be bound to the DataGridView control.

First, I set up the SqlCommand Parameter, open the Connection, and create a new AsyncCallback object, which expects the address of the routine to be invoked (called) when the asynchronous SqlCommand execute function is done. I point the AsyncCallback object at the CallMeWhenDone routine. Next, I call the SqlCommand.BeginExecuteReader and pass in the name of our AsyncCallback object. I also pass in a pointer to the SqlCommand object in the AsyncState objectthis is used later in the callback routine. All of this is illustrated in Figure 11.25. I added a line of code to dump the current ThreadID so that I can clearly see which thread is running which code. Just before I end this routine, I enable the Form-based Timer1 control, which increments the ProgressBar so I know that the code is still running. Of course, the fact that the ProgressBar is incrementing doesn't really mean the thread performing the concurrent execution is still running, but it makes my user think so.

When the asynchronous operation completes, the callback routine is called on its own thread. Now things get tricky. Let's examine the CallMeWhenDone to see how this can be done. First, I need to set up the Delegate object I'll use when it's time to pass the fetched data over to the Form. Remember, I can't access the Form's controls or properties directlythey are running on the other thread. I create a Delegate instance to pass the DataTable created in this thread to a routine that passes the DataTable to the DataGridView.DataSource property.

Once the Delegate objects are set up, I'm ready to fetch the data from the completed BeginExecuteReader routine. This is accomplished by first building a pointer to the original SqlCommand instance that ran the asynchronous query. I use the IAsyncResult.AsyncState argument to pass this address from the calling thread. The EndExecuteReader function returns the SqlDataReader I can use to extract the rows. In typical fashion, I create a new DataTable and populate it with the Load function. When I'm ready to pass this populated DataTable to the Form, I pass it though another Delegate created just for this purpose. Figure 11.26 illustrates how I coded this logic.

This sample application ("Async Callback" on the DVD) also includes logic to permit the user to cancel the query. I found that this can be problematiceven when you have a separate thread on which to execute the SqlCommand.Cancel function. If ADO.NET senses that the command has progressed too far, it throws an exception. However, if ADO.NET can cancel the operation, the thread executing the SqlCommand throws its own InvalidOperation exception. If you simply step over these exceptions, you should be able to cancel the command. Figure 11.27 illustrates how this is done.

Using WaitHandle Logic to Manage Asynchronous Operations

The last of the three techniques you can use to manage threads is a bit more complex in some ways and easier in others. It uses the Framework's ability to manage asynchronous operations by using logic to monitor the state of one or more thread WaitHandles. Since the asynchronous SqlCommand functions return a WaitHandle with the IAsyncResult, it's fairly easy to pick off this handle and simply ask the Framework to block until it's done. Ah, doesn't that mean you're back to blocking after execution? Sure, but since you started the SqlCommand asynchronously, you don't have to block right awaytypically, you run whatever code you can before you need the result of the asynchronous operation. This approach makes the most sense when you start two or more threads working on various asynchronous operations and you need to wait until one or more or all of them are done before proceeding. For example, if you need to launch queries against two or more server data sources at once and have these queries run in parallel, this approach is for you. Sure, you don't necessarily need to be fetching data while other threads are working; you can run several operations of any kind in parallel.

Configuring your code to manage the WaitHandle objects generated by your asynchronous operations is easy. All you need is an array of WaitHandle objectsone for each SqlCommand asynchronous execution function you want to monitor. When you use the Begin asynchronous method, capture the WaitHandle property from the IAsyncResult that's returned by the asynchronous method and store it in the WaitHandle array.

When you're ready to see if your asynchronous operation is done, set up your code to call the WaitHandle WaitAny function and point to the WaitHandle object array you just built. At this point, your code blocks until one (any one) of the asynchronous events signals that it's done or the timeout expires. The WaitAny function returns an index (integer) that indicates which of the WaitHandle objects is done. Once you process the results of that operation, you'll need to come back and continue looking for additional threads to complete.

If you need all of the asynchronous operations to complete before you're notified, use the WaitAll function. In this case, your code is blocked until all of the threads in the WaitHandle array are done or the timeout expires.

When you configure the WaitHandle object, you can (should) set a timeout to tell the Framework how long to wait for the operation to complete. As you're well aware (or should be by now), stuff happens. If the query gets lost or confused, you're going to want a backdoor to let your logic out.

No, you won't be able to start a timer to increment a ProgressBar while the WaitAny or WaitAll functions are executing. Your UI thread is not getting any cyclesthe Framework is attending to other applications and won't give you control until the threads are (all) done or the specified timeout elapses. This also means you won't have any cycles to cancel the querynot after you've executed the WaitAll or WaitAny functions.

MARS introduces a new wrinkle to this picture. Since you can execute several operations at once using MARS, you would think that you can execute several simultaneous queries at once on the same connection. Well, you can'tMARS does not permit multiple asynchronous operations.

That's a lot of information to consume without looking at the code used to implement it. The example I'm going to walk through ("Waiting on Thread Handles" on the DVD) is designed to execute three independent operations on three different connections. This permits the server to run all three queries in parallel (whenever possible) or the approach could be used to execute three (or "N") queries or action commands to three separate and entirely different servers. When the Windows Form is first set up, I create these SqlConnection objects and three SqlCommand objects. The Form_Load event is illustrated in Figure 11.28. Note that I use a special version of the PublishersBy-Author stored procedure that waits for a few seconds before returning, so I can test the affects of the SqlCommand objects returning out of order.

The BuildAndExecuteAsyncCommands routine is responsible for finalizing the configuration of the SqlParameter objects (they all require an input parameter to indicate which author is desired), opening the SqlConnection objects, and executing the BeginExecute function against all three SqlCommand objects. The first section of this routine subsequently captures each WaitHandle object from the IAsyncResult objects returned by the BeginExecute functions and places them in the WaitHandles array. Figure 11.29 shows how this is done in code.

The next step is up to you. At this point, you're free to execute whatever code needs to be (or can be) completed before waiting for the queries to complete.

When I'm ready to wait for the threads to complete, I set up three SqlDataReader objects to capture the returned rowsets. The example code then uses the WaitAny (in this case) to block the thread and wait for the asynchronous operation to complete (the ones I started with the BeginExecute functions). As they finish, their individual WaitHandle objects (in the WaitHandle array) change state and the WaitAll function returns the Index. At this point, I know that the asynchronous operation completed or it timed out. Notice that I gave the operation 12 seconds (12,000ms) to completethat's a really long time for any query to run. The logic in Figure 11.30 first tests to see if the specified thread (as indicated by the Index (Integer) returned by WaitAny has timed out. If so, I throw an exception and keep waiting for the other threads to complete or timeout.

At long last, I'm ready to capture the rows returned by each of the threads. I use the Index returned from the WaitAny function to choose which SqlCommand to process. This is illustrated in Figure 11.31. This part of the code should look pretty familiar by now.

As far as the example goes, there's little to do now but clean upclose the connections and free up the threads and other resources I allocated. The DataTable Load method I used in the ShowData routine closed the DataReader for me.

While this example application has a "Cancel" button, it's not much use. I don't waste any time between the time I execute the asynchronous commands and the time I start waiting for the results.