Custom Pagination

Pagination Stored Procedure

CREATE PROCEDURE GetCustomersPageWise

      @PageIndex INT = 1

      ,@PageSize INT = 10

      ,@RecordCount INT OUTPUT

AS

BEGIN

      SET NOCOUNT ON;

      SELECT ROW_NUMBER() OVER

      (

            ORDER BY [CustomerID] ASC

      )AS RowNumber

      ,[CustomerID]

      ,[CompanyName]

      ,[ContactName]

     INTO #Results

      FROM [Customers]

     

      SELECT @RecordCount = COUNT(*)

      FROM #Results

           

      SELECT * FROM #Results

      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) *@PageSize + 1) + @PageSize) - 1

     

      DROP TABLE #Results

END

HTML Markup

<div>

PageSize:

<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">

    <asp:ListItem Text="10" Value="10" />

    <asp:ListItem Text="25" Value="25" />

    <asp:ListItem Text="50" Value="50" />

</asp:DropDownList>

<hr />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">

    <Columns>

        <asp:BoundField HeaderText="CustomerId" DataField="CustomerId" />

        <asp:BoundField HeaderText="ContactName" DataField="ContactName" />

        <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" />

    </Columns>

</asp:GridView>

<br />

<asp:Repeater ID="rptPager" runat="server">

<ItemTemplate>

    <asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>

</ItemTemplate>

</asp:Repeater>

</div>

C#

using System.Data.SqlClient;

using System.Configuration;

using System.Data;

private void GetCustomersPageWise(int pageIndex)

{

    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constring))

    {

        using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))

        {

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);

            cmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));

            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);

            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

            con.Open();

            IDataReader idr = cmd.ExecuteReader();

            GridView1.DataSource = idr;

            GridView1.DataBind();

            idr.Close();

            con.Close();

            int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);

            this.PopulatePager(recordCount, pageIndex);

        }

    }

}

private void PopulatePager(int recordCount, int currentPage)

{

    double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));

    int pageCount = (int)Math.Ceiling(dblPageCount);

    List<ListItem> pages = new List<ListItem>();

    if (pageCount > 0)

    {

        pages.Add(new ListItem("First", "1", currentPage > 1));

        for (int i = 1; i <= pageCount; i++)

        {

            pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));

        }

        pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));

    }

    rptPager.DataSource = pages;

    rptPager.DataBind();

}

protected void PageSize_Changed(object sender, EventArgs e)

{

    this.GetCustomersPageWise(1);

}

protected void Page_Changed(object sender, EventArgs e)

{

    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);

    this.GetCustomersPageWise(pageIndex);

}