高性能SQL数据分页(原创)

SQL数据分页的存储过程：

ALTER PROCEDURE p_qb_GetMessageRecordToDataGrid 
@TotalRecs int output,
@pagesize int,'页大小
@pagenum int,'当前页
@typeid int'where子句的条件变量
AS
DECLARE @sqlTable AS nvarchar(400)
select @TotalRecs = Count(*) from qb_info where i_typeid=@typeid
if(@pagenum=1)
set @sqlTable='select top '+str(@pagesize)+' * from qb_info where i_typeid='+str(@typeid)
else
set @sqlTable='SELECT TOP '+str(@pagesize)+' * FROM qb_info WHERE (i_id >(SELECT Max(i_id) FROM (SELECT TOP '+str(@pagesize*(@pagenum-1))+' i_id FROM qb_info where i_typeid='+str(@typeid)+') AS T)) and i_typeid='+str(@typeid)          

EXEC sp_executesql @sqlTable

下面是程序代码：

namespace ccxx.CIA.UserControls
{
    using System;
    using System.Data;
    using System.Drawing;
    using System.Web;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;
    using ccxx.data;
    using System.ComponentModel;
    using CCXX.QBCJFX;

    /// <summary>
    ///        CommanMessageList 的摘要说明。
    /// </summary>
    public class CommanMessageList : System.Web.UI.UserControl
    {
        protected ccxx.WebControls.JavaScriptControl js;
        protected ccxx.Common.Style.StyleLink sl;
        protected ccxx.data.InfoManageDb imd;
        protected System.Web.UI.HtmlControls.HtmlInputHidden hdnInfoType;
        
        private ManagInfo ManagInfoObj = new ManagInfo(); 
        private ManagerComment ManagerC = new ManagerComment();
        private ManagInfoType InfoType = new ManagInfoType();

        protected DataSet ds = new DataSet();
        protected System.Web.UI.WebControls.Button btFristPage;
        protected System.Web.UI.WebControls.Button btBackPage;
        protected System.Web.UI.WebControls.Button btNextPage;
        protected System.Web.UI.WebControls.Button btEndPage;

        #region property
        public int _PageSize;
        public int _PageCount;
        public int _PageIndex;
        public int _RecordCount;
        private bool _EditTag=true;
        private bool EditTag
        {
            get
            {
                return _EditTag;
            }
            set
            {
                _EditTag=value;
            }
        }
        #endregion
        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!this.IsPostBack)
            {
                _PageSize=15;
                _PageCount=1;
                Session["pageindex"]=1;
                _RecordCount=0;
            }
            //判断是浏览还是管理
            //还要判断是浏览评论哪些信息

            if( (Session["Op"] != null && Session["Op"].ToString() == "View" ) || Request["Op"] == "View" )　///浏览
            {
                //p_Edit.Visible = false;
                this.EditTag=false;
            }
            else if( (Session["Op"] != null  && Session["Op"].ToString() == "Edit") && Request["Op"] == null) //管理
            {
                //p_Edit.Visible = true;
                this.EditTag=true;
            }
                imd = new InfoManageDb(Page.Session["DbProvider"].ToString());
        }
        protected override void Render(System.Web.UI.HtmlTextWriter writer)
        {
            this.DataList();
            this.DataBind();
            base.Render (writer);
        }

        private void DataList()
        {
            if(this.EditTag)
            {
                Response.Write("<TABLE id=\"Table4\" cellSpacing=\"0\" cellPadding=\"0\" width=\"100%\" border=\"1\" style=\"FONT-SIZE: x-small\">");
                Response.Write("<TR><TD align=\"center\">选择</TD><TD Width=\"50%\">信息标题</TD><TD Width=\"17%\" align=\"center\">加入时间</TD><TD align=\"center\" Width=\"12%\"></TD><TD align=\"center\" Width=\"7%\"></TD></TR>");
                DataPage();
                int i=ds.Tables[0].Rows.Count;
                for(int n=0;n<ds.Tables[0].Rows.Count;n++)
                {
                    Response.Write("<TR><TD align=\"center\"><INPUT type=\"checkbox\"></TD><TD Width=\"50%\"><a href='../MessageView/MessageTxtContent.aspx?i_id=");
                    Response.Write(ds.Tables[0].Rows[n][0]);//i_id
                    Response.Write("' target=_blank>");
                    Response.Write(ds.Tables[0].Rows[n][1]);//qb_info
                    Response.Write("</a></TD><TD Width=\"17%\" align=\"center\">");
                    Response.Write(ds.Tables[0].Rows[n][3]);//dtm_insert
                    Response.Write("</TD><TD Width=\"12%\" align=\"center\"><a href='../MessageManage/MessageAdd.aspx?Op=Edit&i_id=");
                    Response.Write(ds.Tables[0].Rows[n][0]);//i_id
                    Response.Write("' target=_blank>编辑</a></TD><TD Width=\"7%\" align=\"center\"></TD></TR>");
                }
                Response.Write("</table>");
            }
            else
            {
                Response.Write("<TABLE id=\"Table4\" cellSpacing=\"0\" cellPadding=\"0\" width=\"100%\" border=\"1\" style=\"FONT-SIZE: x-small\">");
                Response.Write("<TR><TD Width=\"50%\">信息标题</TD><TD Width=\"17%\" align=\"center\">加入时间</TD></TR>");
                DataPage();
                for(int n=0;n<ds.Tables[0].Rows.Count;n++)
                {
                    Response.Write("<TR><TD Width=\"50%\"><a href='../MessageView/MessageTxtContent.aspx?i_id=");
                    Response.Write(ds.Tables[0].Rows[n][0]);//i_id
                    Response.Write("' target=_blank>");
                    Response.Write(ds.Tables[0].Rows[n][1]);//qb_info
                    Response.Write("</a></TD><TD Width=\"17%\" align=\"center\">");
                    Response.Write(ds.Tables[0].Rows[n][3]);//dtm_insert
                    Response.Write("</TD></TR>");
                }
                Response.Write("</table>");
            }
        }
        private void DataPage()
        {
            if(this.IsPostBack)
            {
                _PageCount=(int)Session["PageCount"];
                _RecordCount=(int)Session["RecordCount"]; 
                _PageSize=(int)Session["PageSize"];
                _PageIndex=(int)Session["pageindex"];
            }
            if( Request["c_location"] != null )
                {
                    if(_PageIndex <= 0 )
                        _PageIndex=1;
                    if(_PageIndex>_PageCount)
                        _PageIndex=_PageCount;
                    ds = MessageDB.GetMessageRecordToDataGrid(Int32.Parse(Request["c_location"]),_PageSize,_PageIndex,ref _RecordCount);
                }
                    /*
                else if( Request["Type"] == "Review" )
                {
                    ds = MessageDB.GetReviewMessageToDataGrid(Session["yhdm"].ToString(),_PageIndex,_PageSize);

                    dgInfoList.VirtualItemCount = (int)ds.Tables[2].Rows[0][0];
                    _PageCount = dgInfoList.VirtualItemCount / _PageSize;
                    if( dgInfoList.VirtualItemCount % _PageSize != 0 )
                        _PageCount++;
                    
                    //当选定行数后控制PageCount与CurrentPageIndex的同步
                    if( dgInfoList.CurrentPageIndex >= _PageCount )
                        dgInfoList.CurrentPageIndex  = _PageCount - 1;

                    dgInfoList.DataSource = ds.Tables[1];
                    dgInfoList.DataBind();
                }
                */
                else
                {
                    if( _PageIndex <= 0 )
                        _PageIndex=1;
                    if(_PageIndex>=_PageCount)
                        _PageIndex=_PageCount;
                    Response.Write(Request["c_location"]);
                    ds = MessageDB.GetMessageRecordToDataGrid(1,_PageSize,_PageIndex,ref _RecordCount);
                }
            ///获取总的记录页数。
            _PageCount=_RecordCount/_PageSize;
            if( _RecordCount % _PageSize != 0 )
                _PageCount++;
            Session["PageCount"]=_PageCount;
            Session["RecordCount"]=_RecordCount;
            Session["PageSize"]=_PageSize;
        }


        #region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {    
            this.btFristPage.Click += new System.EventHandler(this.btFristPage_Click);
            this.btBackPage.Click += new System.EventHandler(this.btBackPage_Click);
            this.btNextPage.Click += new System.EventHandler(this.btNextPage_Click);
            this.btEndPage.Click += new System.EventHandler(this.btEndPage_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion
        #region Event
        private void btFristPage_Click(object sender, System.EventArgs e)
        {
            //_PageIndex=1;
            Session["PageIndex"]=1;
        }

        private void btEndPage_Click(object sender, System.EventArgs e)
        {
            //_PageIndex=_PageCount;
            Session["PageIndex"]=(int)Session["PageCount"];
        }

        private void btBackPage_Click(object sender, System.EventArgs e)
        {
            //_PageIndex--;
            Session["PageIndex"]=(int)Session["PageIndex"]-1;
        }

        private void btNextPage_Click(object sender, System.EventArgs e)
        {
            //_PageIndex++;
            Session["PageIndex"]=(int)Session["PageIndex"]+1;
        }
        #endregion
    }
}

其中DataList()函数是直接向页面写数据，DataPage()函数为分页数据的操作函数。在这个程序中用Session保存分页变量。

我是一菜鸟，也就能写个这，大家多提意见。