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



Friday 15 July 2011

Visualization Gauge Chart in asp.net

Introduction: Hello dears, in this article i will explain that how we can create Visualization: Gauge Chart using asp.net. This is very helpful artucle for the asp.net deveploer to creating a mast graph in asp.net. This type of chart you can create to showing  the percentage of the student,Cricket scoreetc

Implementation: Here i am taking the reference from the below link . http://code.google.com/apis/chart/interactive/docs/gallery/gauge.html
Google use javascript code for the implementation of this graph. I am using same javascript code but am merging also my asp.net experience with this code. And create the same graph in Asp.Net.
Code for .aspx page:
<head id="Head1" runat="server">
         <%--the below two javascripts  are needed to run this program you can
      also download this script from the below link --%>
 <script type='text/javascript' src='https://www.google.com/jsapi'></script>
</head>
<body>
    <form id="form1" runat="server">
     <div>
<%--here i am placing Literal control from the toolbox
to display a grapg  --%>
        <asp:Literal ID="lt" runat="server"></asp:Literal>
    </div>
     <div id='chart_div'></div>
    </form>
</body>
</html>
Code for aspx.cs page:

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.Text;
using System.Data.SqlClient;
using System.IO;
using System.Data.Sql;


public partial class speedometer_gauge : System.Web.UI.Page
{
// here i am taking a object of StringBuilder class named str  
  StringBuilder str = new StringBuilder();
    Admin_Class_Main_con.common qry = new Admin_Class_Main_con.common();
    SqlConnection con = new SqlConnection();
    protected void Page_Load(object sender, EventArgs e)
    {
        // here i am declare connection
        con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
        con.Open();
        if (Page.IsPostBack == false)
        {
            // here i am calling function  chart_bind(); in the page load event of the page
            bind_chart();
        }


    }
    private void bind_chart()
    {
        // here i am using SqlDataAdapter for the sql server select query
        SqlDataAdapter adp = new SqlDataAdapter("select top(7)* from tb_student_percentage", con);
        // here am taking datatable
        DataTable dt = new DataTable();
        try
        {
            // here datatale dt is fill wit the adp
            adp.Fill(dt);
            // this string m catching in the stringbuilder class
            // in the str m writing same javascript code that is given by the google.
            // my data that will come from the sql server
            // below code is same like as google's javascript code
            str.Append(@" <script type='text/javascript'>
        google.load('visualization', '1', {packages:['gauge']});
        google.setOnLoadCallback(drawChart);
        function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Label');
        data.addColumn('number', 'Value');");
            // inside the below line dt.Rows.Count explain that
            // how many rows comes in dt or total rows
            str.Append("data.addRows(" + dt.Rows.Count + ");");

            Int32 i;
            for (i = 0; i <= dt.Rows.Count - 1; i++)
            {
                // i need this type of output "  data.setValue(0, 0, 'Memory'); so on  in the first line so for this
                //m using i for the 0,1& 2 and so on . and after this i am writting zero and after this student_name using datatable
                str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["student_name"].ToString() + "');");
                // i need this type of output "   data.setValue(0, 1, 80);
                //so on  in the first line so for this
                //m using i for the 0,1& 2 and so on . and after this i am writting zero and after this percentage using datatable
                str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["percentage"].ToString() + ") ;");


                //   str.Append("['" + (dt.Rows[i]["student_name"].ToString()) + "'," + dt.Rows[i]["average_marks"].ToString() + "],");
            }

            str.Append("var chart = new google.visualization.Gauge(document.getElementById('chart_div'));");
            // in the below line i am setting the height and width of the Gauge using your own requrirement
            str.Append(" var options = {width: 800, height: 300, redFrom: 90, redTo: 100,");
            //  str.Append(" var chart = new google.visualization.BarChart(document.getElementById('chart_div'));");
            str.Append("yellowFrom:75, yellowTo: 90, minorTicks: 5};");
            str.Append(" chart.draw(data, options);}");
            str.Append("</script>");
            lt.Text = str.ToString().TrimEnd(',');


            con.Close();
        }
        catch
        {

        }
        finally
        {
            con.Close();
        }
    }
}
See output in this image:

Sql Script for database:

/****** Object:  Table [dbo].[tb_student_percentage]    Script Date: 07/15/2011 16:55:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_student_percentage]') AND type in (N'U'))
DROP TABLE [dbo].[tb_student_percentage]
GO
/****** Object:  Table [dbo].[tb_student_percentage]    Script Date: 07/15/2011 16:55:32 ******/
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_student_percentage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_student_percentage](
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [student_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [percentage] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tb_student_percentage] 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_student_percentage] ON
INSERT [dbo].[tb_student_percentage] ([id], [student_name], [percentage]) VALUES (1, N'bharat', N'85')
INSERT [dbo].[tb_student_percentage] ([id], [student_name], [percentage]) VALUES (2, N'vimal', N'56')
INSERT [dbo].[tb_student_percentage] ([id], [student_name], [percentage]) VALUES (3, N'amar', N'58')
INSERT [dbo].[tb_student_percentage] ([id], [student_name], [percentage]) VALUES (4, N'pardeep', N'58')
INSERT [dbo].[tb_student_percentage] ([id], [student_name], [percentage]) VALUES (5, N'munish', N'99')
INSERT [dbo].[tb_student_percentage] ([id], [student_name], [percentage]) VALUES (6, N'rajjo', N'100')
SET IDENTITY_INSERT [dbo].[tb_student_percentage] OFF

Conclusion
Through this article, you have learned how to create Visualization: Gauge Chart using the google’s javascript code as well as asp.net code.

Okas  guys, enjoy this code. Meet in next article again.

Thanx