Wednesday, December 2, 2009

Return top N rows from datatable

Normally to return top N rows we use an SQL statement similar to the one below

Select top N * from table

but How can we achieve the same thing in DataTable
I have seen many examples, using different methods. Most of the methods centered around the idea of creating new columns with automatic values increment and using them as index 

There is better method using LINQ 

public  DataTable GetTopNFromDataTable(int TopRowCount, DataTable dtSource)

            var dtTrec = from item in dtSource.AsEnumerable()
                         select item;
            var topN = dtTrec.Take(TopRowCount);
            DataTable dtNew = new DataTable();
            dtNew = dtSource.Clone();

            foreach (DataRow drrow in topN.ToArray())


            return dtNew;

var dtTrec - stores the item in datatable, Using the Take function of Linq the first N rows is filtered

            var topN = dtTrec.Take(TopRowCount);

Now how to retrieve the rows between N1 & N2, just use the skip function along with Take as shown below

public DataTable GetTopBetweenFromDataTable(int intFrom, int intTo, DataTable dtSource)

   var topN = dtTrec.Skip(intFrom).Take(intFrom);



1 comment:

Anonymous said...

Dim query = From c In dt.AsEnumerable _
Order By c.Field(Of Decimal)("list_order") Descending, _
c.Field(Of DateTime)("insert_date") Descending

Dim query1 = From c In query.Take(5).AsEnumerable
rptNews.DataSource = query1