aspnetpager用法

 

         // 存储过程的分页
         // Repeater1.DataSource = panasia.DAL.SqlHelper.ExecuteReader(CommandType.StoredProcedure, "aspnetpager",
         // new SqlParameter("@kindid", kindid),
         // new SqlParameter("@pagesize", AspNetPager1.PageSize),
         // new SqlParameter("@pageindex", AspNetPager1.CurrentPageIndex));

         string  sql  =   " select * from news where kind_id= "   +  kindid.ToString()  +   "  order by psn desc,ptime desc,id desc " ;
        DataTable dt  =  n.pager(AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, sql);
        System.Data.DataView dv  =   new  System.Data.DataView(dt);
        Repeater1.DataSource  =  dv;
        Repeater1.DataBind();

         /**/ /// <summary>
        /// 用于分页
        /// </summary>
        /// <param name="startindex">开始记录数</param> 
        /// <param name="pagesize">每页条数</param> 
        /// <param name="sql">sql</param> 
        /// <returns>返回数据表</returns>
         public  DataTable pager( int  pageindex,  int  pagesize,  string  sql)
         ... {
            DataTable dt = new DataTable();
            SqlConnection Conn = new SqlConnection(SqlHelper.ConnectionString);
            Conn.Open();
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter(sql, Conn);
                DataSet ds = new DataSet();
                int startindex = (pageindex - 1) * pagesize;
                adapter.Fill( ds, startindex, pagesize, "a");
                dt=ds.Tables["a"];
            }
            finally
            ...{
                if (Conn != null && Conn.State == ConnectionState.Open)
                    Conn.Close();
            }
            return dt;
        }

 

sql2000下

CREATE   procedure  aspnetpager 
( @kindid   nvarchar ( 255 ),
@pagesize   int ,
@pageindex   int )
as
set  nocount  on
begin
declare   @indextable   table (id  int   identity ( 1 , 1 ),nid  int )
declare   @PageLowerBound   int
declare   @PageUpperBound   int
set   @PageLowerBound = ( @pageindex - 1 ) * @pagesize
set   @PageUpperBound = @PageLowerBound + @pagesize
set   rowcount   @PageUpperBound
insert   into   @indextable (nid)  select  id  from  news  where  kind_id = @kindid   order   by  psn  desc ,ptime  desc ,id  desc
select   *   from  news O, @indextable  t  where  O.id = t.nid
and  t.id  between   @PageLowerBound + 1   and   @PageUpperBound   order   by  t.id
end
set  nocount  off
GO

 sql2005

 

create   procedure  aspnetpager 
( @pagesize   int ,
@pageindex   int )
as

begin
  with  temptbl  as  (
SELECT  ROW_NUMBER()  OVER  ( ORDER   BY  psn  desc ,ptime  desc ,id  desc ) AS  Row,  *   from  news O   where  kind_id = 1 )
  SELECT   *   FROM  temptbl  where  Row  between  ( @pageindex - 1 ) * @pagesize + 1   and  ( @pageindex - 1 ) * @pagesize + @pagesize
end