Multiple asynchronous method calls to method while in a loop

Question

I have spent a whole day trying various ways using 'AddOnPreRenderCompleteAsync' and 'RegisterAsyncTask' but no success so far.

I succeeded making the call to the DB asynchronous using 'BeginExecuteReader' and 'EndExecuteReader' but that is missing the point. The asynch handling should not be the call to the DB which in my case is fast, it should be afterwards, during the 'while' loop, while calling an external web-service.

I think the simplified pseudo code will explain best:
(Note: the connection string is using 'MultipleActiveResultSets')

private void MyFunction()
{
    "Select ID, UserName from MyTable"
    // Open connection to DB
    ExecuteReader();
    if (DR.HasRows)
    {
        while (DR.Read())
        {
            // Call external web-service
            // and get current Temperature of each UserName - DR["UserName"].ToString()
            // Update my local DB 
            Update MyTable set Temperature = ValueFromWebService where UserName = 
                    DR["UserName"];

            CmdUpdate.ExecuteNonQuery();
        }

        // Close connection etc
    }
}

Accessing the DB is fast. Getting the returned result from the external web-service is slow and that at least should be handled Asynchnously.

If each call to the web service takes just 1 second, assuming I have only 100 users it will take minimum 100 seconds for the DB update to complete, which obviously is not an option.
There eventually should be thousands of users (currently only 2).

Currently everything works, just very synchronously :)
Thoughts to myself:
Maybe my way of approaching this is wrong?
Maybe the entire process should be called Asynchnously?

Many thanx

Answer 1

Have you considered spinning this whole thing off into it's own thread?

Answer 2

What is really your concern ?

Avoid the long task blocking your application ? If so, you can use a thread (see BackgroundWorker)

Process several call to the web service in parallel to speed up the whole think ? If so, maybe the web service can be called asynchronously providing a callback. You could also use a ThreadPool or Tasks. But you'll have to manage to wait for all your calls or tasks to complete before proceeding to the DB update.

Answer 3

You should keep the database connection open for as short of a time as possible. Therefore, don't do stuff while iterating through a DataReader. Most application developers prefer to put their actual database access code on a separate layer, and in a case like this, you would return a DataTable or a typed collection to the calling code. Furthermore, if you are updating the same table you are reading from, this could result in locks.

How many users will be executing this method at once, and how often does it need to be refreshed? Are you sure you need to do this from inside the web app? You may consider using a singleton for this, in which case spinning off a couple worker threads is totally appropriate even if it's in the web app. Another thing to consider is using a Windows Service, which I think would be more appropriate for periodically updating data via from a web service that doesn't even have to do with the current user's session.

Answer 4

Id say, Create a thread for each webrequest, and do something like this:

extra functions:

int privCompleteThreads = 0;
int OpenThreads = 0;
int CompleteThreads
{
  get{ return privCompleteThreads; }
  set{ privCompleteThreads = value; CheckDoneOperations(); }
}
void CheckDoneOperations
{
  if(CompleteThreads == OpenThreads)
  {
    //done!
  }
}

in main program:

foreach(time i need to open a request)
{
  OpenThreads = OpenThreads + 1;
  //Create thread here
}

inside the threaded function:

//do your other stuff here

//do this when done the operation:
CompleteThreads = CompleteThreads + 1;

now im not sure how reliable this approach would be, its up to you. but a normal web request shouldnt take a second, your browser doesnt take a second loading this page does it? mine loads it as fast as i can hit F5. Its just opening a stream, you could try opening the web request once, and just using the same instance over and over aswell, and see if that speeds it up at all