Working with a database using Threadpool or Asyn

Question

I have a database with a lot of records. (database values ​​are updated via webservice)

Each record / line is of type (id, xmlfilename, operation, parameters, operationId, status), i.e. we perform the operation indicated by the "operation" in the xmlfile file, the specified "xmlfilename" with the parameters for the operation, indicated by the "parameters" .. the status part is "intimate" free, as it is updated as with reqd.

I need to perform this (every line) operation using Threads .. one thread per 'id' number of records. As long as there are "id" lines in the db selection and "operations" are performed

how can I do this efficiently with maximum parallelism and / or concurrency.

Is there a better way?

What is threadpool or user thread or asyn programming best for?

Edit Added: Here is the pseudo code I tried

string operationId=null;

while(operationId = DBWorker.getNextFreeOperationId ())
//how do i check this?another query??untill there are operations with status "Free" 
//,keep selecting operationids 
//note db is updating asynchronously.    
{ 
  //retrieve all rows with operationid=operationId eg:800 . 1 thread/qid???? and
  // status="free" ...
//there are multiple operations with same operationIds 

  DataRowCollection dbRows=DBWorker.retrieveQueuedEntries(operationId); 
  MyWorkItem workItem = new DBWorker.MyWorkItem();
  workItem.DataRows = dbRows; 
  workItem.Event = new AutoResetEvent(false); 
 //MyWorkItem.DoWork will do the necessary "Operation" 
 ThreadPool.QueueUserWorkItem(new WaitCallback(workItem.DoWork),workItem); 
} 
--------------
 MyWorkItem.DoWork(obj x){ 
//for brevity 
for each DataRow row in this.DataRows
  {
    performOperation(row);//use row["operation"] ..
  }
---------------
bool performOperation(DataRow row)
{
  string operation = (string)row["operation"];//retrieve other similarly
  switch(operation)
  {
    case Operations.Add: //call Add operation ..
    ...
  }
}
------------

the above code uses .net2.0 .. does not achieve multithreading .. the script may be, the database is updated from one end asynchronously, while the code will work as a Windows service at the same time.

THX
Amit

Answer 1

If you do not use .net 4.0 - use ThreadPool I would create a function that would receive a string (an event is better if you convert strings for a strict type of objects to DAL) that it should process and do what it has to it, - sort of

using System;
using System.Threading;

namespace SmallConsoleAppForTests
{
class Program
{

    private static AutoResetEvent[] events;

    static void Main(string[] args)
    {

        int dataLength = 3;
        // creating array of AutoResetEvent for signalling that the processing is done
        AutoResetEvent[] events = new AutoResetEvent[dataLength];

        // Initializing the AutoResetEvent array to "not-set" values;
        for (int i = 0; i < dataLength; i++)
            events[i] = new AutoResetEvent(false);

        //Processing the data
        for (int i = 0; i < dataLength; i++)
        {
            var data = new MyWorkItem { Event = events[i], Data = new MyDataClass() };

            ThreadPool.QueueUserWorkItem(x =>
            {
                var workItem = (MyWorkItem)x;
                try
                {
                    // process the data

                }
                catch (Exception e)
                {
                    //exception handling
                }
                finally
                {
                    workItem.Event.Set();
                }
            }, data);
        }

        //Wait untill all the threads finish
        WaitHandle.WaitAll(events);
    }




}

public class MyWorkItem
{
    public AutoResetEvent Event { get; set; }
    public MyDataClass Data { get; set; }
}

// You can also use DataRow instead
public class MyDataClass
{
    //data
    //
}
}

.net 4.0 - " " (PLINQ)