Follow by Email

Wednesday, May 29, 2013

Using Asp.net GridView Sorting and Paging Example | Implementing Paging and sorting in gridview | GridView TemplateField Sorting

In this post i will show how to use GridView sorting and paging in asp.net

Sorting:



Paging: For Paging i have mentioned pagesize as 3. Hence After every 3 Records it will display the following 3 records to the next page.

For both sorting and paging Gridview event is generated which we handled in the code behind.

Source code:

Aspx :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Grid.aspx.cs" Inherits="Grid" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true" OnSorting="GridView1_Sorting" OnPageIndexChanging="GridView1_PageIndexChanging"
            AllowSorting="true" AllowPaging="true" PageSize="3" HeaderStyle-BorderStyle="Solid"
            HeaderStyle-Font-Bold="true" OnRowCancelingEdit="GridView1_OnRowCancelingEdit"
            OnRowEditing="GridView1_OnRowEditing" OnRowCommand="GridView1_OnRowCommand" OnRowUpdating="GridView1_OnRowUpdating"
            OnRowDeleting="GridView1_OnRowDeleting">
            <Columns>
                <asp:TemplateField HeaderText="Emp ID" SortExpression="EmpDeptID">
                    <ItemTemplate>
                        <asp:Label ID="lblEmpIdItem" runat="server" Text='<%# Bind("EmpDeptID") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Label ID="lblEmpIdEdit" runat="server" Text='<%# Bind("EmpDeptID") %>'></asp:Label>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Emp Name" SortExpression="EMpName">
                    <ItemTemplate>
                        <asp:Label ID="lblEmpNameItem" runat="server" Text='<%# Bind("[EMpName]") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEmpNameEdit" runat="server"></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtEmpNameFooter" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Salary" SortExpression="salary">
                    <ItemTemplate>
                        <asp:Label ID="lblSalaryItem" runat="server" Text='<%# Bind("[salary]") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtSalEdit" runat="server"></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtSalaryFooter" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <EditItemTemplate>
                        <asp:Button ID="btnUpdate" runat="server" Text="Update" CommandName="Update" ToolTip="update" />
                        <asp:Button ID="btnCancel" runat="server" Text="Cancel" CommandName="Cancel" ToolTip="cancel" />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="Edit" ToolTip="Edit" />
                        <asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete" />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Button ID="btnAdd" runat="server" Text="Add" CommandName="AddNew" CommandArgument="<%# ((GridViewRow) Container).RowIndex %>"
                            ToolTip="New" />
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>


Codebehind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class Grid : System.Web.UI.Page
{
   string conStr = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString.ToString();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bindgrid();
        }
    }

    public void bindgrid()
    {
        SqlConnection s1 = new SqlConnection(conStr);
        DataView dv = new DataView();
        s1.Open();

        string queryString = "SELECT * FROM employee";
        SqlDataAdapter adapter = new SqlDataAdapter(queryString, s1);

        DataSet employee = new DataSet();
        adapter.Fill(employee, "employee");
        if (ViewState["direction"] != null)
        {
          dv = new DataView(employee.Tables[0]);
          dv.Sort = ViewState["sortExpression"].ToString();
        }
        else
        {
            dv = employee.Tables[0].DefaultView;
        }

        s1.Close();

        GridView1.DataSource = dv;
        GridView1.DataBind();
    }

    public void GridView1_OnRowCancelingEdit(Object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        bindgrid();
    }

    public void GridView1_OnRowEditing(Object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        bindgrid();
    }

    public void GridView1_OnRowCommand(Object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "AddNew")
        {
            TextBox Empname = (TextBox)GridView1.FooterRow.FindControl("txtEmpNameFooter");
            TextBox Salary1 = (TextBox)GridView1.FooterRow.FindControl("txtSalaryFooter");

            SqlConnection Con = new SqlConnection(conStr);
            Con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Con;
            cmd.CommandText = "InsertSP";
            cmd.Parameters.AddWithValue("@empname", Empname.Text);
            cmd.Parameters.AddWithValue("@sal", Salary1.Text);
            cmd.Parameters.AddWithValue("@flag", 1);
            cmd.ExecuteNonQuery();
            cmd.Dispose();

            bindgrid();
        }
        
    }
    protected void GridView1_OnRowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow row = GridView1.Rows[e.RowIndex];

        Label EmpId = ((Label)(row.FindControl("lblEmpIdEdit")));
        TextBox EmpName = ((TextBox)(row.FindControl("txtEmpNameEdit")));
        TextBox Salary1 = ((TextBox)(row.FindControl("txtSalEdit")));

        SqlConnection Con = new SqlConnection(conStr);
        Con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = Con;
        cmd.CommandText = "InsertSP";
        cmd.Parameters.AddWithValue("@empname", EmpName.Text);
        cmd.Parameters.AddWithValue("@sal", Salary1.Text);
        cmd.Parameters.AddWithValue("@flag", EmpId.Text);
        cmd.ExecuteNonQuery();
        cmd.Dispose();

        GridView1.EditIndex = -1;
        bindgrid();
    }

    protected virtual void GridView1_OnRowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        GridViewRow row = GridView1.Rows[e.RowIndex];
        Label EmpId = ((Label)(row.FindControl("lblEmpIdItem")));

        SqlConnection Con = new SqlConnection(conStr);
        Con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = Con;
        cmd.CommandText = "DelSP";
        cmd.Parameters.AddWithValue("@empid", EmpId.Text);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        bindgrid();
    }

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        ViewState["sortExpression"] = e.SortExpression;
        string direction = string.Empty;
        if (SortDirection == SortDirection.Ascending)
        {
            SortDirection = SortDirection.Descending;
            ViewState["direction"] = "DESC";
        }
        else
        {
            SortDirection = SortDirection.Ascending;
            ViewState["direction"] = "ASC";
        }
        bindgrid();
    }
    public SortDirection SortDirection
    {
        get
        {
            if (ViewState["SortDirection"] == null)
            {
                ViewState["SortDirection"] = SortDirection.Ascending;
            }
            return (SortDirection)ViewState["SortDirection"];
        }
        set
        {
            ViewState["SortDirection"] = value;
        }
    }


    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        bindgrid();
    }
}