I have a script which runs through a database table and downloads a file for each row, adds to a results table in memory, then bulk uploads all the results back to the database once finished.
The problem I have is that there could be thousands of files to download and the script could timeout or error half way through.
Is there a better approach to this, maybe involving threading or asynchronous calls?
Threading seems to be the way to go.. you should have one, or many threads that reads rows from the db(if you want many threads, you should partition the read accordingly) and putting them in some sort of concurrent collection(either .net 4 build-in ones, or built/download a custom one). then you should have a thread collection who will get items from that list and get the file, if he timeouts, he should put the task back to the collection..
this is a basic producer-consumer threading pattern. you can easly find many examples in google.
Is it possible to persist the results back to the database after each download operation? That way you could compare the rows of the two tables in order to pick up where you left off in the case of some kind of timeout or error. Threading might make it faster, but it wont solve the problem you asked about on its own.
If you have a lot of time for the script to run (running once at midnight everyday etc), then the easiest solution is to persist to the database for every X download.
Perhaps this may be a good candidate for a cloud app. Bandwidth, queueing for async processing, scalable on a timely basis?
If the reason for timeout is the number of files to download, I recommend to use ThreadPool for asynchronous calls.
At first, set the maximum number of parallel threads using ThreadPool.SetMaxThreads. Then you can queue tasks with ThreadPool.QueueUserWorkItem. This practice will guarantee the maximum number of concurrent threads. All above the maximum number will be waiting until one of the thread in pool is finished.
I wouldn't do this type of operation in a script. Instead I'd have some type of program, probably running as a windows service, which would actually perform the job of downloading all of those files and updating the relevant records.
If it is only supposed to run when a user clicks a button, then I'd have the service monitor a table for a command to execute. Once it detects that command then kick off and go.
Not sure of the pattern name here, but it's basically like a job queuing system.