How to retrieve data from Sql Server long query using C# in small steps

I want to get data from a long query like Sql Server Management Studio does. I mean, it displays small amounts of data as soon as is received from the server (in this case, SQL Server 2008).

I am using C# and NET 3.5. I have read documentation about BeginExecuteReader: this method starts the query asynchronously but the read operation is done synchronously.

Unfortunately, I haven't fo开发者_开发知识库und any real life example while googling for this :-( Can you help me with this topic?

My query is a simple one: SELECT [...] FROM [...] WHERE [...]

But there are many rows involved and I would like to show the user the firsts ones that match the query, without waiting for the whole operation to complete.

Many thanks!

You don't need to execute the reader asynchronously for what you want to accomplish. Here are a couple options for you:

Option 1: Display records as you read them (or batched) by using a SqlDataReader. Depending on your application, you may need to execute the reader on a separate thread and notify the UI thread to update the view accordingly (using a BackgroundWorker for example).

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        //read record, diplay record
    }
}

Option 2 (best option): Page your query results and show only a "screen" of data at a time.

You can try paginate the result set and get the chunk as soon as they were transfered from sql server database to the application.

1. Count the result set.
2. With the count number, paginate through the result set with a page size like 1000 (or more) (Use ROW_NUMBER on Sql Server)
3. Display the first result set (paginated), than append the rest as soon as they return.

Hope it helps.