asp.net(C#)海量数据表高效率分页算法（易懂，不使用存储过程）

首先创建一张表(要求ID自动编号)：

create table redheadedfile(

id int identity(1,1),

filenames nvarchar(20),

senduser nvarchar(20),

primary key(id)

)

然后我们写入50万条记录：

declare @i int

set @i=1

while @i<=500000

begin

insert into redheadedfile(filenames,senduser) values('我的分页算法','陆俊铭')

set @[email protected]+1

end

用Microsoft Visual Studio .NET 2003创建一张WebForm网页(本人起名webform8.aspx)

前台代码片段如下(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>

</HEAD>

<asp:datalist id="datalist1" AlternatingItemStyle-BackColor="#f3f3f3" Width="100%" CellSpacing="0"

CellPadding="0" Runat="server">

<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">共<asp:label id="LPageCount" Runat="server" ForeColor="#ff0000"></asp:label>页/共

<asp:label id="LRecordCount" Runat="server" ForeColor="#ff0000"></asp:label>记录

<asp:linkbutton id="Fistpage" Runat="server"

CommandName="0">首页</asp:linkbutton>    <asp:linkbutton id="Prevpage" Runat="server" CommandName="prev">

上一页</asp:linkbutton>    <asp:linkbutton id="Nextpage" Runat="server"

CommandName="next">下一页</asp:linkbutton>    <asp:linkbutton id="Lastpage" Runat="server"

CommandName="last">尾页</asp:linkbutton>    当前第<asp:label id="LCurrentPage" Runat="server"

ForeColor="#ff0000"></asp:label>页    跳页<asp:TextBox ID="gotoPage" Runat="server" Width="30px"

MaxLength="5" AutoPostBack="True"></asp:TextBox></div>

</form>

</body>

</HTML>

后台代码片段如下(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>

/// 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;//定义每页显示记录

int PageCount,RecCount,CurrentPage,Pages,JumpPage;//定义几个保存分页参数变量

private void Page_Load(object sender, System.EventArgs e)

{

if(!IsPostBack)

{

RecCount = Calc();//通过Calc()函数获取总记录数

PageCount = RecCount/PageSize + OverPage();//计算总页数（加上OverPage()函数防止有余数造成显示

数据不完整）

ViewState["PageCounts"] = RecCount/PageSize -

ModPage();//保存总页参数到ViewState（减去ModPage()函数防止SQL语句执行时溢出查询范围，可以用存储过程分页算法来理解这句）

ViewState["PageIndex"] = 0;//保存一个为0的页面索引值到ViewState

ViewState["JumpPages"] = PageCount;//保存PageCount到ViewState，跳页时判断用户输入数是否超出页

码范围

//显示LPageCount、LRecordCount的状态

LPageCount.Text = PageCount.ToString();

LRecordCount.Text = RecCount.ToString();

//判断跳页文本框失效

if(RecCount <= 20)

gotoPage.Enabled = false;

TDataBind();//调用数据绑定函数TDataBind()进行数据绑定运算

}

//计算余页

public int OverPage()

{

int pages = 0;

if(RecCount%PageSize != 0)

pages = 1;

else

pages = 0;

return pages;

}

//计算余页，防止SQL语句执行时溢出查询范围

public int ModPage()

{

int pages = 0;

if(RecCount%PageSize == 0 && RecCount != 0)

pages = 1;

else

pages = 0;

return pages;

}

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;

}

//数据库连接语句（从Web.Config中获取）

public static SqlConnection MyCon()

{

SqlConnection MyConnection = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);

MyConnection.Open();

return MyConnection;

}

//对四个按钮（首页、上一页、下一页、尾页）返回的CommandName值进行操作

private void Page_OnClick(object sender, CommandEventArgs e)

{

CurrentPage = (int)ViewState["PageIndex"];//从ViewState中读取页码值保存到CurrentPage变量中进行参数运

算

Pages = (int)ViewState["PageCounts"];//从ViewState中读取总页参数运算

string cmd = e.CommandName;

switch(cmd)//筛选CommandName

{

case "next":

CurrentPage++;

break;

case "prev":

CurrentPage--;

break;

case "last":

CurrentPage = Pages;

break;

default:

CurrentPage = 0;

break;

}

ViewState["PageIndex"] = CurrentPage;//将运算后的CurrentPage变量再次保存至ViewState

TDataBind();//调用数据绑定函数TDataBind()

}

private void TDataBind()

{

CurrentPage = (int)ViewState["PageIndex"];//从ViewState中读取页码值保存到CurrentPage变量中进行按钮失

效运算

Pages = (int)ViewState["PageCounts"];//从ViewState中读取总页参数进行按钮失效运算

//判断四个按钮（首页、上一页、下一页、尾页）状态

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;

}

//数据绑定到DataList控件

DataSet ds = new DataSet();

//核心SQL语句，进行查询运算（决定了分页的效率:)）

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();

//显示Label控件LCurrentPaget和文本框控件gotoPage状态

LCurrentPage.Text = (CurrentPage+1).ToString();

gotoPage.Text = (CurrentPage+1).ToString();

//释放SqlDataAdapter

MyAdapter.Dispose();

}

#region Web 窗体设计器生成的代码

override protected void OnInit(EventArgs e)

{

// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。

InitializeComponent();

base.OnInit(e);

}

/// <summary>

/// 设计器支持所需的方法 - 不要使用代码编辑器修改

/// 此方法的内容。

/// </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

//跳页代码

private void gotoPage_TextChanged(object sender, System.EventArgs e)

{

try

{

JumpPage = (int)ViewState["JumpPages"];//从ViewState中读取可用页数值保存到JumpPage变量中

//判断用户输入值是否超过可用页数范围值

if(Int32.Parse(gotoPage.Text) > JumpPage || Int32.Parse(gotoPage.Text) <= 0)

Response.Write("<script>alert('页码范围越界！');location.href='WebForm8.aspx'</script>");

else

{

int InputPage = Int32.Parse(gotoPage.Text.ToString()) - 1;//转换用户输入值保存在int型

InputPage变量中

ViewState["PageIndex"] = InputPage;//写入InputPage值到ViewState["PageIndex"]中

TDataBind();//调用数据绑定函数TDataBind()再次进行数据绑定运算

}

//捕获由用户输入不正确数据类型时造成的异常

catch(Exception exp)

{

Response.Write("<script>alert('"+exp.Message+"');location.href='WebForm8.aspx'</script>");

}

大家来试试，效率是不是高了很多？

如有不妥望大家来指正

asp.net(C#)海量数据表高效率分页算法（易懂，不使用存储过程）

继续阅读

CQ V1.0分词bates(基于双数组tire树)—应该是目前最快的中文分词算法

Command Network(POJ 3164)---定根最小树形图模板题题目描述输入格式输出格式输入样例输出样例分析源程序

C#多线程——前台线程和后台线程

开源低带宽语音编解码器

C#+ArcEngine代码检测ArcEngine Runtime是否安装以及是否授权

241 Different Ways to Add Parentheses（C代码版）

【趋高机器视觉】机器视觉技术原理解析及解决方案

CSMA/CD1． CSMA/CD的概述2． CSMA 的工作原理3． CSMA/CD控制规程及特点4． CSMA/CD协议5． CSMA/CD的优点6．结束语

极大似然法(ML)与最大期望法(EM)

七牛云-C#SDK-上传-前期准备

C++ 第十五周报告1--《冒泡法排序》

笔试面试题目：滑动窗口(二)

数据结构与算法（27）——排序（二）

Dijkstra--简易版（最短路径）

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

hdu7108哈希