Selecting second set of 20 row from DataTable

Question

I have a DataTable I am populating from SQL table with the following example columns

• ID
• Type
• Value

I am populating the DataTable with rows which are of a certain type. I want to select the rows 10 - 20 from my resulting DataTable:

Connect conn = new Connect();
SqlDataAdapter da = new SqlDataAdapter(SQL, conn.Connection());

//Creates data
DataTable d = new DataTable();
da.Fill(d);

DataRow[] result = d.Select();

In the above code I have omitted the main SQL, and currently I have no select for my DataRow array. I cannot find a way to reference the row numbers.

So for instance I am looking for something like Select("rownum > X && rownum < Y")

I have searched here, and a number of other resources to no avail. Any clues would be really handy, or just a simple not possible.

Answer 1

It's always better to select only what you need from the database(f.e. by using the TOP clause or a window function like ROW_NUMBER) instead of filtering it in memory.

However, you can use Linq-To-DataSet and Enumerable.Skip + Enumerable.Take:

var rows = d.AsEnumerable()
    .Skip(9).Take(11);  // select rows 10-20 as desired (so you want 11 rows)

If you want a new DataTable from the filtered result use CopyToDataTable, if you want a DataRow[] use rows.ToArray().

Answer 2

I would just do the 'take' and 'skip' command and keep it simple:

 d.Select().Skip(10).Take(10);  // skips 10 rows, then selects ten after that.

This would be assuming you have the Linq using set (using System.Linq)