Improve performance by using Asynchronous web method call?

The main focus of the project I am doing right now is about creating an Excel automation function (CEF) which allows user to fetch relevant data based on the provided parameters within the function. CEF will populate the cell wherever user uses the following formula, "=CEF(id, [param1],[param2], ..."

public string CEF(string id, string param1 = "", string param2 = "", string param3 = "", string param4 = "") 
{ 
    return results; 
}

Behind it is an web method (getDataString()) that is exposed to and called by CEF so that it can retrieve results from the database through the web service.

In the original implementation, I used the synchronized approach to call the web method, which turned out to be quite problematic. The Excel froze until all the results had been returned.

So I decided to try to use Asynchronous approach here (the example I followed), but it's actually giving me a new problem -- CEF always returns null.

void getDataStringCompleted(object sender, getDataStringCompletedEventArgs args)
{
    results = args.Result;
}

I guess the reason I always get the nulls is probably because CEF always gets hit f开发者_如何学Goirst and thus returns a null string. Currently I'm kinda stuck and don't know where to go from here. Can anyone help or give me some pointers? Thanks very much.

EDIT:

When I was debugging my code, I found that the code for hook-up event probably wasn't working correctly.

public string CEF(string id, string param1 = "", string param2 = "", string param3 = "", string param4 = "") 
{ 
    MyWebservice service = new MyWebservice();

    //Hookup async event handler
    service.getDataStringCompleted += new 
        getDataStringCompletedEventArgs(this.getDataStringCompleted); // <=problem 
    service.getDataStringAsync(id,param1, param2, param3, param4); // <=problem
    return results; 
}

I wonder if there is any way that I can call getDataStringCompleted first, and assign values to variable results and then return it in the method CEF.

Well, the first problem is that you're returning results inside CEF, when there aren't any.

public string CEF(string id, string param1 = "", string param2 = "", string param3 = "", string param4 = "") 
{ 
    MyWebservice service = new MyWebservice();

    //Hookup async event handler
    service.getDataStringCompleted += new 
        getDataStringCompletedEventArgs(this.getDataStringCompleted); 
    service.getDataStringAsync(id,param1, param2, param3, param4); 
    return results; // <- Isn't set yet!
}

In an Async call, you can't return results from where you call the method. It's the event handler that will have results, whenever it fires. In your case you're returning results before the event handler has set it. CEF in this case won't be able to return a value. Your event handler will have to get the value and the put it in the appropriate cell.

Sometimes when working with OFfice it's a lot harder to get async stuff working correctly. This is one of those times, sadly. I'm not really sure how you'd solve this if Excel needs CEF to return a value, because you'll have to block one way or another until you get one back from the service (and the easiest way to do that is just to call it synchronously).

Personally when there is a need of data being streamed directly to a spreadsheet I am a fan of the RTD function. In this case it would probably be overkill though. [Good article about RTD: http://support.microsoft.com/kb/285339]

When you said that your function returns "null" - do you refresh spreadsheet seconds or minutes later [F9] and the values are still not provided?

As far as I undertand the question, I think you will find what you need here.