Post
Ask Question
ashok kumar

ashok kumar

  • NA
  • 71
  • 0

Custome Paging

Jan 20 2013 9:42 AM
aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="paging.aspx.cs" Inherits="paging" %>

<!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>Custom SEO Friendly Paging</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblMessage" runat="Server" ForeColor="Red" EnableViewState="False"></asp:Label>
       
        <asp:Repeater ID="Repeater1" runat="server" EnableViewState="false">
            <ItemTemplate>
                <b>
                    <%# Eval("TESTID") %>.</b>
                <%# Eval("TEST_TOPPIC") %><br />
            </ItemTemplate>
        </asp:Repeater>
        <asp:Literal ID="litPaging" runat="server" EnableViewState="False"></asp:Literal>
        <asp:DropDownList ID="ddlindex" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlindex_SelectedIndexChanged">
            <asp:ListItem Selected="True" Text="Index" Value="5"></asp:ListItem>
            <asp:ListItem Selected="false" Text="10" Value="10"></asp:ListItem>
            <asp:ListItem Selected="false" Text="15" Value="15"></asp:ListItem>
            <asp:ListItem Selected="false" Text="20" Value="20"></asp:ListItem>
        </asp:DropDownList>
    </div>
    </form>
</body>
</html>


cs file

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;

public partial class paging : System.Web.UI.Page
{
    string connStr = ConfigurationManager.AppSettings["ArtSQLConnStr"].ToString();
    int _startIndex = 0;
    int _thisPage = 1;
    int _pageSize = 1;
    int _totalNumberOfRows = 0;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Request["start"] != null && Request["page"] != null)
            {
                int.TryParse(Request["start"].ToString(), out _startIndex);
                int.TryParse(Request["page"].ToString(), out _thisPage);
            }
            BindGridViewArticels();
        }
    }

    private void BindGridViewArticels()
    {
        DataTable dTable = new DataTable();
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            using (SqlCommand dCmd = new SqlCommand())
            {
                 _pageSize = Convert.ToInt32(ddlindex.SelectedValue);
                SqlParameter[] prms = new SqlParameter[4];
                prms[0] = new SqlParameter("@startRowIndex", SqlDbType.Int);
                prms[0].Value = _startIndex;
                prms[1] = new SqlParameter("@pageSize", SqlDbType.Int);
                prms[1].Value = _pageSize;
                prms[2] = new SqlParameter("@categoryParentID", SqlDbType.Int);
                prms[2].Value = 2;
                prms[3] = new SqlParameter("@totalCount", SqlDbType.Int);
                prms[3].Direction = ParameterDirection.Output;

                dCmd.CommandText = "LoadArticles";
                dCmd.CommandType = CommandType.StoredProcedure;
                dCmd.Parameters.AddRange(prms);
                dCmd.Connection = conn;
                using (SqlDataAdapter dAd = new SqlDataAdapter())
                {
                    dAd.SelectCommand = dCmd;
                    conn.Open();
                    dAd.Fill(dTable);
                    conn.Close();
                }
                _totalNumberOfRows = int.Parse(prms[3].Value.ToString());
                _totalNumberOfRows =Convert.ToInt32( Math.Ceiling(_totalNumberOfRows /Convert.ToDouble( ddlindex.SelectedValue)));
            }

        }
       
        Repeater1.DataSource = dTable;
        Repeater1.DataBind();
        litPaging.Text = GetPagingDone(_thisPage, _totalNumberOfRows, _pageSize, "paging.aspx", "");
    }

    private string GetPagingDone(int thisPageNo, int totalCount, int pageSize, string pageName, string extraQstringToAdd)
    {
        int pageno = 0;
        int loop =totalCount<10?totalCount:10;
        int i = 0;
        int remainder = totalCount % pageSize;
        StringBuilder strB = new StringBuilder("<br /><b><font color=\"green\">Page:</font> ", 500);

        if (thisPageNo > 6)
        {
            if (thisPageNo <= totalCount - 4)
            {
                i = thisPageNo - 6; loop = thisPageNo + 4;
            }
            else
            {
                i = totalCount-10; loop =totalCount  ;
            }
        }
        for (; i < loop; i++)
        {
            pageno = i + 1;
            if (pageno.Equals(thisPageNo))
                strB.Append(pageno + "&nbsp;| ");
            else
                if (i == loop - 1 && loop<=totalCount-1)
                    strB.Append("<a href=\"" + pageName + "?start=" + (pageno - 1) + "&page=" + pageno + extraQstringToAdd + "\" title=\"Go to Page " + pageno + "\">" + "..." + "</a> ");
                else
                strB.Append("<a href=\"" + pageName + "?start=" + (pageno-1) + "&page=" + pageno + extraQstringToAdd + "\" title=\"Go to Page " + pageno + "\">" + pageno + "</a> | ");
        }
        return strB.ToString() + "</b></span>";

    }
    protected void ddlindex_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindGridViewArticels();
    }
}


Database file

/*
DECLARE @COUNT INT
EXEC LoadArticles 0,5,2,@COUNT OUTPUT
PRINT @COUNT
*/

ALTER PROC LoadArticles
(
    @startRowIndex int,
    @pageSize int,
    @categoryParentID INT,
    @totalCount int output
)
AS
    BEGIN
        SET NOCOUNT ON;
        SET @totalCount = 0
        SET @startRowIndex = @startRowIndex + 1
        BEGIN
            SELECT * FROM (
            Select STUDENT_TEST.*, ROW_NUMBER() OVER (ORDER BY TESTID DESC) as RowNum
            FROM STUDENT_TEST WHERE    STUDENT_ID = 2) as ArticleList
            WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
            ORDER BY TESTID DESC      
            SELECT @totalCount = Count(TESTID) FROM STUDENT_TEST WHERE STUDENT_ID = 2

        END

END