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 fileusing 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 + " | ");
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