c# - SqlDataAdapter.Fill - Asynchronous approach

Question

Using C# / .NET 3.5.

Currently I'm populating 2 DataTables one after the other using SqlDataAdapter.Fill().

I want to populate both of these DataTables in parallel, at the same time by doing each one asynchronously. However, there is no asynchronous version of the Fill() method - i.e. BeginFill() would be great!

One approach I've tried is (pseudo):

1. SqlCommand1.BeginExecuteReader // 1st query, for DataTable1
2. SqlCommand2.BeginExecuteReader // 2nd query, for DataTable2
3. SqlCommand1.EndExecuteReader
4. SqlCommand2.EndExecuteReader
5. DataTable1.Load(DataReader1)
6. DataTable2.Load(DataReader2)

However, DataTable.Load() takes a long time:
It takes 3 seconds to do step 1 to step 4.
Step 5 then takes 22 seconds.
Step 6 takes 17 seconds.
So, combined 39 seconds for steps 5 and 6.

The end result is, this gives me no benefit over just doing 2 SqlDataAdapter.Fills, one after the other. I want the net result to be that the entire process takes only as long as the longest query (or as close to that as possible).

Looking for recommended ways forward to end up with something that is truly an asynchronous approach to filling a DataTable.

Or do I just manage it myself and roll 2 separate threads, each one filling a DataTable?

Answer 1

I would suggest have a separate worker thread for each. You could use ThreadPool.QueueUserWorkItem.

List<AutoResetEvent> events = new List<AutoResetEvent>();

AutoResetEvent loadTable1 = new AutoResetEvent(false);
events.Add(loadTable1);
ThreadPool.QueueUserWorkItem(delegate 
{ 
     SqlCommand1.BeginExecuteReader;
     SqlCommand1.EndExecuteReader;
     DataTable1.Load(DataReader1);
     loadTable1.Set();
});

AutoResetEvent loadTable2 = new AutoResetEvent(false);
events.Add(loadTable2);
ThreadPool.QueueUserWorkItem(delegate 
{ 
     SqlCommand2.BeginExecuteReader;
     SqlCommand2.EndExecuteReader;
     DataTable2.Load(DataReader2);
     loadTable2.Set();
});

// wait until both tables have loaded.
WaitHandle.WaitAll(events.ToArray());