Saturday, April 21, 2012

How to do asp.net,C# custom paging like google using stored procedures with source code

This will give you some hints that How to do custom paging like google using stored procedures in  asp.net and sql server 2008. Its pretty simple but logical as well.  So i am placing my code which is simple to understand and you can modify it accordingly.

Stored Procedure:
Create PROCEDURE [dbo].[yourProcedurename]                           
@StartingIndex int,
@PageSize int
AS                                             
BEGIN
  with Slice as  
(  
      select row_number() over(order by id desc) as displayRowNum,*   
        from table_Name
)
select * from Slice where displayRowNum between @StartingIndex and @StartingIndex+@PageSize;
END  

C# Code:
Set your current page to paging bar...
 private int SetCurrentPageNumber()
    {
        int PageNum = 0;
        if (Request.QueryString["Page"] != null && Request.QueryString["Page"] != "" && Request.QueryString["Page"] != "0")
        {
            PageNum = Convert.ToInt32(Request.QueryString["Page"]);
        }
        return PageNum;
    }

Set page number for the next page...
private int CheckPageQS()
    {
        int Value;
        if (Request.QueryString["Page"] != null && Request.QueryString["Page"] != "" && Request.QueryString["Page"] != "1")
        {
            Value = ((Convert.ToInt32(Request.QueryString["Page"]) - 1) * PageSize) + 1;
            PageSize = PageSize - 1;
        }
        else
        {
            Value = 0;
        }
        return Value;
    }

Finally do the paging. pass the total number of pages to the method who do paging and apply your paging style.

 private void DoPaging(int TotalPages)
    {

        int CurrentPage = SetCurrentPageNumber();
        int StartingPage = CurrentPage - 7;
        int EndingPage = CurrentPage + 10;

        if (CurrentPage == 0)
        {
            CurrentPage = 1;
        }
        if (StartingPage < 1)
        {
            StartingPage = 1;
        }
        if (EndingPage > TotalPages)
        {
            EndingPage = TotalPages;
        }

        ///////////For First Pages/////////////
        if (CurrentPage <= 1)
        {
            HyperLink pg = new HyperLink();
            pg.Text = "First";
            //pg.NavigateUrl = "Default.aspx?page=" + Convert.ToString(i);
            pg.CssClass = "DisabledPage";
            pnlPaging.Controls.Add(pg);
        }
        else
        {
            HyperLink pg = new HyperLink();
            pg.Text = "First";
            pg.NavigateUrl = "?page=1";
            pg.CssClass = "EnabledPage";
            pnlPaging.Controls.Add(pg);
        }
        ///////////////////////////////////
        for (int i = StartingPage; i < EndingPage; i++)
        {
            if (i == CurrentPage)
            {
                HyperLink pg = new HyperLink();
                pg.Text = Convert.ToString(i);
                // pg.NavigateUrl = "?page=" + Convert.ToString(i);
                pg.CssClass = "DisabledPage";
                pnlPaging.Controls.Add(pg);
            }
            else
            {
                HyperLink pg = new HyperLink();
                pg.Text = Convert.ToString(i);
                pg.CssClass = "EnabledPage";
                pg.NavigateUrl = "?page=" + Convert.ToString(i);
                pnlPaging.Controls.Add(pg);
            }
        }

        //For Last Page
        if (CurrentPage >= TotalPages)
        {
            HyperLink pg = new HyperLink();
            pg.Text = "Last";
            // pg.NavigateUrl = "Default.aspx?page=" + Convert.ToString(i);
            pg.CssClass = "DisabledPage";
            pnlPaging.Controls.Add(pg);
        }
        else
        {
            HyperLink pg = new HyperLink();
            pg.Text = "Last";
            pg.NavigateUrl = "?page=" + TotalPages;
            pg.CssClass = "EnabledPage";
            pnlPaging.Controls.Add(pg);
        }

    }


Asp.NET Code:

<asp:Panel ID="pnlPaging" runat="server">

 </asp:Panel>



No comments:

Post a Comment