Tuesday 26 July 2011

nth highest salary of the employee in asp.net


Introduction: Hello guys, in this article i will explain that how we can find nth highest salary of the employee using asp.net. This is very helpful artucle for the asp.net deveploer , to find the 1st,2nd, 3rHighest salary of the employee and so on for any company ,shop etc.

Implementation: Create a page named nth highest salary. Drag and drop a dropdownlist and Gridview from the  toolbox. Below I am giving complete  code for .aspx page and .aspx.cs page

Code for .aspx page:
<head runat="server">
    <title>Nth Highesr salary of the employee</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
       Select value  <asp:DropDownList ID="dd_rank" runat="server" AutoPostBack="True"
            onselectedindexchanged="dd_rank_SelectedIndexChanged">
        </asp:DropDownList>
   
        <br />
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            CellPadding="4" ForeColor="#333333" GridLines="None">
            <RowStyle BackColor="#E3EAEB" />
            <Columns>
                <asp:TemplateField HeaderText="employee_name">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("emp_name") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="salary">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("salary") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#7C6F57" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <br />
        <br />
   
    </div>
    </form>
Code for aspx.cs page:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd;
    SqlDataAdapter adp;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        // here i am creating the connection with the database
        con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
        con.Open();
        try
        {

               if(Page.IsPostBack == false)
            {
                // here  i am calling the function that will bind the dropdownlist
                bind_dd_rank();

            }
        }
        catch
        {
           
        }

    }
    private void bind_dd_rank()
    {
        try
        {
            // here i am binding gridview with the column name rank
            // in the below sql query am finding that how many type of salary. suppose there will be the salary of the  3 employee
            // will 13000. it count it only 1 time. for eg:
            //13000(1)
            //13000(1)
            //13000(1)
            //13000(1)
            //13000(1)
            //12000(2)
            //12000(2)
            //12000(2)
            //12000(2)
            //10000(3)
            //10000(3)
            //10000(3)
            //10000(3)
            //10000(3)
            // below quesry will give the output

            //  Salary        rank
            //  13000           1
            //  12000           2
            //  10000           3
            // here m binding the dropdownlist with the rank so the dropdownlist
            // show the value 1,2,3 and so on according to the values of the database
            cmd = new SqlCommand(@"select salary, ROW_NUMBER () OVER (order by salary desc) as rank from
            tb_emp_salary group by salary order by salary desc");
            cmd.Connection = con;
        
            SqlDataReader a;
            // here  i am using the ExecuteReader to bind the gridview
            dd_rank.AppendDataBoundItems = true;
            ListItem li = new ListItem();
            li.Text = "Select";
            li.Value = "Select";
            dd_rank.Items.Add(li);
            a = cmd.ExecuteReader();
            dd_rank.DataSource = a;
            // here m binding the dropdownlist with the rank field
            dd_rank.DataValueField = "rank";
            dd_rank.DataBind();
            a.Close();

        }
        catch

        {

        }
    }
    // this function will use to bind the gridview
        private void gdd_bind()
        {
        try
        {
           
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            // here am using sql query to find nth salary of the employee
            //        SELECT TOP 1 salary
            //FROM (
            //SELECT DISTINCT TOP 6 salary
            //FROM employee
            //ORDER BY salary DESC) a
            //ORDER BY salary
            // in the above query you can find 6th highest salary of the employee
            // there is a sub query inside the query that will find the top 6 salaries in desc order
            // then from these top 6 we will find the top 1 order by desc order.
            // below i am using this query but inplace of 6 i am passing value from the dropdownlist
            // so you can find nth salary of the employee..
            // for eg: 1st,2nd,3rd,4th,5th and so on according to the database
            adp = new SqlDataAdapter(@"SELECT * FROM tb_emp_salary where salary =(SELECT TOP 1 salary FROM (SELECT DISTINCT TOP " + dd_rank.SelectedItem.Text + " salary FROM tb_emp_salary ORDER BY salary DESC) a ORDER BY salary)", con);
            dt = new DataTable();
            adp.Fill(dt);
            adp.Dispose();
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
        catch
        {

        }                                 
        }
         protected void dd_rank_SelectedIndexChanged(object sender, EventArgs e)
    {
        // here i am calling function for binding the gridview
        // when user will select any value from the dropdownlist
        // then this function performs and bind the gridview with the nth highest salary
        //
        gdd_bind();
    }
}
See output in this image:


Sql Script for database:

/****** Object:  Table [dbo].[tb_emp_salary]    Script Date: 07/26/2011 16:12:22 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_emp_salary]') AND type in (N'U'))
DROP TABLE [dbo].[tb_emp_salary]
GO
/****** Object:  Table [dbo].[tb_emp_salary]    Script Date: 07/26/2011 16:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_emp_salary]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_emp_salary](
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [emp_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [salary] [int] NULL,
 CONSTRAINT [PK_tb_emp_salary] PRIMARY KEY CLUSTERED
(
      [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[tb_emp_salary] ON
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (1, N'vimal', 12000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (2, N'harish', 10000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (3, N'parvesh', 10000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (4, N'aman', 10000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (5, N'deepak', 9000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (6, N'anver', 9000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (7, N'ranbeer', 8000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (8, N'neetu', 8000)
INSERT [dbo].[tb_emp_salary] ([id], [emp_name], [salary]) VALUES (9, N'renu', 7000)
SET IDENTITY_INSERT [dbo].[tb_emp_salary] OFF

Conclusion
Through this article, you have learned how to find nth highest salary of the employee in asp.net .

Okas  guys, enjoy this code..

Thanx



No comments:

Post a Comment