top分页和aspnextpage控件

----------------aspnextpage的用法 ------------------------------------------------

    DataSet ds;
    SqlDataAdapter dr;
    SqlCommand com;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
  	    Bind(1,20)
        }
    }

    public void Bind(int NowPage, int PageSize)
    {
            SqlParameter[] parameters = {
     		new SqlParameter("@tbname", SqlDbType.NVarChar,50),
    	 	new SqlParameter("@FieldKey", SqlDbType.NVarChar,1000),
     		new SqlParameter("@NowPage", SqlDbType.Int,4),
     		new SqlParameter("@PageSize", SqlDbType.Int,4),
     		new SqlParameter("@FieldShow", SqlDbType.NVarChar,1000),
     		new SqlParameter("@FieldOrder", SqlDbType.NVarChar,1000),
     		new SqlParameter("@Where", SqlDbType.NVarChar,1000),
                  new SqlParameter("@Totalcount", SqlDbType.Int,4)};
            parameters[0].Value = "Users";
            parameters[1].Value = "ID";
            parameters[2].Value = NowPage;
            parameters[3].Value = PageSize;
            parameters[4].Value = "";
            parameters[5].Value = "Sort desc";
            parameters[6].Value = "";
            parameters[7].Direction = ParameterDirection.Output;//特别要注意
              Repeater1.DataSource =HHDBUtility.DbHelperSQL.RunProcedure("P_Pageing",parameters);
            Repeater1.DataBind();
            AspNetPager1.AlwaysShow = true;
            AspNetPager1.PageSize = PageSize;
            AspNetPager1.RecordCount = Convert.ToInt32(parameters[7].Value);
        }
    protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
    {
        AspNetPager1.CurrentPageIndex = e.NewPageIndex;
        DataListDataBind();
    } 

-----------------存储过程分页方法-----------------

USE [HHWeb]
GO
/****** Object:  StoredProcedure [dbo].[P_Pageing]    Script Date: 03/06/2012 10:16:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[P_Pageing]

@tbname     nvarchar(50),           --要分页显示的表的名称

@FieldKey   nvarchar(100),   --用于定位记录的主键(唯一键)字段,可以是逗号分隔的多个字段

@NowPage int=1,      --要显示的页码

@PageSize   int=20,     --每页显示的记录条数

@FieldShow nvarchar(500)='',  --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar(500)='',  --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序

@Where    nvarchar(500)='',   --查询条件

@Totalcount int OUTPUT    --总条数

AS

SET NOCount ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

    RAISERROR(N'对象"%s"不存在',1,16,@tbname)

    RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

    RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

    RETURN

END

 --分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

    RETURN

END

 

--其他参数检查及规范

IF ISNULL(@NowPage,0)<1 SET @NowPage=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=20

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

    SET @FieldOrder=N''

ELSE

    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)


--计算分页显示的TOPN值

DECLARE @TopN varchar(20)
SELECT @TopN=(@NowPage-1)*@PageSize

--判断查询条件是否存在
DECLARE @WhereOne nvarchar(1000)
IF ISNULL(@Where,N'')=N''
BEGIN
    SET @WhereOne=N' '
    SET @Where=N'WHERE '+@FieldKey+N' not in(select top '+@TopN+N' '+@FieldKey+N' from '+@tbname+N' '+@FieldOrder+N')'
END
ELSE
BEGIN
    SET @WhereOne=N'WHERE '+@Where+N' '
    SET @Where=N'WHERE '+@Where+N' and '+@FieldKey+N' not in(select top '+@TopN+N' '+@FieldKey+N' from '+@tbname+N' WHERE '+@Where+N' '+@FieldOrder+N')'
END

IF @Totalcount IS NULL

BEGIN

    DECLARE @sql nvarchar(4000)

    SET @sql=N'SELECT @Totalcount=Count(*)'

        +N' FROM '+@tbname

        +N' '+@WhereOne

    EXEC sp_executesql @sql,N'@Totalcount int OUTPUT',@Totalcount OUTPUT
    
END


--执行查询
EXEC(N'SELECT TOP '+@PageSize+' '+@FieldShow+' FROM '+@tbname+' with(nolock) '+@Where+' '+@FieldOrder+'')

--DECLARE @s int
--exec P_Pageing 'DownloadCodes','ID',4,2,'','AddTime desc','DowncodeNumber=1',@s OUTPUT
--select @s