分页效能

分页功能
分页存储过程：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--============================================== 
-- Author:      白玉水堂 
-- Create date: 2013-10-22 16：15 
-- Description: 分页，用到了ROW_NUMBER() 
-- ============================================= 
ALTER PROCEDURE [dbo].[proc_FengYe] 
@tblName   varchar(255),       -- 表名 
@strFields varchar(1000) = '*', -- 需要返回的列，默认* 
@strOrder varchar(255)='',      -- 排序的字段名，必填 
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC 
@PageSize   int = 10,          -- 页尺寸,默认10 
@PageIndex int = 1,           -- 页码,默认1 
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) 
AS 
 
declare @strSQL   varchar(5000) 
 
if @strWhere !='' 
set @strWhere=' where '+@strWhere 
 
set @strSQL= 
'SELECT * FROM ('+ 
'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strFields+' '+ 
'FROM '+@tblName+' '+@strWhere+ 
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize) 
 
exec (@strSQL) 



DAL层
#region 分页选择新闻
        /// <summary>
        /// 分页选择新闻
        /// </summary>
        /// <param name="pagesize">页大小</param>
        /// <param name="pageindex">页索引</param>
        /// <param name="cond">条件，不用加where</param>
        /// <returns></returns>
        public DataTable
            Select(int pagesize,int pageindex,string cond)
        {
            DataTable dt = new DataTable();
            string cmdText = "proc_FengYe";
            SqlParameter[] paras = new SqlParameter[]{
               new SqlParameter("@tblName","news" as object),                
               new  SqlParameter("@strFields","id,title,createTime,traffic " as object),              
               new SqlParameter("@strOrder","id" as object),
               new SqlParameter("@strOrderType","desc" as object),
               new SqlParameter("@PageSize",pagesize as object),
               new SqlParameter("@PageIndex",pageindex as object),
               new SqlParameter("@strWhere",cond as object)
             };
            dt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.StoredProcedure);
            return dt;
        }
        #endregion

        #region 根据条件计算新闻记录数
        /// <summary>
        /// 根据条件计算新闻记录数
        /// </summary>
        /// <param name="cond">条件，不用加where</param>
        /// <returns></returns>
        public int SelectAllNewsCount(string cond)
        {
            string cmdText = "select count(*) from news ";
            if (!string.IsNullOrEmpty(cond))
            {
                cmdText =string.Format(cmdText+ " where " + cond);
            }
            return int.Parse(sqlhelper.ExecuteScalar(cmdText));
        }
        #endregion


Web层：
Load事件
//计算所属板块为新闻的总记录数     
            string cond = "caId=1";
            newslistAspNetPager.RecordCount = nm.SelectAllNewsCount(cond);
            BindNews();

//绑定所属板块为新闻的列表
    private void BindNews()
    {
        int pagesize = newslistAspNetPager.PageSize;
        int pageindex = newslistAspNetPager.CurrentPageIndex;
        string cond = "caId=1";
        repnewslist.DataSource = nm.Select(pagesize, pageindex, cond);
        repnewslist.DataBind();
    }

//分页事件
    protected void newslistAspNetPager_PageChanged(object sender, EventArgs e)
    {
        BindNews();       
    }

web前端：
<asp:Repeater ID="repnewslist" runat="server">
        <ItemTemplate>
        <img src="images/dian.jpg" alt="分页效能"/> <a href='News.aspx?newsid=<%# Eval("id") %>' title='<%# Eval("title") %>' target="_blank"><%# StringTruncat(Eval("title").ToString(), 30, "...")%></a>    &nbsp;<span id="futi">发布时间(<%# Eval("createTime") %>)  浏览(<%#Eval("traffic")%>)  </span><br/>
        </ItemTemplate>
        </asp:Repeater>
        <p>&nbsp;</p>    
        <webdiyer:AspNetPager ID="newslistAspNetPager" runat="server"
                onpagechanged="newslistAspNetPager_PageChanged" PageSize="13"
                CssCurrentPageButton
                CustomInfoHTML="共%PageCount%页，每页%PageSize%条" FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页"
                ShowCustomInfoSection="Left" SubmitButtonText="跳转" CustomInfoSectionWidth="20%" AlwaysShow="True">
        </webdiyer:AspNetPager>