How to apply paging to a dataset?

Question

How can I apply paging to a dataset? I have a method in which I dynamically build a table from a dataset. Querying the database from which I get my dataset from is not an issue but iterating the datarows from the dataset is slow.

To increase performance when loading a page containing a lot of datarows I want to apply paging ability.

A feature that would be good to have is ability for the user to set pagesize (how many rows to display on each page).

Answer 1

You can take a GridView to show the data and use its paging ability: http://msdn.microsoft.com/en-us/library/5aw1xfh3%28v=VS.80%29.aspx

Answer 2

Linq is best option.

ROW_NUMBER() is also an option in SQL server 2005

Answer 3

If your data is a single DataTable then you can use the AsEnumerable() extension method. That will return the data as an IEnumerable collection. You can then use the LINQ extension methods .Skip() and .Take().

IEnumerable<DataRow> MyDataPage = MyDataTable.AsEnumerable().Skip(100).Take(10);

The above code would give you rows 101 to 110 of MyDataTable and it would be an IEnumerable collection which you can bind just like a data table. If you need it to be an actual DataTable you can just call CopyToDataTable() thus:

DataTable NewDT = MyDataPage.CopyToDataTable();

More detailed info is available here