超大数据查询分页、

时间：2010-09-10 来源：北冥有魚,其名為坤、

大数据量的表用一般的分页会导致卡死，今天特意写了一个可供超大数据查询的分页。
页面代码如下：

代码 <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
          </asp:ScriptManager>
          <asp:UpdatePanel ID="UpdatePanel1" runat="server">
          <ContentTemplate>

        <asp:GridView ID="gvSysLog" runat="server" AutoGenerateColumns="False"  Width="100%"
         CssClass="GridViewStyle">
            <AlternatingRowStyle CssClass="AltRowStyle" />
                <RowStyle CssClass="RowStyle" />
            <Columns>
                <asp:BoundField DataField="SystemID" HeaderText="系统ID"  Visible="false"/>
                <asp:BoundField DataField="SystemName" HeaderText="系统名称" />
                <asp:BoundField DataField="SystemIP" HeaderText="系统IP" />
                <asp:BoundField DataField="UserID" HeaderText="用户ID" />
                <asp:BoundField DataField="UserName" HeaderText="用户名" />
                <asp:BoundField DataField="UserIP" HeaderText="用户IP" />
                <asp:BoundField DataField="Category" HeaderText="分类" />
                <asp:BoundField DataField="Message" HeaderText="具体描述" />
                <asp:BoundField DataField="CreateTime" HeaderText="创建时间" />
            </Columns>
            </asp:GridView>
            <div style="PADDING-TOP:10px; text-align:center"  >
                  共<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>--%>
               <asp:Button ID="msgbox" runat="server"  Text="确定" CssClass="btn_2k3"
                    onclick="msgbox_Click"/>
               </div>
            </ContentTemplate>
            </asp:UpdatePanel>
    </form>

程序代码：

代码 using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Text;

public partial class SYS_Manage_SysLog : System.Web.UI.Page
{
    string strpage = "";
    int PageCount = 0;
    int RecCount = 0;
    int CurrentPage = 0;
    int Pages = 0;
    int JumpPage = 0;//跳到第几页
    //每页显示记录数
    int PageSize =20; //每页显示20条记录
    private static int PageIndex = 1;
    private static int PageCounts = 1;
    private static int JumpPages = 1;

    protected void Page_Load(object sender, EventArgs e)
    {
        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(Logid)  from DEG_SysLogs where 1=1 ");

            string bss = ass.ToString();
            int RecordCount = 0;
            ds = DEG.BLL.DbHelper.Query(bss);

            RecordCount = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
            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();
    }
    #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;

        //用于函数参数的个数不对在查询表达式 'isnull(max(xx.Logid),0)' 中。
        // SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确。
        //分页的核心语句，决定性能   row_number()也不错。也可以将以下语句改为存储过程。
        StringBuilder sql = new StringBuilder("select  top  " + PageSize + "  * from DEG_SysLogs a ");
        sql.Append(" where   a.[Logid]>( select max(xx.[Logid])  from ( select  TOP " + PageSize2 + "  [Logid]  from DEG_SysLogs  where 1=1 ");
        sql.Append("   ORDER BY [Logid] )xx   ) ");
        sql.Append("   ORDER BY a.[Logid] ");
        gvSysLog.DataSource = DEG.BLL.DbHelper.Query(sql.ToString());
        gvSysLog.DataBind();
    }

    //转到第几页
    protected void msgbox_Click(object sender, 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();//绑定数据集
        }
    }
}

效果图：