First create a table (requires automatic numbering of IDs):
create table redheadedfile(
id int identity(1,1),
filenames nvarchar(20),
senduser nvarchar(20),
primary key(id)
)
Then we write 500,000 records:
declare @i int
set @i=1
while @i<=500000
begin
insert into redheadedfile(filenames,senduser) values('my paging algorithm','Lu Junming')
set @i=@i+1
end
GO
Use Microsoft Visual Studio .NET 2003 to create a WebForm web page (I named it webform8.aspx)
The front-end code snippet is as follows (webform8.aspx):
<%@ Page language="c#" Codebehind="WebForm8.aspx.cs" AutoEventWireup="false" Inherits="WebApplication6.WebForm8" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm8</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content=" http://schemas.microsoft.com/intellisense/ie5 " name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:datalist id="datalist1" AlternatingItemStyle-BackColor="#f3f3f3" Width="100%" CellSpacing="0"
CellPadding="0" Runat="server">
<ItemTemplate>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="30%"
align="center"><%#DataBinder.Eval(Container.DataItem,"filenames")%></td>
<td width="30%"
align="center"><%#DataBinder.Eval(Container.DataItem,"senduser")%></td>
<td width="30%"
align="center"><%#DataBinder.Eval(Container.DataItem,"id")%></td>
</tr>
</table>
</ItemTemplate>
</asp:datalist>
<div align="center">Total<asp:label id="LPageCount" Runat="server" ForeColor="#ff0000"></asp:label>Pages/Total
<asp:label id="LRecordCount" Runat= "server" ForeColor="#ff0000"></asp:label>Record
<asp:linkbutton id="Fistpage" Runat="server"
CommandName="0">Home</asp:linkbutton> <asp:linkbutton id="Prevpage" Runat="server" CommandName ="prev">
Previous page</asp:linkbutton> <asp:linkbutton id="Nextpage" Runat="server"
CommandName="next">Next page</asp:linkbutton > <asp:linkbutton id="Lastpage" Runat="server"
CommandName="last">Last page</asp:linkbutton> Current page<asp: label id="LCurrentPage" Runat="server"
ForeColor="#ff0000"></asp:label>Page Jump page<asp:TextBox ID="gotoPage" Runat="server" Width ="30px"
MaxLength="5" AutoPostBack="True"></asp:TextBox></div>
</form>
</body>
</HTML>
The background code snippet is as follows (webform8.aspx.cs)
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Configuration;
namespace WebApplication6
{
/// <summary>
/// Summary description of WebForm8.
/// </summary>
public class WebForm8 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.LinkButton Fistpage;
protected System.Web.UI.WebControls.LinkButton Prevpage;
protected System.Web.UI.WebControls.LinkButton Nextpage;
protected System.Web.UI.WebControls.LinkButton Lastpage;
protected System.Web.UI.WebControls.DataList datalist1;
protected System.Web.UI.WebControls.DropDownList mydroplist;
protected System.Web.UI.WebControls.Label LPageCount;
protected System.Web.UI.WebControls.Label LRecordCount;
protected System.Web.UI.WebControls.Label LCurrentPage;
protected System.Web.UI.WebControls.TextBox gotoPage;
const int PageSize=20; //Define the records displayed on each page
int PageCount,RecCount,CurrentPage,Pages,JumpPage;//Define several variables to save paging parameters
private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
RecCount = Calc();//Get the total number of records through the Calc() function
PageCount = RecCount/PageSize + OverPage();//Calculate the total number of pages (plus the OverPage() function to prevent remainders from causing
incomplete display data)
ViewState["PageCounts"] = RecCount/PageSize -
ModPage();//Save Total page parameters to ViewState (minus the ModPage() function to prevent the query range from overflowing when the SQL statement is executed. You can use the stored procedure paging algorithm to understand this sentence)
ViewState["PageIndex"] = 0;//Save a page index value of 0 to ViewState
ViewState["JumpPages"] = PageCount;//Save PageCount to ViewState, and determine whether the user input number exceeds the
page number
range when jumping.
//Display the status of LPageCount and LRecordCount
LPageCount.Text = PageCount.ToString();
LRecordCount.Text = RecCount.ToString();
//Determine whether the page jump text box is invalid
if(RecCount <= 20)
gotoPage.Enabled = false;
TDataBind();//Call the data binding function TDataBind() to perform data binding operations
}
}
//Calculate remaining pages
public int OverPage()
{
int pages = 0;
if(RecCount%PageSize != 0)
pages = 1;
else
pages = 0;
return pages;
}
//Calculate the remaining pages to prevent the query range from overflowing when the SQL statement is executed.
public int ModPage()
{
int pages = 0;
if(RecCount%PageSize == 0 && RecCount != 0)
pages = 1;
else
pages = 0;
return pages;
}
/*
*Static function to calculate total records
*The reason why I use static functions here is: if static data or static functions are referenced, the connector will optimize the generated code and remove the dynamic relocation items (
the paging effect of massive data tables is more obvious).
*I hope you will give me your opinions and correct me if there are any mistakes.
*/
public static int Calc()
{
int RecordCount = 0;
SqlCommand MyCmd = new SqlCommand("select count(*) as co from redheadedfile",MyCon());
SqlDataReader dr = MyCmd.ExecuteReader();
if(dr.Read())
RecordCount = Int32.Parse(dr["co"].ToString());
MyCmd.Connection.Close();
return RecordCount;
}
//Database connection statement (obtained from Web.Config)
public static SqlConnection MyCon()
{
SqlConnection MyConnection = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
MyConnection.Open();
return MyConnection;
}
//Operate the CommandName values returned by the four buttons (Home Page, Previous Page, Next Page, Last Page)
private void Page_OnClick(object sender, CommandEventArgs e)
{
CurrentPage = (int)ViewState["PageIndex"];//Read the page number value from ViewState and save it to the CurrentPage variable for parameter
calculation
Pages = (int)ViewState["PageCounts"];//Read the total page parameter operation string from ViewState
cmd = e.CommandName;
switch(cmd)//Filter CommandName
{
case "next":
CurrentPage++;
break;
case "prev":
CurrentPage--;
break;
case "last":
CurrentPage = Pages;
break;
default:
CurrentPage = 0;
break;
}
ViewState["PageIndex"] = CurrentPage;//Save the calculated CurrentPage variable to ViewState again
TDataBind();//Call the data binding function TDataBind()
}
private void TDataBind()
{
CurrentPage = (int)ViewState["PageIndex"];//Read the page number value from ViewState and save it to the CurrentPage variable for button
invalidation
operation
Pages = (int)ViewState["PageCounts"];//Read the total page parameters from ViewState to perform button invalidation calculations
//Determine the status of the four buttons (homepage, previous page, next page, last page)
if (CurrentPage + 1 > 1)
{
Fistpage.Enabled = true;
Prevpage.Enabled = true;
}
else
{
Fistpage.Enabled = false;
Prevpage.Enabled = false;
}
if (CurrentPage == Pages)
{
Nextpage.Enabled = false;
Lastpage.Enabled = false;
}
else
{
Nextpage.Enabled = true;
Lastpage.Enabled = true;
}
//Data binding to DataList control
DataSet ds = new DataSet();
//Core SQL statements, perform query operations (determines the efficiency of paging:))
SqlDataAdapter MyAdapter = new SqlDataAdapter("Select Top "+PageSize+" * from redheadedfile where id
not in(select top "+PageSize*CurrentPage+" id from redheadedfile order by id asc) order by id asc",MyCon());
MyAdapter.Fill(ds,"news");
datalist1.DataSource = ds.Tables["news"].DefaultView;
datalist1.DataBind();
//Display Label control LCurrentPaget and text box control gotoPage status
LCurrentPage.Text = (CurrentPage+1).ToString();
gotoPage.Text = (CurrentPage+1).ToString();
//Release SqlDataAdapter
MyAdapter.Dispose();
}
#region Code generated by Web Forms Designer
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Forms designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Designer supports required methods - do not use code editor to modify
/// The content of this method.
/// </summary>
private void InitializeComponent()
{
this.Fistpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);
this.Prevpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);
this.Nextpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);
this.Lastpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);
this.gotoPage.TextChanged += new System.EventHandler(this.gotoPage_TextChanged);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
//Jump page code
private void gotoPage_TextChanged(object sender, System.EventArgs e)
{
try
{
JumpPage = (int)ViewState["JumpPages"];//Read the available page value from ViewState and save it to the JumpPage variable
//Determine whether the user input value exceeds the available page range value
if(Int32.Parse(gotoPage.Text) > JumpPage || Int32.Parse(gotoPage.Text) <= 0)
Response.Write("<script>alert('Page range out of bounds!');location.href='WebForm8.aspx'</script>");
else
{
int InputPage = Int32.Parse(gotoPage.Text.ToString()) - 1;//Convert the user input value and save it in the int type
InputPage variable
ViewState["PageIndex"] = InputPage;//Write the InputPage value into ViewState["PageIndex"]
TDataBind();//Call the data binding function TDataBind() to perform the data binding operation again
}
}
//Catch exceptions caused by users entering incorrect data types
catch(Exception exp)
{
Response.Write("<script>alert('"+exp.Message+"');location.href='WebForm8.aspx'</script>");
}
}
}
}
Let’s try it. Is the efficiency much higher?
If there is anything wrong, please correct me.