How to stream rows from a SQL Server table into a .NET app, in 10000 row chunks?

Question

I have a table with 300 million rows in Microsoft SQL Server 2008 R2. There is a clustered index on the date column [DataDate] which means that the entire table is ordered by the date column.

How do I stream out the data from this table, into my .NET application, in 10000 row chunks?

Environment:

• Using C#.
• Have to be able to pause the data stream at any point, to allow the client to process the rows.
• Unfortunately, cannot use a select * from as this will select the entire table (its 50GB - it won't fit into memory).

Answer 1

You can use a combination of the following with LINQ to SQL:

Enumerable.Take function returns a specified number of contiguous elements from the start of a sequence.

Enumerable.Skip bypasses a specified number of elements in a sequence and then returns the remaining elements.

---

Refer to:

Accessing Rows In A LINQ Result Without A Foreach Loop?

The LINQ Enumerable Class, Part 2 - Positioning within Sequences

LINQ Enabled Paging

Answer 2

You can run SELECT * FROM, then loop through the results in a SqlDataReader.

This will never load more than one row into memory at a time; it will load each row as you call Read().