利用AspNetPage分页控件,结合高效ＳＱＬ存储过程

Sql Server2000 存储过程 

--用户管理分页 
--参数@NewsTypeID 新闻类型
--参数@NewsStatus 新闻状态
--参数@KeyWord 关键字
--参数@Promulgator 发布者
--参数@InTime 发布时间
-- @docount=1 返回记录统计
Alter procedure PR_GetNews
(
    @KeyWord varchar(100),
    @NewsTypeID varchar(8),
    @NewsStatus int,
    @Promulgator varchar(50),
    @InTime datetime,
    @pagesize int,
    @pageindex int,
    @docount bit
)
as
set nocount on

declare @where nvarchar(400)
set @where='where 1=1'
    if @KeyWord <> '' set @where = @where+' and (Content like ''%'+@KeyWord+'%'' or Title like ''%'+@KeyWord+'%'')'
    if @NewsTypeID <>'' set @where=@where+' and NewsTypeID like ''%'+@NewsTypeID+'%'''    --使用模糊查询因为一条新闻可能是多个类型
    if @NewsStatus >= 0 set @where=@where+' and NewsStatus= '+str(@NewsStatus,1) + ''
    if @Promulgator <> '' set @where = @where+' and Promulgator like ''%' + @Promulgator + '%'''
    if @InTime is not null set @where = @where + ' and (NewsCheckInTime between '''+convert(varchar(10),@InTime,120)+''' and '''+convert(varchar(10),GetDate()+1,120)+''')'

declare @RecordCount int
declare @TempSql nvarchar(1000)

print @where
set @TempSql='select @RecordCount=count(NewsID) from TB_News '+ @where
exec sp_executesql @TempSql,N'@RecordCount int output',@RecordCount output

if(@docount=1)
select @RecordCount
else
begin
if(@pageindex=1)
exec('select top '+@pagesize+' * from TB_News '+@where+' order by NewsID desc')
else
begin
declare @PageUpperBound int
declare @endrecords int
set @PageUpperBound=@pageindex*@pagesize
if(@PageUpperBound-@pagesize)>=@RecordCount
select ''
else if(@RecordCount-(@PageUpperBound-@pagesize)<=@pagesize)
begin
set @endrecords=@RecordCount-(@PageUpperBound-@pagesize)

exec('select  *  from (select top '+@endrecords+' * from TB_News '+@where+' order by NewsID )A order by NewsID desc')
end
else
exec('select * from (select top '+@pagesize+'* from (select top '+@PageUpperBound+' * from TB_News '+@where+' order by NewsID desc)A order by NewsID )B order by NewsID desc')
end
end
set nocount off
--select @where

GO

数据访问层

管理分页代码#region 管理分页代码
        /**//// <summary>
        /// 分页页数管理
        /// </summary>
        /// <param name="NewsTypeID">新闻类型</param>
        /// <param name="NewsStatus">新闻状态</param>
        /// <returns>int</returns>
        public static int PR_GetNews_ByPageCount(string KeyWord,string NewsTypeID,int NewsStatus,string Promulgator,string InTime)
        ...{
            int docount=1; //1表示查询总行数
            int pagesize=0;
            int pageindex=0;
            SqlConnection conn =null;
            conn = new SqlConnection(DBConnectString);
            conn.Open();
            
            int Result=0;

            SqlParameter[] parmArr = new SqlParameter[8];             
            parmArr.SetValue(new SqlParameter("@KeyWord",KeyWord),0);
            parmArr.SetValue(new SqlParameter("@NewsTypeID",NewsTypeID),1);
            parmArr.SetValue(new SqlParameter("@NewsStatus",NewsStatus),2);
            parmArr.SetValue(new SqlParameter("@Promulgator",Promulgator),3);
            parmArr.SetValue(new SqlParameter("@InTime",InTime),4);
            parmArr.SetValue(new SqlParameter("@pagesize",pagesize), 5);             
            parmArr.SetValue(new SqlParameter("@pageindex",pageindex),6);
            parmArr.SetValue(new SqlParameter("@docount",docount),7);
            Result =(int) SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "PR_GetNews", parmArr);
            if(conn !=null)
            ...{
                conn.Close();
                conn.Dispose();
            }

            return Result;
        }


        /**//// <summary>
        /// 管理分页
        /// </summary>
        /// <param name="NewsTypeID">新闻类型</param>
        /// <param name="NewsStatus">新闻状态</param>
        /// <param name="pagesize">每页显示的记录数</param>
        /// <param name="pageindex">当前页</param>
        /// <returns>DataSet</returns>
        public static DataSet PR_GetNews_ByPage(string KeyWord,string NewsTypeID,int NewsStatus,string Promulgator,string InTime,int pagesize ,int pageindex )
        ...{
            int docount=0; //查询记录
            SqlConnection conn =null;
            conn = new SqlConnection(DBConnectString);
            conn.Open();
            
            DataSet ds=null;
            
            SqlParameter[] parmArr = new SqlParameter[8];             
            parmArr.SetValue(new SqlParameter("@KeyWord",KeyWord),0);
            parmArr.SetValue(new SqlParameter("@NewsTypeID",NewsTypeID),1);
            parmArr.SetValue(new SqlParameter("@NewsStatus",NewsStatus),2);
            parmArr.SetValue(new SqlParameter("@Promulgator",Promulgator),3);
            parmArr.SetValue(new SqlParameter("@InTime",InTime),4);
            parmArr.SetValue(new SqlParameter("@pagesize",pagesize), 5);             
            parmArr.SetValue(new SqlParameter("@pageindex",pageindex),6);
            parmArr.SetValue(new SqlParameter("@docount",docount),7);
            ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "PR_GetNews", parmArr);
            
            if(conn !=null)
            ...{
                conn.Close();
                conn.Dispose();
            }
            
            return ds;
        }

        #endregion

逻辑层就一个返回就不写了。下面 WEB 层

//绑定 DataGrid 及 AspNetPage控件
private void GridDataBind(string KeyWord,string NewsTypeID,int NewsStatus,string Promulgator,string InTime)
        ...{                    
            DataSet ds=null;
            //获取总记录总数
            MyPage.RecordCount = Pad.BS.Facade.System.FNews.PR_GetNews_ByPageCount(KeyWord,NewsTypeID,NewsStatus,Promulgator,InTime);
            
            ds = Pad.BS.Facade.System.FNews.PR_GetNews_ByPage(KeyWord,NewsTypeID,NewsStatus,Promulgator,InTime,MyPage.PageSize,MyPage.CurrentPageIndex);
            this.gridNews.DataSource = ds;
            this.gridNews.DataBind();
            MyPage.CustomInfoText="记录总数：<font color="red"><b>"+MyPage.RecordCount.ToString()+"</b></font>";
            MyPage.CustomInfoText+=" 总页数：<font color="red"><b>"+MyPage.PageCount.ToString()+"</b></font>";
            MyPage.CustomInfoText+=" 当前页：<font color="red"><b>"+MyPage.CurrentPageIndex.ToString()+"</b></font>";                            
            
        }

//AspNetPage的 PageChanged 事件
private void MyPage_PageChanged(object src, Pad.WControl.PageChangedEventArgs e)
        ...{
            MyPage.CurrentPageIndex = e.NewPageIndex;
            
                GridDataBind(this.txtKeyWord.Text.ToString(),this.listNewsType.SelectedValue.ToString(),
                    Convert.ToInt32(this.ListStatus.SelectedValue.ToString()),this.txtAuthor.Text.ToString(),
                    this.txtDate.Text.ToString());
        }