How to process SqlCommand result (rows) as they come?

Question

How to process SqlCommand result (rows) as they come? In other words, I am trying to do exactly what Sql Management Studio 2005 does when executing a query with many thousand rows. To me, it looks like as soon as Sql has found the first result, it notify the UI and display the rows as they come...

I suppose that it can be done asynchronously with BeginExecuteReader and EndExecuteReader, however (in my tests) the callback method is called only when the result set is completed. Any idea on how to replicate what Sql Management Studio 2005 does?

Thanks!

Answer 1

Don't do an async operation, but just call ExecuteReader and then iterate over the result set as it comes in.

using(SqlCommand cmd = new SqlCommand(......))
using(SqlDataReader rdr = cmd.ExecuteReader())
{
    while(rdr.Read())
    {
        // read and interpret row
        // possibly update GUI or something
    } 
}

If you need to update some UI or something while processing those, you can always call another method with a new row that's been read.

Marc

Answer 2

For your app to remain responsive, your main GUI thread has to be free to process incoming messages. This is even true for drawing to the screen; you may update a label's text, but it won't be displayed on screen until after the "update label text" windows message has been processed.

The easy way of being responsive is calling Application.DoEvents() frequently. This will basically process all incoming messages and return.

If you have single operations that take a long time, like the ExecuteReader(), you won't be able to call DoEvents() often enough. In that case you have to fall back to a background thread. One relatively easy way to use a background thread is the BackgroundWorker component. There's a nice example in this blog post.