How To Use Multi Threading To Call Stored Procedure For Each Of Item In Collection

Have a job which does the following 2 tasks Read up to 300 unique customerId from database into a List. Then Call a stored procedure for each customerId, which execute queries in

Solution 1:

You have to choose among several options for implementation. First of all, choose the schema you are using. You can implement your algorithm in co-routine fashion, whenever the thread needs some long-prepairing data, it yields the execution with await construction.

// It can be run inside the `async void` event handler from your UI.// As it is async, the UI thread wouldn't be blockedasync Task SaveAll()
{
    for(int i = 0; i < 100; ++i)
    {
         // somehow get a started task for saving the (i) customer on this threadawait SaveAsync(i);
    }
}

// This method is our first coroutine, which firstly starts fetching the data// and after that saves the result in databaseasync Task SaveAsync(int customerId)
{
    // at this point we yield the work to some other method to be run// as at this moment we do not do anythingvar customerData = await FetchCustomer(customerId);
    // at this moment we start to saving the data asynchroniously// and yield the execution another timevar result = await SaveCustomer(customerData);
    // at this line we can update the UI with result
}

FetchCustomer and SaveCustomer can use the TPL (they can be replaced with anonymous methods, but I don't like this approach). Task.Run will execute the code inside the default ThreadPool, so UI thread wouldn't be blocked (more about this method in Stephen Cleary's blog):

async Task<CustomerData> FetchCustomer(int customerId)
{
    await Task.Run(() => DataRepository.GetCustomerById(customerId));
}

// ? here is a placeholder for your result typeasync Task<?> SaveCustomer(CustomerData customer)
{
    await Task.Run(() => DataRepository.SaveCustomer(customer));
}

Also I suggest you to examine this articles from that blog:

• StartNew is Dangerous
• Async and Await
• Don't Block on Async Code
• Async/Await - Best Practices in Asynchronous Programming

Another option is to use the TPL Dataflow extension, very similar to the this answer:

Nesting await in Parallel foreach

I suggest you to examine the contents of linked post, and decide for yourself, which approach will you implement.

Solution 2:

1. I would try to solve your task completely within SQL. This will greatly reduce server roundtrips.

2. I think you can create a enumeration of tasks, each one dealing with one record, and then to call Task.WhenAll() to run them all.