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