如CSDN一样的分页

如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);
    }
}