Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
653 views
in Technique[技术] by (71.8m points)

c# - SqlDataAdapter.Fill - Asynchronous approach

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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());

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...