ASP.NET 大量数据查询分页例子

分页这个也是最多人问的，也是很基础很实用的。

网上有很多分页代码,要不是有前台就没后台,要不是有后台没前台,要不是就是控件,要不就是一大堆SQL代码，让人不知道怎样用。

力求最简单最易懂.三层架构那些就不搞了。自己下载一个

如果是access 就用这个SQLHELPER [URL=http://hi.csdn.net/link.php?url=http://blog.csdn.net/zzxap]http://hi.csdn.net/link.php?url=http://blog.csdn.net/zzxap[/URL]

如果是MSSQL 就用这个sqlhelper [URL=http://www.cnblogs.com/zzxap/archive/2009/11/05/1596773.html]http://www.cnblogs.com/zzxap/archive/2009/11/05/1596773.html[/URL]

承接外包,网站开发 zzxap@hotmail.com

[code=HTML]

前台如果你用的是 gridview 就把Repeater替换成gridview 。一样的。。

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<asp:Repeater runat="server" Id="mainlist" >

<li class="pic-a" > Eval("product_type").ToString() </li>

<li class="pic-b" >Eval("product_desc").ToString() </li>

</div>

</ItemTemplate>

</asp:Repeater>

</div>

  共<asp:label id="LPageCount" ForeColor="#ff0000" Runat="server"></asp:label>頁

<asp:label id="LTotalCount" ForeColor="#ff0000" Runat="server"></asp:label>條記錄

<asp:linkbutton id="Fistpage" Runat="server" CommandName="0" OnClick="Pager_Click">首頁</asp:linkbutton>

<asp:linkbutton id="Prevpage" Runat="server" CommandName="prev" OnClick="Pager_Click">上一頁</asp:linkbutton>

<asp:linkbutton id="Nextpage" Runat="server" CommandName="next" OnClick="Pager_Click">下一頁</asp:linkbutton>

<asp:linkbutton id="Lastpage" Runat="server" CommandName="last" OnClick="Pager_Click">尾頁</asp:linkbutton>當前第

<asp:label id="LCurrentPage" ForeColor="#ff0000" Runat="server"></asp:label>頁

  轉到第

<asp:textbox id="gotoPage" Width="30px" Runat="server" AutoPostBack="True" MaxLength="5" ></asp:textbox>頁

<asp:Label style=" POSITION: absolute" id="msgbox" runat="server" ForeColor="Red" BorderColor="Red"></asp:Label>

</div>

</ContentTemplate>

</asp:UpdatePanel>

</form>

[/code]

[code=C#]

using System;

using System.Data;

using System.Configuration;

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.OleDb;

using System.Text;

using Microsoft.ApplicationBlocks.Data;

public partial class _Default : System.Web.UI.Page

{

string strpage = "";

int PageCount = 0;

int RecCount = 0;

int CurrentPage = 0;

int Current = 0;

int Pages = 0;

int JumpPage = 0;//跳到第几页

//每页显示记录数

int PageSize =20; //每页显示20条记录

string product_type = "";

string type = "";

string headid = "";

private static int PageIndex=1;

private static int PageCounts=1;

private static int JumpPages=1;

protected void Page_Load(object sender, EventArgs e)

{

#region 接收从查询页面传递过来的参数。

if (Request.QueryString["product_type"]!=null)

{

product_type = Server.UrlDecode(Request.QueryString["product_type"].ToString());

}

if (Request.QueryString["type"]!=null)

{

type = Server.UrlDecode(Request.QueryString["type"].ToString());

}

#endregion

// headid = Request.QueryString["headid"];

if (!IsPostBack)

{

//-------------------------------------------------------------

RecCount = Calc();

//计算总记录数

PageCount = RecCount / PageSize + OverPage();

//计算总页数

PageCounts = RecCount / PageSize - ModPage();

if (!string.IsNullOrEmpty(strpage))

{

//设置当前页为返回页

PageIndex= int.Parse(strpage);

}

else

{

PageIndex= 1;

//设置当前页为11

//Session["CurPage"] = 1;

}

JumpPages = PageCount;

LPageCount.Text = PageCount.ToString();

//总页数

LTotalCount.Text = RecCount.ToString();

//总记录数

if (RecCount <= PageSize)

{

gotoPage.Enabled = false;

}

else

{

gotoPage.Enabled = true;

}

//-------------------------------------------------------------

GetMainData(); //绑定数据

}

#region "計算總行數"

public int OverPage()

{

//算余

int pages = 0;

if (RecCount % PageSize != 0) {

pages = 1;

}

else {

pages = 0;

}

return pages;

}

public int ModPage()

{

//算余

int pages = 0;

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

{

pages = 1;

}

else {

pages = 0;

}

return pages;

}

public int Calc()

{

//計算記錄總數

DataSet ds= new DataSet();

StringBuilder ass = new StringBuilder("Select count(id) from M_product where 1=1 ");

//如果有查询参数传入，就计算查询结果的总数

if (type != null && type != "")

{

ass.Append(" and [type]=" + type.Replace("'", "") + " ");

}

if (product_type != null && product_type != "")

{

ass.Append(" and product_type=" + product_type.Replace("'", "") + " ");

}

string bss = ass.ToString();

int RecordCount = 0;

ds = SqlHelper.ExecuteDataset(SqlHelper.Conn, CommandType.Text, bss);

RecordCount = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());

//RecordCount = 10;

if (RecordCount < PageSize)

{

Fistpage.Enabled = false;

Prevpage.Enabled = false;

Nextpage.Enabled = false;

Lastpage.Enabled = false;

}

else

{

Fistpage.Enabled = true;

Prevpage.Enabled = true;

Nextpage.Enabled = true;

Lastpage.Enabled = true;

}

return RecordCount;

}

#endregion

#region "翻頁"

public void Pager_Click(object sender, EventArgs e)

{

CurrentPage = (int)PageIndex;

Pages = (int)PageCounts;

string arg = ((LinkButton)sender).CommandName.ToString();

switch (arg) {

case "next":

//下一页如果要支持URL分页。只要把CurrentPage 从URL获取就可以了

if (CurrentPage < Pages + 1)

{ CurrentPage = CurrentPage + 1; }

break;

case "prev":

//上一页

if (CurrentPage != 1)

{ CurrentPage -= 1; }

break;

case "last":

//最后一页

CurrentPage = Pages + 1;

break;

default:

//首页

CurrentPage = 1;

break;

}

//根据页数控制翻页按钮的可用与否

if (CurrentPage > 1) {

Fistpage.Enabled = true;

Prevpage.Enabled = true;

}

else {

Fistpage.Enabled = false;

Prevpage.Enabled = false;

}

if (CurrentPage == Pages + 1)

{

Nextpage.Enabled = false;

Lastpage.Enabled = false;

}

else {

Nextpage.Enabled = true;

Lastpage.Enabled = true;

}

PageIndex= CurrentPage;

//获取改變后的页码

// //Session["CurPage"] = CurrentPage;

//用户返回到当前页

GetMainData();

}

//转到第几页

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

{

string asd = this.gotoPage.Text.Trim().ToString();

JumpPage = (int)JumpPages;

if (string.IsNullOrEmpty(asd)) {

Alert("out of page range");

return;

}

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

{

Alert("out of page range");//页数超出范围

return;

}

else {

int InputPage = Int32.Parse(gotoPage.Text.ToString());

PageIndex= InputPage;

////Session["CurPage"] = InputPage;

GetMainData();//绑定数据集

}

#endregion

public void Alert(string rtt)

{

//顯示提示信息

// System.Web.UI.ScriptManager.RegisterClientScriptBlock(UpdatePanel1, this.GetType(), "AjaxMsgBox", "alert('" + rtt + "');", true);

}

private void GetMainData()

{

CurrentPage = (int)PageIndex;//获取当前页

Pages = (int)PageCounts;//获取总页数

LCurrentPage.Text = CurrentPage.ToString();

int PageSize2 = PageSize * (CurrentPage - 1)+1;

if (type != null && product_type!=null)//如果是手机

{

//用于函数参数的个数不对在查询表达式 'isnull(max(xx.id),0)' 中。

// SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确。

//分页的核心语句，决定性能 row_number()也不错。也可以将以下语句改为存储过程。

StringBuilder sql = new StringBuilder("select top " + PageSize + " * from M_product a ");

sql.Append(" where a.[id]>( select max(xx.[id]) from ( select TOP " + PageSize2 + " [id] from M_product where 1=1 ");

if (type != null && type != "")

{

sql.Append(" and [type]=" + type.Replace("'", "")+"");

}

if (product_type != null && product_type != "")

{

sql.Append(" and product_type=" + product_type.Replace("'", "") + "");

}

sql.Append(" ORDER BY [id] )xx ) ");

if (type != null && type != "")

{

sql.Append(" and a.[type]=" + type.Replace("'", "") + "");

}

if (product_type != null && product_type != "")

{

sql.Append(" and a.product_type=" + product_type.Replace("'", "") + "");

}

sql.Append(" ORDER BY a.[id] ");

mainlist.DataSource = SqlHelper.ExecuteDataset( SqlHelper.Conn, CommandType.Text,sql.ToString());

mainlist.DataBind();

}

[/CODE]