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())
            {

                dtNew.ImportRow(drrow);
            }

            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);

......

}

Post a Comment