Need help with asynchronous operation

Question

I'm relatively new to asynchronous and service-oriented programming and want to do the following:

1. Fire off a stored procedure in a database that could run for minutes or even hours.
2. Return a code to the caller of a job id that the client can use to track the progress of the job.

This seems like a simple task, but being new to asynchronous coding, I'm concerned about unknown pitfalls. Is there a well defined pattern for this type of functionality? If so, does it have a name and what is a good resource?

Answer 1

Check out the observer pattern.

From wikipedia:

The observer pattern (a subset of the asynchronous publish/subscribe pattern) is a software design pattern in which an object, called the subject, maintains a list of its dependents, called observers, and notifies them automatically of any state changes, usually by calling one of their methods. It is mainly used to implement distributed event handling systems.

Another good MSDN article on the observer pattern can be found here.

Answer 2

When running asynchronous calls is good custom to setup some kind of callback to handle the result of the procedure (I am not talking strictly about stored procedures, could be any asynchronously invoked call) - which could be a result set or simply a success/failure indicator.

If you make considerate use of asynchronous calls setting up some sort of safety net to catch the outcome and react in an appropriate way I can't see any pitfall with this approach (such approach is in fact often necessary in many cases).

For a practical example (.NET) of an asynchrounous call to a stored procedure (which seems to be relevant to your specific case) have a look here.

Answer 3

If you want to do it asynchronously you have 2 main ways:

Register a callback function with whatever resource you are using that it will call when complete.

or

If your resource doesnt have such functionality, fire up a thread that tracks all requests and polls for request completion at regular intervals. Every time a task completes, notify the owner via a callback.

Answer 4

In your case, you need to use the callback approach. This will set off the sql SP execution asynchronously and your code will be notified when the execution completes. In addition you can use the AsynResult object to poll if the operation has been completed or not.

Refer to the link SqlCommand..::.BeginExecuteReader Method (AsyncCallback, Object) for callback method.

If you need to poll too, you can poll on the IAsyncResult:

// Make the asynchronous call
IAsyncResult result = command.BeginExecuteReader(callback, command);

// Poll to see if complete
while (!result.IsCompleted)
{    
// Do more work here if the call isn't complete    
Thread.Sleep(100);
}

Refer to my answer on What is AsyncCallback? for all different types of asynchronous techniques explained in simple manner.