如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
