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 1^st,2^nd, 3^rHighest 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:

<title>Nth Highesr salary of the employee</title>

</head>

<body>

<div>

Select value <asp:DropDownList ID="dd_rank" runat="server" AutoPostBack="True"

onselectedindexchanged="dd_rank_SelectedIndexChanged">

</asp:DropDownList>

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

CellPadding="4" ForeColor="#333333" GridLines="None">

<asp:TemplateField HeaderText="employee_name">

<asp:Label ID="Label1" runat="server" Text='<%# Eval("emp_name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="salary">

<asp:Label ID="Label2" runat="server" Text='<%# Eval("salary") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

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

//12000(2)

//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]

/****** Object: Table [dbo].[tb_emp_salary] Script Date: 07/26/2011 16:12:22 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

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

Search This Blog

Using ASP.NET

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 1^st,2^nd, 3^rHighest salary of the employee and so on for any company ,shop etc.

Comments

Post a Comment

Popular posts from this blog

Sending reset password link for one time use only in asp.net

add delete update inside gridview using store procedure in ASP.NET

Change password using asp.net

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.

Comments

Post a Comment

Popular posts from this blog

Sending reset password link for one time use only in asp.net

add delete update inside gridview using store procedure in ASP.NET

Change password using 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 1^st,2^nd, 3^rHighest salary of the employee and so on for any company ,shop etc.