VS2005+SQL SERVER 2000+AspNetPager1 分页存储

public partial class message_message : System.Web.UI.Page
{
    SqlConnection sqlcon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    SqlCommand sqlcom;

    private const string TableNames = "message";   //表名，可以是多个表，但不能用别名
    private const string PrimaryKey = "id";  //主键，可以为空，但@Order为空时该值不能为空
    private string Fields = "id,sendTime,Content,SendName,receiveName,status,status2,userid";  //要取出的字段，可以是多个表的字段，可以为空，为空表示select *
    private int PageSize = 20;  //每页记录数
    private int CurrentPage = 0;  //当前页，0表示第1页
    private string Filter = "";  //条件，可以为空，不用填 where
    private string Group = "";   //分组依据，可以为空，不用填 group by
    private string Order = "id";  //排序，可以为空，为空默认按主键升序排列，不用填 order by

    protected void Page_Load(object sender, EventArgs e)
    {
      if (!IsPostBack)
      {
        sqlcon.Open();
        sqlcom = new SqlCommand("select count(id) from message", sqlcon);
        AspNetPager1.AlwaysShow = true;
        int totalCount = (int)sqlcom.ExecuteScalar();
        AspNetPager1.RecordCount = totalCount;
        sqlcon.Close();
        bind();
      }
    }

    public void bind()
    {
        //下面是AspNetPager1分页
        //SqlDataAdapter dr = new SqlDataAdapter("select top 100 * from message", sqlcon);
        //DataSet ds = new DataSet();
        //dr.Fill(ds, AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1), AspNetPager1.PageSize, "message");
        //GridView1.DataSource = ds.Tables["message"];
        //GridView1.DataKeyNames = new string[] { "id" };//主键
        //GridView1.DataBind();
        //this.AspNetPager1.RecordCount = 100;

        //下面是存储过程AspNetPager1分页
        SqlCommand cmd = new SqlCommand("usp_PagingLarge", sqlcon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@TableNames", SqlDbType.VarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 100));
        cmd.Parameters.Add(new SqlParameter("@Fields", SqlDbType.VarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4));
        cmd.Parameters.Add(new SqlParameter("@CurrentPage", SqlDbType.Int, 4));
        cmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@Group", SqlDbType.VarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@Order", SqlDbType.VarChar, 200));
        cmd.Parameters["@TableNames"].Value = TableNames;
        cmd.Parameters["@PrimaryKey"].Value = PrimaryKey;
        cmd.Parameters["@Fields"].Value = Fields;
        cmd.Parameters["@PageSize"].Value = PageSize;
        cmd.Parameters["@CurrentPage"].Value = CurrentPage;
        cmd.Parameters["@Filter"].Value = Filter;
        cmd.Parameters["@Group"].Value = Group;
        cmd.Parameters["@Order"].Value = Order;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds.Tables[0].DefaultView;
        GridView1.DataKeyNames = new string[] { "id" };//主键
        GridView1.DataBind();
    }
    protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
    {
        AspNetPager1.CurrentPageIndex = e.NewPageIndex;
        CurrentPage = AspNetPager1.CurrentPageIndex - 1;
        bind();
    }

--------------------------------------------------------------------------------------

存储过程

 

CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200),    --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100),    --主键，可以为空，但@Order为空时该值不能为空
@Fields    VARCHAR(200),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,            --每页记录数
@CurrentPage INT,        --当前页，0表示第1页
@Filter VARCHAR(200) = '',    --条件，可以为空，不用填 where
@Group VARCHAR(200) = '',    --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = ''    --排序，可以为空，为空默认按主键升序排列，不用填 order by
AS
BEGIN
    DECLARE @SortColumn VARCHAR(200)
    DECLARE @Operator CHAR(2)
    DECLARE @SortTable VARCHAR(200)
    DECLARE @SortName VARCHAR(200)
    IF @Fields = ''
        SET @Fields = '*'
    IF @Filter = ''
        SET @Filter = 'WHERE 1=1'
    ELSE
        SET @Filter = 'WHERE ' +  @Filter
    IF @Group <>''
        SET @Group = 'GROUP BY ' + @Group

    IF @Order <> ''
    BEGIN
        DECLARE @pos1 INT, @pos2 INT
        SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
        IF CHARINDEX(' DESC', @Order) > 0
            IF CHARINDEX(' ASC', @Order) > 0
            BEGIN
                IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                    SET @Operator = '<='
                ELSE
                    SET @Operator = '>='
            END
            ELSE
                SET @Operator = '<='
        ELSE
            SET @Operator = '>='
        SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
        SET @pos1 = CHARINDEX(',', @SortColumn)
        IF @pos1 > 0
            SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
        SET @pos2 = CHARINDEX('.', @SortColumn)
        IF @pos2 > 0
        BEGIN
            SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
            IF @pos1 > 0
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
            ELSE
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
        END
        ELSE
        BEGIN
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
        END
    END
    ELSE
    BEGIN
        SET @SortColumn = @PrimaryKey
        SET @SortTable = @TableNames
        SET @SortName = @SortColumn
        SET @Order = @SortColumn
        SET @Operator = '>='
    END

    DECLARE @type varchar(50)
    DECLARE @prec int
    SELECT @type=t.name, @prec=c.prec
    FROM sysobjects o
    JOIN syscolumns c on o.id=c.id
    JOIN systypes t on c.xusertype=t.xusertype
    WHERE o.name = @SortTable AND c.name = @SortName
    IF CHARINDEX('char', @type) > 0
    SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

    DECLARE @TopRows INT
    SET @TopRows = @PageSize * @CurrentPage + 1
    print @TopRows
    print @Operator
    EXEC('
        DECLARE @SortColumnBegin ' + @type + '
        SET ROWCOUNT ' + @TopRows + '
        SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER

BY ' + @Order + '
        SET ROWCOUNT ' + @PageSize + '
        SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator +

'@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '   
    ')   
END
GO

---------------------------------------------------------------

前台

 

<webdiyer:AspNetPager ID="AspNetPager1" runat="server"
CustomInfoHTML="第%CurrentPageIndex%页，共%PageCount%页，每页%PageSize%条"
FirstPageText="首页" LastPageText="尾页" LayoutType="Table" NextPageText="下一页" PageIndexBoxType="DropDownList"
PagingButtonLayoutType="Span" PrevPageText="上一页" ShowCustomInfoSection="Left"
ShowPageIndexBox="Always" SubmitButtonText="Go" PageSize="20" TextAfterPageIndexBox="页"
TextBeforePageIndexBox="转到" OnPageChanging="AspNetPager1_PageChanging">
</webdiyer:AspNetPager>

 

 

注：如果是多表，那么private string Order = "id";这里的id就要指定是哪个表，即：private string Order = "表1.id";