sql百万级数据通用分页类asp.net版-白红宇的个人博客

sql百万级数据通用分页类asp.net版

发布日期：2022-02-05 18:27:41 浏览次数：13 分类：技术文章

本文共 7066 字，大约阅读时间需要 23 分钟。

using System;

using System.Data;

using System.Data.SqlClient;

namespace Pagination.Class

{

/// <summary>

/// Pager 的摘要说明。

/// </summary>

public class Pager

{

public Pager()

{

// TODO: 在此处添加构造函数逻辑

}

/// <param name="fieldlist"></param>字段列表，如id,title,content

/// <param name="condition"></param>条件，如id<100

/// <param name="pkey"></param>主键，自增

/// <param name="tablename"></param>表的名字，如News

/// <param name="sort"></param>排序，0表示降序，1表示升序

/// <param name="pagesize"></param>每页大小

/// <param name="cpage"></param>当前页码

public DataSet GetCurrentDataSet(string fieldlist,string condition,string pkey,string tablename,int sort,int pagesize,int cpage)//得到当前页记录

{

SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["cs"]);

SqlDataAdapter cmd=new SqlDataAdapter(GetSql(fieldlist,condition,pkey,tablename,sort,pagesize,cpage),conn);

DataSet ds=new DataSet();

cmd.Fill(ds);

return ds;

}

static string GetSql(string fieldlist,string condition,string pkey,string tablename,int sort,int pagesize,int cpage)

{

string sql="";

if(sort==0)

{

if(condition!="")

{

sql="select top "+pagesize.ToString()+" "+fieldlist+" from "+tablename+" where "+condition+" and "+pkey+" not in(select top "+pagesize*(cpage-1)+" "+pkey+" from "+tablename+" where "+condition+" and order by "+pkey+" desc) order by "+pkey+" desc";

}

else

{

sql="select top "+pagesize.ToString()+" "+fieldlist+" from "+tablename+" where "+pkey+" not in(select top "+pagesize*(cpage-1)+" "+pkey+" from "+tablename+" order by "+pkey+" desc) order by "+pkey+" desc";

}

else

{

if(condition!="")

{

}

else

{

}

return sql;

}

public int GetCounts(string pkey,string tablename,string condition)//得到总记录数

{

string sql;

if(condition!="")

{

sql="select count("+pkey+") from "+tablename where "+condition;

}

else

{

sql="select count("+pkey+") from "+tablename;

}

SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["cs"]);

SqlCommand cmd=new SqlCommand(sql,conn);

conn.Open();

SqlDataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);

dr.Read();

string str=dr[0].ToString();

dr.Close();

return Convert.ToInt32(str);

}

这里可以输出总记录数和当前页面的dataset。

针对此通用分页类的调用实例

1 建表News

CREATE TABLE [News] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

[content] [text] COLLATE Chinese_PRC_CI_AS NULL ,

CONSTRAINT [PK_News] PRIMARY KEY CLUSTERED

(

[id]

) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

2 建立生成数据的存储过程

create proc addnews

@count int

declare @i int

set @i=0

set nocount on

while @i<@count

begin

set @i=@i+1

insert into news (title,content) values ('百万级数据分页测试','Test by 逐日★风')

end

set nocount off

3 生成数据

addnews 2000000

4 建立Aspx文件

<asp:Repeater id="Repeater1" runat="server">

</HeaderTemplate>

<tr>

</tr>

</ItemTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

<asp:Label id="lblcount" runat="server"></asp:Label><FONT face="宋体">条记录，当前

<asp:Label id="lblcurrent" runat="server"></asp:Label>页  

<asp:LinkButton id="hlprev" runat="server">上页</asp:LinkButton> 

<asp:LinkButton id="hlnext" runat="server">下页</asp:LinkButton>  

<asp:DropDownList id="DropDownList1" runat="server" AutoPostBack="True"></asp:DropDownList></FONT></div>

5 Cs文件

protected System.Web.UI.WebControls.Label lblcount;

protected System.Web.UI.WebControls.Label lblcurrent;

protected System.Web.UI.WebControls.DropDownList DropDownList1;

protected System.Web.UI.WebControls.LinkButton hlprev;

protected System.Web.UI.WebControls.LinkButton hlnext;

protected System.Web.UI.WebControls.Repeater Repeater1;

private int pagesize=20;

static int cpage;

static int TotalPages;

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

{

// 在此处放置用户代码以初始化页面

if(!this.IsPostBack)

{

cpage=1;

Class.Pager pg=new Class.Pager();

int Counts=pg.GetCounts("id","News","");

lblcount.Text=Counts.ToString();//得到总记录数

TotalPages=Counts/pagesize;

if(Counts<pagesize)

TotalPages=1;

else

{

if(Counts%pagesize!=0)TotalPages++;

}//计算总页数

lblcurrent.Text=cpage.ToString()+"/"+TotalPages;

DataSet ds=new DataSet();

ds=pg.GetCurrentDataSet("id,title,content","","id","News",1,10,cpage);

bind(ds);

Gethl();

Getddlist();

}

private void Getddlist()

{

if(TotalPages!=1)

{

for(int i=1;i<=TotalPages;i++)

{

DropDownList1.Items.Add(new ListItem(i.ToString(),i.ToString()));

}

else

{

DropDownList1.Items.Add(new ListItem("1","1"));

}

private void Gethl()//得到上页，下页状态

{

if(lblcurrent.Text=="1")

{

hlprev.Enabled=false;

hlnext.Enabled=false;

}

else

{

if(cpage==1)

hlprev.Enabled=false;

else

hlprev.Enabled=true;

if(cpage==Convert.ToInt32(TotalPages))

hlnext.Enabled=false;

else

hlnext.Enabled=true;

}

lblcurrent.Text=cpage.ToString()+"/"+TotalPages;

}

private void bind(DataSet ds)

{

Repeater1.DataSource=ds;

Repeater1.DataBind();

}

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

override protected void OnInit(EventArgs e)

{

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

InitializeComponent();

base.OnInit(e);

}

/// <summary>

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

/// 修改此方法的内容。

/// </summary>

private void InitializeComponent()

{

this.hlprev.Click += new System.EventHandler(this.hlprev_Click);

this.hlnext.Click += new System.EventHandler(this.hlnext_Click);

this.DropDownList1.SelectedIndexChanged += new System.EventHandler(this.DropDownList1_SelectedIndexChanged);

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

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

{

cpage=cpage-1;

Class.Pager pg=new Class.Pager();

DataSet ds=new DataSet();

ds=pg.GetCurrentDataSet("id,title,content","","id","News",cpage,10,cpage);

bind(ds);

Gethl();

DropDownList1.ClearSelection();

DropDownList1.Items.FindByValue(cpage.ToString()).Selected=true;

}

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

{

cpage=cpage+1;

Class.Pager pg=new Class.Pager();

DataSet ds=new DataSet();

ds=pg.GetCurrentDataSet("id,title,content","","id","News",cpage,10,cpage);

bind(ds);

Gethl();

DropDownList1.ClearSelection();

DropDownList1.Items.FindByValue(cpage.ToString()).Selected=true;

}

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

{

cpage=Convert.ToInt32(DropDownList1.SelectedValue);

Class.Pager pg=new Class.Pager();

DataSet ds=new DataSet();

ds=pg.GetCurrentDataSet("id,title,content","","id","News",cpage,10,cpage);

bind(ds);

Gethl();

}

转载地址：https://blog.csdn.net/lisong58420/article/details/2304501 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：xmlhttp对象的简单封装

下一篇：sql 经典语句

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章