Method "Partial" return

Question

I'm developing a method that searches for info in a very large table. Since I don't use ORDER BY or anything special in the query (just a simple SELECT id,description FROM complain WHERE description like 'YOUR TEXT HERE'"), I was hoping to provide a more dynamic user experience by returning batches of results. This would be something similar to running a query in Management Studio.

A few details, my call stack is not large, but not everything is in the same method. There's buttonSearchClick, performCleanSearch and searchComplainBasedOnDetailInfo each of those in a different layer (Interface, SearchBLL and SearchDAL respectively).

I thought about creating an async method that fills something like a List<Complain> but that doesn't seem as clean. I would have to make 3 layers of async. Does anyone have any better ideas on how to implement this? Or is this the best way to do it?

Edit1: I've managed to use SqlCommand.BeginExecuteReader along to Async Processing on the connection string to fetch the results from the query as they appear... now i have to figure out a way to make my DAL method be async so the upper layer can fetch the results also async... i was thinking in implementing a buffer of some kind... maybe a queue...

Edit2: I'm not looking for a paging solution or twitter like one (where you scroll and new results are searched) because I know for a fact that the user will have to read all the information im fetching...

This might be of use to you http://stackoverflow.com/questions/9234730/respond-to-async-sql-calls-as-they-complete — Jason Evans, Mar 13 '13 at 14:12
Similar to what @Luke said - you might consider paging: http://stackoverflow.com/questions/5335797/microsoft-sql-server-paging — chue x, Mar 13 '13 at 14:17
@LukeHennerley that is a synchronous method... Chue: i dont have that much complains by costumers by product... i have alot of complains in general which makes the search quite long, but the end result are 10 rows top... — Leonardo, Mar 13 '13 at 14:34

score 1 · Answer 1 · answered Mar 13 '13 at 14:21

1

You could use a BackgroundWorker and in your DoWork get your batches doing something like:

DataTable dt;
int iRecords = 0;
do
{
  dt = new DataTable();
  using(SqlConnection con = new SqlConnection(""))
  {
    SqlCommand cmd = new SqlCommand(string.Format("SELECT TOP 100 * FROM complain where ID > {0}", iRecords));
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    sda.Fill(dt);
    //Report your progress here
  }
} while(dt.Rows.Count != 0)

answered Mar 13 '13 at 14:21

LukeHennerley

6,344
1
32
50

doesn't "Fill" only return when the query is done?!? i mean that Fill does not seem to be async... – Leonardo Mar 13 '13 at 14:29
@Leonardo It doesn't need to be `async` you are executing and returning bitesize chunks on a worker thread. If you want more control over execution of database queries, consider entity framework and combining that with the `TaskLibrary`. – LukeHennerley Mar 13 '13 at 14:31

score 0 · Answer 2 · answered Mar 13 '13 at 14:28

0

You can return an IEnumerable<Task<IEnumerable<T>>> (T will be set to whatever type your items are) from your method. Each Task<IEnumerable<T>> will represent batch of information potentially received in the future. The method itself could either be an iterator block that yields each batch as it fetches it, or depending on your method of querying you may be able to batch the entire sequence without needing to write an iterator block.

Then from the caller's perspective they can write something like this:

foreach(Task<IEnumerable<T> batch in GetBatches())
{
    updateUIWithBatch(await batch);
}

answered Mar 13 '13 at 14:28

Servy

202,030
26
332
449

Could you provide a more extensive code sample or maybe point where i could find a more "end-to-end" sample? – Leonardo Mar 13 '13 at 16:55
@Leonardo Not really, without knowing how you're actually querying the various batches. – Servy Mar 13 '13 at 16:56

Method "Partial" return

2 Answers2