爱奇思

学习就是——从糊涂中慢慢走向清醒,然后再从清醒中慢慢走向糊涂,所以说我说它是一个O,只是圆的半径慢慢的扩大罢了!
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

如CSDN一样的分页 (转)

Posted on 2009-08-05 18:51  牛牛博客  阅读(412)  评论(0编辑  收藏  举报

如CSDN一样的分页 
分页样图

源码下载:http://download.csdn.net/source/369744

 

SQL

CREATE PROCEDURE sp_page
@CurrentPage int, @PageSize int,@Field_Info varchar(500),@Table_info varchar(20),@Field_id varchar(10),@intOrder int,@otherwhere varchar(50),@RecordCount int output,@PageCount int output
--@CurrentPage为显示那一页,@PageSize为每一页显示几行,@Field_info为要显示的字段可以为*,@Table_info为要查询的表或视图,@field_id为按这个字段排序,@intorder0为升序排1为降序排,@otherwhere为条件,@RecordCount为总行数,@PageCount为总页数
AS
begin
    DECLARE @MinPage int, @MaxPage int
    declare @sql varchar(1000)
    declare @sqlt nvarchar(300)
    declare @order varchar(4)
    set @Field_Info = replace(@Field_Info,'''','')       --除去@field_info中的'
    set @Table_info = replace(@table_info,'''','')       --除去@table_info中的'
    set @Field_id = replace(@Field_id,'''','')            --除去@field_id中的'
    set @otherwhere = replace(@otherwhere,'''','''''')     --将@otherwhere中的'换成''让SQL语句正确释别'
    set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
    exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output     --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
    IF @PageSize <= 0
        begin
            set @PageSize = 10
        end
    else if @PageSize > @RecordCount
        begin
            set @pageSize = @RecordCount
        end
    set @pagecount = @RecordCount / @PageSize
    if ((@recordcount % @pagesize) != 0)                    --如果除不尽则加一页
        begin
            set @PageCount = @RecordCount / @PageSize
            set @PageCount = @pagecount + 1
        end
    else
        begin
            set @pagecount = @recordcount /@PageSize
        end
    IF @CurrentPage <= 0
        begin
            set @CurrentPage = 1
        end
    else if @CurrentPage > @pagecount
        begin       
            set @currentpage = @pagecount        --如果输入页数大于总页数则符最后一页
        end
    SET @MinPage = (@CurrentPage - 1) * @PageSize + 1
    SET @MaxPage = @MinPage + @PageSize - 1
   
    BEGIN
        if @intorder = 0
            set @order = 'asc'
        else
            set @order = 'desc'
        if @Field_Info like ''
            set @field_Info = '*'
        if @otherwhere like ''
            set @sql = 'select ' + @Field_Info + ' from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage)
        else
            set @sql = 'select ' + @Field_Info + ' from (select ' +  @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ' and ' + @otherwhere
        exec(@sql)
    END
end

 

declare @rcon int
declare @pcon int
exec sp_page 8,73,'','user_info','id',0,'',@rcon output,@pcon output


HTML

<%...@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Pageing_Default2" %>
<%...@ Register TagPrefix="cc1" Namespace="CutePager" Assembly="ASPnetPagerV2netfx2_0" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>无标题页</title>
    <link href="Styles/lightstyle.css" type="text/css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:repeater id="Repeater1" runat="server">
                <HeaderTemplate>
                    <table style="background-color:#ffcc66;" cellpadding="5" cellspacing="0">
                        <tr>
                            <th style="width:70px;">
                                index</th>
                            <th style="width:200px;">
                                ProductName</th>
                        </tr>
                    </table>
                </HeaderTemplate>
                <ItemTemplate>
                    <table cellpadding="5" cellspacing="0" style="background-color:#f0f1f2;">
                        <tr>
                            <td style="width:70px;" align="center"><%...#Eval("RowNumber")%></td>
                            <td style="width:200px;"><%...#Eval("ProductName")%></td>
                        </tr>
                    </table>
                </ItemTemplate>
                <AlternatingItemTemplate>
                    <table cellpadding="5" cellspacing="0" style="background-color:#ccccff;">
                        <tr>
                            <td style="width:70px;" align="center"><%...#Eval("RowNumber")%></td>
                            <td style="width:200px;"><%...#Eval("ProductName")%></td>
                        </tr>
                    </table>
                </AlternatingItemTemplate>
            </asp:repeater>
        <br />
        <cc1:pager id="pager1" runat="server" oncommand="pager_Command" showfirstlast="true">
        </cc1:pager>
    </div>
    </form>
</body>
</html>

C#

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Pageing_Default2 : System.Web.UI.Page
...{
    protected void Page_Load(object sender, EventArgs e)
    ...{
        if (!IsPostBack)
        ...{
            pager1.CurrentIndex = 1;
            BindRepeater(1);
        }
    }

    const string strConn = "Data Source=SA;Initial Catalog=Northwind;Integrated Security=True";

    public void pager_Command(object sender, CommandEventArgs e)
    ...{
        int currnetPageIndx = Convert.ToInt32(e.CommandArgument);
        pager1.CurrentIndex = currnetPageIndx;
        BindRepeater(currnetPageIndx);
    }

       
    private void BindRepeater(int pageNo)
    ...{
 
        /**//*
         @CurrentPage int, --@CurrentPage为显示那一页
        @PageSize int,--@PageSize为每一页显示几行
        @Field_Info varchar(500),--@Field_info为要显示的字段可以为*
        @Table_info varchar(20),--@Table_info为要查询的表或视图
        @Field_id varchar(10),--@field_id为按这个字段排序
        @intOrder int,--@intorder0为升序排1为降序排
        @otherwhere varchar(50),--@otherwhere为条件
        @RecordCount int output,--@RecordCount为总行数
        @PageCount int output--@PageCount为总页数
         */
        SqlConnection cn = new SqlConnection(strConn);

        SqlCommand Cmd = new SqlCommand("dbo.sp_page", cn);
        Cmd.CommandType = CommandType.StoredProcedure;
        SqlDataReader dr;

        Cmd.Parameters.Add("@CurrentPage", SqlDbType.Int, 4).Value = pageNo;
        Cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pager1.PageSize;
        Cmd.Parameters.Add("@Field_Info", SqlDbType.VarChar, 500).Value = "*";
        Cmd.Parameters.Add("@Table_info", SqlDbType.VarChar, 20).Value = "Products";        //表名
        Cmd.Parameters.Add("@Field_id", SqlDbType.VarChar, 10).Value = "ProductID";
        Cmd.Parameters.Add("@intOrder", SqlDbType.Int).Value = 1;             //排序
        Cmd.Parameters.Add("@otherwhere", SqlDbType.VarChar,50).Value = "";       //条件
        Cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;   //总行数
        Cmd.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        cn.Open();
        dr = Cmd.ExecuteReader();

        Repeater1.DataSource = dr;
        Repeater1.DataBind();

        dr.Close();
        cn.Close();

        Int32 _totalRecords = Convert.ToInt32(Cmd.Parameters["@RecordCount"].Value);
        pager1.ItemCount = _totalRecords;
        pager1.PageCount = Convert.ToInt32(Cmd.Parameters["@PageCount"].Value);
    }
}


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/liuliang1232005/archive/2008/05/24/2476231.aspx