利用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());
        }