Search This Blog

Monday, December 2, 2013

Rank DataTable with LINQ C# Example

C# > LINQ > Rank

Example: Using LINQ to implement RANK function with DataTable.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public  DataTable RankDt(DataTable dt, string fld)
        {
            var rankDt = (from row in dt.AsEnumerable()
                            orderby row.Field<int>(fld) descending
                            select row).CopyToDataTable();

            rankDt.Columns.Add("Rank");
            int rank = 1;
            for (int i = 0; i < rankDt.Rows.Count - 1; i++)
            {
                rankDt.Rows[i]["Rank"] = rank;
                if (rankDt.Rows[i][fld].ToString() != rankDt.Rows[i + 1][fld].ToString())
                    rank++;
            }
            rankDt.Rows[rankDt.Rows.Count - 1]["Rank"] = rank;
            return rankDt;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add("Product");
            DataTable dt = ds.Tables[0];
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("quantity", typeof(Int32));
 
            DataRow dr = dt.NewRow();
            dr[0] = "Product 1" ;
            dr[1] = 500;
            dt.Rows.Add(dr);
            dr = dt.NewRow();

            dr[0] = "Product 2";
            dr[1] = 500;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = "Product 3";
            dr[1] = 50;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = "Product 4";
            dr[1] = 100;
            dt.Rows.Add(dr);

            dataGridView1.DataSource = RankDt(dt, "quantity");
        }
     }
}