Showing posts with label asp net gridview paging event. Show all posts
Showing posts with label asp net gridview paging event. Show all posts

Monday, January 19, 2015

Gridview Custom Paging in ASP. Net Using Stored Procedure

In This article I am going to explain about how to Create Custom Paging in Grid\view Control. But Before going in depth let us first all understand the need of custom paging.
Suppose your database contain more than thousand record and you need to display only Ten record per page. In This situation Custom paging is more useful be because you may not want to show 1000-10=990 extra record which reduce the performance and improve the complicity. 

 So create the custom paging first of all create Stored procedure because I will create custom paging through help of Stored Procedure.

 Create PROCEDURE custompaging
 (
       @PageIndex INT,
       @PageSize INT,
       @totalrecord INT OUTPUT
 )
     
AS
BEGIN
      SET NOCOUNT ON
      SELECT ROW_NUMBER() OVER( ORDER BY id ASC)AS RowNumber,id,name, salary INTO #Results FROM emp    
      SELECT @totalrecord = COUNT(*) FROM #Results          
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1    
      DROP TABLE #Results
END
 
Note: IN above declared procedure I have Used ROW_NUMBER() keyword which give us row numbers of records that you can select from the table.
Now Open a Empty web site and take a Gridview control for display the record of table and one Repeater control at the bottom of Gridview for create the paging.

Default.aspx code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<!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">
    <asp:GridView ID ="Gridview1" runat ="server" AutoGenerateColumns ="false" >
    <Columns>
    <asp:BoundField HeaderText ="Id" DataField ="rownumber" />
    <asp:BoundField HeaderText ="Name" DataField ="name" />
    <asp:BoundField HeaderText ="Salary" DataField ="salary" />
    </Columns>
    </asp:GridView>
    <asp:Repeater ID ="repeater1" runat ="server" >
    <ItemTemplate >
    <asp:LinkButton ID ="linkbutton1" runat ="server" Text ='<%#Eval("Text") %>' CommandArgument ='<%#Eval("Value") %>' Enabled ='<%#Eval("Enabled") %>' OnClick ="Page_size"></asp:LinkButton>
    </ItemTemplate>
    </asp:Repeater>
    </form>
</body>
</html>
 
Default.aspx.cs Code
 
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 _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        this.ShowdetailPageWise(1);
    }
 
    private void ShowdetailPageWise(int pageIndex)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Myconnection"].ConnectionString);
        SqlCommand cmd = new SqlCommand("custompaging", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@pageindex", pageIndex);
        cmd.Parameters.AddWithValue("@pagesize", 3);
        cmd .Parameters .Add ("@totalrecord",SqlDbType.Int,3);
        cmd.Parameters["@totalrecord"].Direction = ParameterDirection.Output;
        con.Open();
        IDataReader dr = cmd.ExecuteReader();
        Gridview1.DataSource = dr;
        Gridview1.DataBind();
        dr.Close();
        con.Close();
        int totalrecord = Convert.ToInt32(cmd.Parameters["@totalrecord"].Value);
        this.pagination(totalrecord, pageIndex);
     }
    public void pagination(int totalrecord, int pageindex)
    {  
        double totalpage = (double)((decimal)totalrecord  /3 );
        int pageCount = (int)Math.Ceiling(totalpage);
        List<ListItem> pages = new List<ListItem>();
        if (pageCount > 0)
        {                          
            pages.Add(new ListItem("First", "1", pageindex > 1));
                   
            for (int i = 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != pageindex));
            }         
            pages.Add(new ListItem("Last", pageCount.ToString(), pageindex  < pageCount));
        }
        repeater1.DataSource = pages;
        repeater1.DataBind();
}
    protected void Page_size(object sender, EventArgs e)
    {
       int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
       this.ShowdetailPageWise(pageIndex);
    }
}
 
Note: Above declared code I have used Two Method One for Show the table record in Gridview control and other method is Pagination which is used to create the custom paging at the bottom of Gridview.
 
I have created connection string in Web.config file.
 
  <connectionStrings>
    <add name="Myconnection" connectionString="Data Source=MCNDESKTOP08;Initial Catalog=pulkit;User ID=sa;Password=*********"/>
  </connectionStrings>
 
OutPut