有源码,也有存储过程。 贴一段给你看看 --创建存储过程CREATE procedure wqnews_GetPagedWQNews (@pagesize int, @pageindex int, @docount bit) as set nocount on if(@docount=1) select count(newsid) from wqnews else begin declare @indextable table(id int identity(1,1),nid int) declare @PageLowerBound int declare @PageUpperBound int set @PageLowerBound=(@pageindex-1)*@pagesize set @PageUpperBound=@PageLowerBound+@pagesize set rowcount @PageUpperBound insert into @indextable(nid) select newsid from wqnews order by addtime desc select O.newsid,O.source,O.heading,O.addtime from wqnews O,@indextable t where O.newsid=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id end set nocount off go
请各位研究一下,效率如何? CREATE proc page @RecordCount int output, @QueryStr nvarchar(100)='table1',--表名、视图名、查询语句 @PageSize int=20, --每页的大小(行数) @PageCurrent int=2, --要显示的页 从0开始 @FdShow nvarchar (1000)='*', --要显示的字段列表 @IdentityStr nvarchar (100)='id', --主键 @WhereStr nvarchar (1000)='1=1', @FdOrder nvarchar(100)='desc' --排序 只能取desc或者asc asdeclare @sql nvarchar(4000) if @WhereStr = '' begin set @WhereStr = '1=1' end set @WhereStr = ' ( ' + @WhereStr + ' ) ' if @PageCurrent = 0 begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder end else begin if upper(@FdOrder) = 'DESC' begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc' end else begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc' end end --print @sql execute sp_executesql @sql if(@RecordCount is null or @RecordCount<0)begin declare @tsql nvarchar(1000) set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output select @RecordCount endGO
提供一个共公的分页过程给你参考(从自己的数据组件抽取来,可能有些不完整) 功能和表结构,数据库类型无任何关系.纯C#代码实现. 在一百万级的数据中,获取最慢的一页的效率大概在1秒左右(测试电脑是P1.7 521M) 前题是表或视图有PK字段主索引!public interface IFilter { string Where(); System.Data.IDataParameter[] Parameters(); } public struct LoadRegion { public int Start ; public int End ; } public class QueryByCursor { public QueryByCursor(string table) { Table = table; Region.Start = 0; Region.End = 99999999; } private string Table = null; public LoadRegion Region; private string mPKField = null; public string PKField { get { return mPKField; } set { mPKField = value; } } public System.Collections.IList Query(System.Data.IDbConnection conn,System.Data.IDbCommand cmd, IFilter filter) { if (conn.State == System.Data.ConnectionState.Closed) conn.Open(); cmd.Connection = conn; System.Data.IDataReader reader = null; if (PKField != null && PKField != "" & Region.End- Region.Start <1000) { return DoQueryByPK(cmd, filter); } else { return DoQuery(cmd, filter); } } private System.Collections.IList DoQuery(System.Data.IDbCommand cmd, IFilter filter) { cmd.CommandText = "select * from " + Table; AddFilter(cmd, filter); System.Collections.ArrayList list = new System.Collections.ArrayList(); int i = 0; using (System.Data.IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (i >= Region.Start && i <= Region.End) { //list add entity } i++; } } return list; } private System.Collections.IList DoQueryByPK(System.Data.IDbCommand cmd, IFilter filter) { cmd.CommandText = "select "+PKField +" from " + Table; AddFilter(cmd,filter); System.Collections.ArrayList list = new System.Collections.ArrayList(); int i = 0; using (System.Data.IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (i >= Region.Start && i <= Region.End) { list.Add(reader[0]); } i++; } } if (list.Count == 0) return new System.Collections.ArrayList(); System.Text.StringBuilder where = new StringBuilder(); for (int k = 0; k < list.Count; k++) { if (k == 0) { where.Append("('" + list[k] + "'"); } else { where.Append(",'" + list[k] + "'"); } where.Append(")"); } cmd.CommandText = "select * from " + Table +" where " + PKField +" in" + where; cmd.Parameters.Clear(); System.Collections.ArrayList record = new System.Collections.ArrayList(); using (System.Data.IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { //record add entity } } return record; } private static void AddFilter(System.Data.IDbCommand cmd, IFilter filter) { string where = filter.Where(); System.Collections.IList list = filter.Parameters(); if (filter != null) cmd.CommandText +=" where " + where; if (list != null && list.Count > 0) { foreach (object item in list) { cmd.Parameters.Add(item); } } } }
看看这个,也许你会得到些启示: datagrid自定义分页是datagrid的一个重要的功能,datagrid自定义分页主要用于数据源较大时。因为数据源大(记录多),加载时间长,反应慢,耗服务器的资源。而且每显示一页就要重新加载所有的数据。而如果我们用自定义分页,则每一次只加载一页的记录,也就是只加载我们要显示的记录。这样加载数据时间短,反应快,节约服务器的资源。在做项目的过程中,我们也经常需要用到自定义功能。今天我们就说说怎样自定义分页。 先看看datagrid的自定义分页的原理,它主要依据两个主要属性,第一个就是VirtualItemCount属性,它表示datagrid一共要显示多少条记录,它就是的作用是用于生成pager(分页导航行),通过结合另外两个属性pagesize和PageButtonCount,datagrid就知道要分成多少页了及当前要显示多少个分页按钮,我们不难得到总共要显示的总页数=(VirtualItemCount+pagesize-1)/pagesize;如要总页数<PageButtonCount,则显示总页数个按钮;如果总页数>PageButtonCount,则显示PageButtonCount个按钮,当然到了最后一页就只显示VirtualItemCount% pagesize(总记录条数除以每页显示记录的余数)个按钮。另一个重要的属性就是datasource(数据源),自定义分页的一个重要的特点是显示数据源中所有的记录,如果数据源中有一条记录,则显示一条记录;如果数据源中有一万条记录,则它会显示一万条记录,可能你的机子就慢了 : )。所以自定义最重要的一点是如何设置或获取数据源了。 接下来,我们先说说自定义分页的主要步骤: 1. 设置datagrid的VirtualItemCount属性; 2. 获取datagrid的datasource(数据源); 3. 绑定数据到datagrid; 4. 设置新页的页码(datagrid.currentpageindex属性)。 重复上述2,3,4步。 下面我们以一个例子来讲解datagrid 的自定义过程。 例子要求机子装有ms sql server 7.0 或 2000,当然还要能运行asp.net页了(废话)。 我们先来写一个通用的存储过程,用于分页,返回某页的要显示的记录集,及一个输出参数--总的记录条数,但这个存储过程有缺陷,例如只能用于单表查询,必须要有条件语句等。 CREATE PROCEDURE up_custompage @vc_order_column_name varchar(100),@vc_select_column_list varchar(100), @vc_select_table_list varchar(100),@vc_condition varchar(100),@page_size int,@current_page int,@total1 int output /* ( @vc_order_column_name :表要排序列的列名,只能按一列排序,而且该列必须得在输出列表中; @vc_select_column_list :返回列的列名列表; @vc_select_table_list:要查询的表名; @vc_condition:查询条件的字符串,必须要有查询条列,否则会抛出异常; @page_size:每页显示记录的条数; @current_page:当前页的页码; @total1:所有符合条件的记录的总数。 构造的sql语句=select top 每页显示记录的条数 * from (select top 每页显示记录的条数 返回列的列名列表 from 要查询的表名 where 要排序列的列名 in (select top 每页显示记录的条数 X 当前页的页码 要排序列的列名 from 要查询的表名 where 查询条件 order by 要排序列的列名) order by 要排序列的列名 desc ) as temp1 order by 要排序列的列名 ) */ AS --声明要用到的变量,@temp1是正常的分页语句字符串,@temp2是最后一页的分页语句字符串,@page_total表一共有几页,@last_page --是最后一页的页码 declare @temp1 varchar(500),@temp2 nvarchar(500),@page_total int,@last_page int --构造获得总页的数的检索语句 set @temp2=N'select @total2=count(*) from ' + @vc_select_table_list + ' where ' + @vc_condition --执行检索语句,取得总的记录条数 exec sp_executesql @temp2,N' @total2 int output ',@total1 output/*构造分页检索语句,基本原理是先取出@page_size*@current_page条记录,相当于是取出当前页及当前页前面的所有页面的记录然后取出当前面所要显示的记录,也就是反序排序后取前@page_size条记录;最后再反序排序(因为前面的顺序被反排过一次,现在再反排一次,正好是我们要的顺序),最后执行,返回结果集。 */ if @total1>0 begin set @page_total=(@total1+@page_size-1)/@page_size --如果当前页不是最后一页 if @current_page<@page_total set @temp1='select top ' + cast(@page_size as varchar(4)) + ' * from (select top ' + cast(@page_size as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name + ' in (select top ' + cast(@page_size*@current_page as varchar(10)) + ' ' + @vc_order_column_name + ' from ' + @vc_select_table_list + ' where '+ @vc_condition + ' order by ' + @vc_order_column_name + ') order by ' + @vc_order_column_name + ' DESC) as temp1 order by ' + @vc_order_column_name else --最后一页只返回分页后的最后几条记录,也就是@total1%@page_size条记录 begin set @last_page=@total1%@page_size set @temp1='select top ' + cast(@last_page as varchar(4)) + ' * from (select top ' + cast(@last_page as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name + ' in (select top ' + cast(@total1 as varchar(10)) + ' ' + @vc_order_column_name + ' from ' + @vc_select_table_list + ' where '+ @vc_condition + ' order by ' + @vc_order_column_name + ') order by ' + @vc_order_column_name + ' DESC) as temp1 order by ' + @vc_order_column_name end --执行检索 exec(@temp1) end else return-------------------------------------------------------------------------
继续: __________________________ 然后在新建一个aspx页面,代码如下: <%@ Page language="c#" Codebehind="custompage.aspx.cs" AutoEventWireup="false" Inherits="cyc_test.custompage" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <title>custompage</title> <meta content="Microsoft Visual Studio 7.0" name="GENERATOR"> <meta content="C#" name="CODE_LANGUAGE"> <meta content="JavaScript" name="vs_defaultClientScript"> <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"> <style>A { BEHAVIOR: url(MouseOver.htc) } HR { COLOR: black; HEIGHT: 2px } .StdText { FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-FAMILY: verdana } .StdTextBox { BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; FONT-SIZE: 9pt; FILTER: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true'); BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; FONT-FAMILY: verdana } .Shadow { FILTER: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true') } </style> </HEAD> <body style="FONT-FAMILY: arial" bgColor="ivory" MS_POSITIONING="GridLayout"> <form id="custompage" method="post" runat="server"> <h2>项目总结之datagrid自定义分页篇 </h2> <asp:label id="Label1" runat="server" font-bold="true" cssclass="StdText">当前路径: </asp:label><asp:label id="lblURL" style="COLOR: blue" runat="server" cssclass="StdText"></asp:label> <!-- Query --><br> <asp:label id="Label2" runat="server" cssclass="stdtext" Text="查询语句:"></asp:label> <asp:textbox id="Textbox1" runat="server" cssclass="stdtextbox" text="SELECT employeeid, firstname, lastname,title FROM Employees where employeeid>0 order by employeeid" width="765px" Enabled="false"></asp:textbox> <hr> <!-- Show the information --> <asp:datagrid id="grid" runat="server" OnPageIndexChanged="PageIndexChanged" AllowCustomPaging="True" AllowPaging="True" PageSize="5" BorderWidth="1" BorderColor="black" BorderStyle="solid" BackColor="White" CssClass="Shadow" GridLines="vertical" CellSpacing="0" CellPadding="2" Font-Names="Verdana" Font-Size="Smaller"> <PagerStyle Font-Bold="true" Mode="NumericPages" BackColor="palegreen" /> <AlternatingItemStyle BackColor="#eeeeee" /> <ItemStyle BackColor="White" /> <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="Navy" /> </asp:datagrid></form> </body> </HTML> 再写后台的源代码: using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient;namespace cyc_test { /// <summary> /// custompage 的摘要说明。 /// </summary> public class custompage : System.Web.UI.Page { protected System.Web.UI.WebControls.Label Label1; protected System.Web.UI.WebControls.Label lblURL; protected System.Web.UI.WebControls.Label Label2; protected System.Web.UI.WebControls.TextBox Textbox1; protected System.Web.UI.WebControls.DataGrid grid;
<%@ language="vb" debug=true%> <%@import namespace=system.data %> <%@import namespace="system.data.oledb"%> <script language="vb" runat="server"> sub page_load(sender as object,e as eventargs) BindGrid() end subsub DataGrid1_PageIndexChanged(sender as object,e as datagridpagechangedeventargs) datagrid1.currentpageindex=e.newpageindex BindGrid() end subsub BindGrid() dim conn as new oledbconnection("provider=microsoft.jet.oledb.4.0;data source="&server.mappath("sfz.mdb")) dim comm as new oledbcommand("select * from [sfz] where Address like '%清%'",conn)' dim objread as oledbdatareader ' conn.open() ' datagrid1.datasource=comm.executereader() ' datagrid1.databind() ' conn.close() conn.open() dim DS as new dataset() dim objadpt as new oledbdataadapter(comm) objadpt.fill(ds,"ds1") datagrid1.datasource=ds.tables("ds1").defaultview datagrid1.databind() conn.close() End sub</script><form method="post" runat="server"> <asp:datagrid id="datagrid1" runat=server allowpaging=true pagesize=4 onpageindexchanged="DataGrid1_PageIndexChanged"> <columns> <asp:TemplateColumn> <ItemTemplate> <asp:LinkButton runat="server" Text="编辑" CommandName="Edit" CausesValidation="false"></asp:LinkButton> </ItemTemplate> <EditItemTemplate> <asp:LinkButton runat="server" Text="更新" CommandName="Update"></asp:LinkButton> <asp:LinkButton runat="server" Text="取消" CommandName="Cancel" CausesValidation="false"></asp:LinkButton> </EditItemTemplate> </asp:TemplateColumn> </columns> <pagerstyle nextpagetext="下页" prevpagetext="上页" /> </asp:datagrid> </form>
以下为存储过程,一个是分页存储过程,一个是得到总记录的存储过程 分页存储过程: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER PROCEDURE Test_GetOrders (@PAGESIZE INT, --页面大小,如每页存储20条记录 @PAGEINDEX INT --当前页码 ) AS SET NOCOUNT ON BEGINDECLARE @INDEXTABLE TABLE(ID INT IDENTITY(1,1),NID INT) --定义表变量DECLARE @PAGELOWERBOUND INT --定义此页的底码DECLARE @PAGEUPPERBOUND INT --定义此页的顶码SET @PAGELOWERBOUND=(@PAGEINDEX-1)*@PAGESIZESET @PAGEUPPERBOUND=@PAGELOWERBOUND+@PAGESIZESET ROWCOUNT @PAGEUPPERBOUNDINSERT INTO @INDEXTABLE(NID) SELECT OrderID FROM Orders ORDER BY OrderID DESCSELECT ORDERID,CUSTOMERID,CONVERT(CHAR(10),ORDERDATE,120) AS ORDERDATE, SHIPCITY,SHIPCOUNTRY,ISNULL(SHIPREGION,'无') AS SHIPREGION FROM ORDERS A INNER JOIN @INDEXTABLE T ON A.ORDERID=T.NID WHERE T.ID>@PAGELOWERBOUND AND T.ID<=@PAGEUPPERBOUND ORDER BY T.IDENDSET NOCOUNT OFF GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO得到总记录的存储过程: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOALTER PROC GetOrderCount AS --得到ORDERS表的总记录数 SELECT COUNT(*) FROM ORDERSGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
http://cncxz.cnblogs.com/archive/2005/12/19/300120.html
http://www.codeproject.com/aspnet/PagingLarge.asp
---------------------------------------------------------------
看了这篇文章发现我用过的分页方法属于最优的方案。感到欣慰啊。。
[email protected]
http://www.webdiyer.com
贴一段给你看看
--创建存储过程CREATE procedure wqnews_GetPagedWQNews
(@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(newsid) from wqnews
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select newsid from wqnews order by addtime desc
select O.newsid,O.source,O.heading,O.addtime from wqnews O,@indextable t where O.newsid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
go
CREATE proc page
@RecordCount int output,
@QueryStr nvarchar(100)='table1',--表名、视图名、查询语句
@PageSize int=20, --每页的大小(行数)
@PageCurrent int=2, --要显示的页 从0开始
@FdShow nvarchar (1000)='*', --要显示的字段列表
@IdentityStr nvarchar (100)='id', --主键
@WhereStr nvarchar (1000)='1=1',
@FdOrder nvarchar(100)='desc' --排序 只能取desc或者asc
asdeclare
@sql nvarchar(4000)
if @WhereStr = '' begin
set @WhereStr = '1=1'
end
set @WhereStr = ' ( ' + @WhereStr + ' ) '
if @PageCurrent = 0 begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
end
else begin
if upper(@FdOrder) = 'DESC' begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc'
end
else begin
set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc'
end
end
--print @sql
execute sp_executesql @sql
if(@RecordCount is null or @RecordCount<0)begin
declare @tsql nvarchar(1000)
set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr
exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output
select @RecordCount
endGO
功能和表结构,数据库类型无任何关系.纯C#代码实现.
在一百万级的数据中,获取最慢的一页的效率大概在1秒左右(测试电脑是P1.7 521M)
前题是表或视图有PK字段主索引!public interface IFilter
{
string Where();
System.Data.IDataParameter[] Parameters();
}
public struct LoadRegion
{
public int Start ;
public int End ;
} public class QueryByCursor
{
public QueryByCursor(string table)
{
Table = table;
Region.Start = 0;
Region.End = 99999999;
}
private string Table = null;
public LoadRegion Region;
private string mPKField = null;
public string PKField
{
get
{
return mPKField;
}
set
{
mPKField = value;
}
}
public System.Collections.IList Query(System.Data.IDbConnection conn,System.Data.IDbCommand cmd, IFilter filter)
{
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
cmd.Connection = conn;
System.Data.IDataReader reader = null;
if (PKField != null && PKField != "" & Region.End- Region.Start <1000)
{
return DoQueryByPK(cmd, filter);
}
else
{
return DoQuery(cmd, filter);
}
}
private System.Collections.IList DoQuery(System.Data.IDbCommand cmd, IFilter filter)
{
cmd.CommandText = "select * from " + Table;
AddFilter(cmd, filter);
System.Collections.ArrayList list = new System.Collections.ArrayList();
int i = 0;
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (i >= Region.Start && i <= Region.End)
{
//list add entity
}
i++;
}
}
return list;
}
private System.Collections.IList DoQueryByPK(System.Data.IDbCommand cmd, IFilter filter)
{
cmd.CommandText = "select "+PKField +" from " + Table;
AddFilter(cmd,filter);
System.Collections.ArrayList list = new System.Collections.ArrayList();
int i = 0;
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (i >= Region.Start && i <= Region.End)
{
list.Add(reader[0]);
}
i++;
}
}
if (list.Count == 0)
return new System.Collections.ArrayList();
System.Text.StringBuilder where = new StringBuilder();
for (int k = 0; k < list.Count; k++)
{
if (k == 0)
{
where.Append("('" + list[k] + "'");
}
else
{
where.Append(",'" + list[k] + "'");
}
where.Append(")");
}
cmd.CommandText = "select * from " + Table +" where " + PKField +" in" + where;
cmd.Parameters.Clear();
System.Collections.ArrayList record = new System.Collections.ArrayList();
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//record add entity
}
}
return record;
}
private static void AddFilter(System.Data.IDbCommand cmd, IFilter filter)
{
string where = filter.Where();
System.Collections.IList list = filter.Parameters();
if (filter != null)
cmd.CommandText +=" where " + where;
if (list != null && list.Count > 0)
{
foreach (object item in list)
{
cmd.Parameters.Add(item);
}
}
}
}
datagrid自定义分页是datagrid的一个重要的功能,datagrid自定义分页主要用于数据源较大时。因为数据源大(记录多),加载时间长,反应慢,耗服务器的资源。而且每显示一页就要重新加载所有的数据。而如果我们用自定义分页,则每一次只加载一页的记录,也就是只加载我们要显示的记录。这样加载数据时间短,反应快,节约服务器的资源。在做项目的过程中,我们也经常需要用到自定义功能。今天我们就说说怎样自定义分页。
先看看datagrid的自定义分页的原理,它主要依据两个主要属性,第一个就是VirtualItemCount属性,它表示datagrid一共要显示多少条记录,它就是的作用是用于生成pager(分页导航行),通过结合另外两个属性pagesize和PageButtonCount,datagrid就知道要分成多少页了及当前要显示多少个分页按钮,我们不难得到总共要显示的总页数=(VirtualItemCount+pagesize-1)/pagesize;如要总页数<PageButtonCount,则显示总页数个按钮;如果总页数>PageButtonCount,则显示PageButtonCount个按钮,当然到了最后一页就只显示VirtualItemCount% pagesize(总记录条数除以每页显示记录的余数)个按钮。另一个重要的属性就是datasource(数据源),自定义分页的一个重要的特点是显示数据源中所有的记录,如果数据源中有一条记录,则显示一条记录;如果数据源中有一万条记录,则它会显示一万条记录,可能你的机子就慢了 : )。所以自定义最重要的一点是如何设置或获取数据源了。
接下来,我们先说说自定义分页的主要步骤:
1. 设置datagrid的VirtualItemCount属性;
2. 获取datagrid的datasource(数据源);
3. 绑定数据到datagrid;
4. 设置新页的页码(datagrid.currentpageindex属性)。
重复上述2,3,4步。
下面我们以一个例子来讲解datagrid 的自定义过程。
例子要求机子装有ms sql server 7.0 或 2000,当然还要能运行asp.net页了(废话)。
我们先来写一个通用的存储过程,用于分页,返回某页的要显示的记录集,及一个输出参数--总的记录条数,但这个存储过程有缺陷,例如只能用于单表查询,必须要有条件语句等。
CREATE PROCEDURE up_custompage @vc_order_column_name varchar(100),@vc_select_column_list varchar(100),
@vc_select_table_list varchar(100),@vc_condition varchar(100),@page_size int,@current_page int,@total1 int output
/*
(
@vc_order_column_name :表要排序列的列名,只能按一列排序,而且该列必须得在输出列表中;
@vc_select_column_list :返回列的列名列表;
@vc_select_table_list:要查询的表名;
@vc_condition:查询条件的字符串,必须要有查询条列,否则会抛出异常;
@page_size:每页显示记录的条数;
@current_page:当前页的页码;
@total1:所有符合条件的记录的总数。
构造的sql语句=select top 每页显示记录的条数 * from (select top 每页显示记录的条数 返回列的列名列表 from 要查询的表名 where 要排序列的列名 in (select top 每页显示记录的条数 X 当前页的页码 要排序列的列名 from 要查询的表名 where 查询条件 order by 要排序列的列名) order by 要排序列的列名 desc ) as temp1 order by 要排序列的列名
)
*/
AS
--声明要用到的变量,@temp1是正常的分页语句字符串,@temp2是最后一页的分页语句字符串,@page_total表一共有几页,@last_page
--是最后一页的页码
declare @temp1 varchar(500),@temp2 nvarchar(500),@page_total int,@last_page int
--构造获得总页的数的检索语句
set @temp2=N'select @total2=count(*) from ' + @vc_select_table_list + ' where ' + @vc_condition
--执行检索语句,取得总的记录条数
exec sp_executesql @temp2,N' @total2 int output ',@total1 output/*构造分页检索语句,基本原理是先取出@page_size*@current_page条记录,相当于是取出当前页及当前页前面的所有页面的记录然后取出当前面所要显示的记录,也就是反序排序后取前@page_size条记录;最后再反序排序(因为前面的顺序被反排过一次,现在再反排一次,正好是我们要的顺序),最后执行,返回结果集。
*/
if @total1>0
begin
set @page_total=(@total1+@page_size-1)/@page_size
--如果当前页不是最后一页
if @current_page<@page_total
set @temp1='select top ' + cast(@page_size as varchar(4)) + ' * from
(select top ' + cast(@page_size as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name
+ ' in (select top ' + cast(@page_size*@current_page as varchar(10)) + ' ' + @vc_order_column_name + ' from ' +
@vc_select_table_list + ' where '+ @vc_condition + ' order by ' +
@vc_order_column_name + ') order by ' + @vc_order_column_name + ' DESC) as temp1 order by ' + @vc_order_column_name
else
--最后一页只返回分页后的最后几条记录,也就是@total1%@page_size条记录
begin
set @last_page=@total1%@page_size
set @temp1='select top ' + cast(@last_page as varchar(4)) + ' * from
(select top ' + cast(@last_page as varchar(4)) + ' ' + @vc_select_column_list + ' from ' + @vc_select_table_list +' where ' + @vc_order_column_name
+ ' in (select top ' + cast(@total1 as varchar(10)) + ' ' + @vc_order_column_name + ' from ' +
@vc_select_table_list + ' where '+ @vc_condition + ' order by ' +
@vc_order_column_name + ') order by ' + @vc_order_column_name + ' DESC) as temp1 order by ' + @vc_order_column_name
end
--执行检索
exec(@temp1)
end
else
return-------------------------------------------------------------------------
__________________________
然后在新建一个aspx页面,代码如下:
<%@ Page language="c#" Codebehind="custompage.aspx.cs" AutoEventWireup="false" Inherits="cyc_test.custompage" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>custompage</title>
<meta content="Microsoft Visual Studio 7.0" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<style>A { BEHAVIOR: url(MouseOver.htc) }
HR { COLOR: black; HEIGHT: 2px }
.StdText { FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-FAMILY: verdana }
.StdTextBox { BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; FONT-SIZE: 9pt; FILTER: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true'); BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; FONT-FAMILY: verdana }
.Shadow { FILTER: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true') }
</style>
</HEAD>
<body style="FONT-FAMILY: arial" bgColor="ivory" MS_POSITIONING="GridLayout">
<form id="custompage" method="post" runat="server">
<h2>项目总结之datagrid自定义分页篇
</h2>
<asp:label id="Label1" runat="server" font-bold="true" cssclass="StdText">当前路径: </asp:label><asp:label id="lblURL" style="COLOR: blue" runat="server" cssclass="StdText"></asp:label>
<!-- Query --><br>
<asp:label id="Label2" runat="server" cssclass="stdtext" Text="查询语句:"></asp:label>
<asp:textbox id="Textbox1" runat="server" cssclass="stdtextbox" text="SELECT employeeid, firstname, lastname,title FROM Employees where employeeid>0 order by employeeid" width="765px" Enabled="false"></asp:textbox>
<hr>
<!-- Show the information -->
<asp:datagrid id="grid" runat="server" OnPageIndexChanged="PageIndexChanged" AllowCustomPaging="True" AllowPaging="True" PageSize="5" BorderWidth="1" BorderColor="black" BorderStyle="solid" BackColor="White" CssClass="Shadow" GridLines="vertical" CellSpacing="0" CellPadding="2" Font-Names="Verdana" Font-Size="Smaller">
<PagerStyle Font-Bold="true" Mode="NumericPages" BackColor="palegreen" />
<AlternatingItemStyle BackColor="#eeeeee" />
<ItemStyle BackColor="White" />
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="Navy" />
</asp:datagrid></form>
</body>
</HTML>
再写后台的源代码:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;namespace cyc_test
{
/// <summary>
/// custompage 的摘要说明。
/// </summary>
public class custompage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label lblURL;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.TextBox Textbox1;
protected System.Web.UI.WebControls.DataGrid grid;
private void Page_Load(object sender, System.EventArgs e)
{
// 第一次加载页时,初始化
if (!Page.IsPostBack)
{
lblURL.Text = Request.Url + "<hr>";
createdatasource(1);
}
} //绑定datagrid的函数
protected void createdatasource(int current_page)
{
string str_table_name,str_column_list,str_order_column,str_condition;
//查询的表名
str_table_name="employees";
//返回的列名列表
str_column_list="employeeid,firstname,lastname,title";
//排序列的列名
str_order_column="employeeid";
//查询的表件
str_condition="employeeid>0";
string strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
SqlConnection conn = new SqlConnection(strConn);
//声明执行存储过程的SqlCommand
SqlCommand scd_sel=new SqlCommand("up_custompage",conn);
scd_sel.CommandType=CommandType.StoredProcedure;
//给存储过程的参数赋值
SqlParameter sp_temp;
sp_temp=scd_sel.Parameters.Add("@vc_order_column_name",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_order_column;
sp_temp=scd_sel.Parameters.Add("@vc_select_column_list",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_column_list;
sp_temp=scd_sel.Parameters.Add("@vc_select_table_list",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_table_name;
sp_temp=scd_sel.Parameters.Add("@vc_condition",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_condition;
sp_temp=scd_sel.Parameters.Add("@page_size",SqlDbType.Int);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=grid.PageSize;
sp_temp=scd_sel.Parameters.Add("@current_page",SqlDbType.Int);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=current_page;
sp_temp=scd_sel.Parameters.Add("@total1",SqlDbType.Int);
sp_temp.Direction=ParameterDirection.Output;
//执行存储过程
SqlDataAdapter sda=new SqlDataAdapter();
sda.SelectCommand=scd_sel;
if (conn.State==ConnectionState.Closed)
conn.Open();
DataSet ds=new DataSet();
sda.Fill(ds,"tb1");
conn.Close();
//设置VirtualItemCount属性
grid.VirtualItemCount=(int)scd_sel.Parameters["@total1"].Value;
//绑定数据源
grid.DataSource=ds.Tables["tb1"].DefaultView;
grid.DataBind();
} #region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN:该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.grid.ItemCreated += new System.Web.UI.WebControls.DataGridItemEventHandler(this.grid_ItemCreated);
this.grid.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.PageIndexChanged);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion
//datagrid的ItemCreated事件,用于定制分页导航行
private void grid_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
ListItemType elemType = e.Item.ItemType;
//定制分页导航行,样式为[1] [2] 第 3 页 [4]
if (elemType == ListItemType.Pager)
{
TableCell pager = (TableCell) e.Item.Controls[0];
for (int i=0; i<pager.Controls.Count; i+=2)
{
Object o = pager.Controls[i];
if (o is LinkButton)
{
LinkButton h = (LinkButton) o;
h.Text = "[ " + h.Text + " ]";
}
else
{
Label l = (Label) o;
l.Text = "第" + l.Text + “页”;
}
}
}
}
//页选中(分页)事件
public void PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
grid.CurrentPageIndex = e.NewPageIndex;
//页码值是从零开始的,所以要加一
createdatasource(grid.CurrentPageIndex+1);
}
}
}
先讲一种简单的吧
1.不启用Datagrid AutoPage属性的,你可以简单地加上一个label于DataGrid下方,
然后简单地从SqlDataReader上获得,分页总数和当前记录集.
简单地Label.Text=<a href="default.aspx?page=1">1 </a> ;
Label.Text+=<a href="default.aspx?page=2">2 </a>
当然你可以用循环来输入.
最后,担心你不会绑定数据.这里一个示例.
比方说: Select @PageCount ;Select * from #temp;
注意这里返回两句Select.你可以用
SqlDataReader reader=cmd.ExecuteReader();
reader.Read();
int PageCount=reader.GetInt(0);
DataGrid.DataSource=reader.NextResult();
DataGrid.DataBind();这个方法实用范围广,DataList ,Repeater也同样适合.2.另外一个方法就是利用DataGrid自定义分页功能,它把label换成自己一部分,你可以有pagechange事件,你可以把一串数字绑定到分页上.另外,此方法只适合于DataGrid和DataView.
<%@import namespace=system.data %>
<%@import namespace="system.data.oledb"%>
<script language="vb" runat="server">
sub page_load(sender as object,e as eventargs)
BindGrid()
end subsub DataGrid1_PageIndexChanged(sender as object,e as datagridpagechangedeventargs)
datagrid1.currentpageindex=e.newpageindex
BindGrid()
end subsub BindGrid() dim conn as new oledbconnection("provider=microsoft.jet.oledb.4.0;data source="&server.mappath("sfz.mdb"))
dim comm as new oledbcommand("select * from [sfz] where Address like '%清%'",conn)' dim objread as oledbdatareader
' conn.open()
' datagrid1.datasource=comm.executereader()
' datagrid1.databind()
' conn.close() conn.open()
dim DS as new dataset()
dim objadpt as new oledbdataadapter(comm)
objadpt.fill(ds,"ds1")
datagrid1.datasource=ds.tables("ds1").defaultview
datagrid1.databind()
conn.close()
End sub</script><form method="post" runat="server">
<asp:datagrid id="datagrid1"
runat=server
allowpaging=true
pagesize=4
onpageindexchanged="DataGrid1_PageIndexChanged">
<columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:LinkButton runat="server" Text="编辑" CommandName="Edit" CausesValidation="false"></asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton runat="server" Text="更新" CommandName="Update"></asp:LinkButton>
<asp:LinkButton runat="server" Text="取消" CommandName="Cancel" CausesValidation="false"></asp:LinkButton>
</EditItemTemplate>
</asp:TemplateColumn>
</columns>
<pagerstyle nextpagetext="下页" prevpagetext="上页" />
</asp:datagrid>
</form>
http://www.webdiyer.com
下面还把常用的几种删除记录的方式也写了一下(1.根据选中的CheckBox,一次删除多条记录,2.属性生成器里添加的删除按钮列删除,3.在模板列中放入Button按钮,每次删除之前都会有提示框,代码如下)
说明:由于这个程序我用到了数据访问层,所以数据库访问层的代码没有放上来,自己可以写一下,用到地方下面的代码会有说明
关于删除的方法,由于NorthWind表是SQLSERVER自带的,所以用了一个方法代替一下,并没有真正的删除记录,代码我注释掉了,不过逻辑是一样的。 关于页面的跳转,我用了DropDownList控件和TextBox控件,用DropDownList控件看的清楚,使用方便,但是一旦记录很多,页数增加,那就要每次都清除,每次加载一次,可能会影响效率,所以我用了两种方法,供其选择。
希望这个例子对大家有所帮助,也希望能够多提意见!
以下为存储过程,一个是分页存储过程,一个是得到总记录的存储过程
分页存储过程:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE Test_GetOrders
(@PAGESIZE INT, --页面大小,如每页存储20条记录
@PAGEINDEX INT --当前页码
)
AS
SET NOCOUNT ON
BEGINDECLARE @INDEXTABLE TABLE(ID INT IDENTITY(1,1),NID INT) --定义表变量DECLARE @PAGELOWERBOUND INT --定义此页的底码DECLARE @PAGEUPPERBOUND INT --定义此页的顶码SET @PAGELOWERBOUND=(@PAGEINDEX-1)*@PAGESIZESET @PAGEUPPERBOUND=@PAGELOWERBOUND+@PAGESIZESET ROWCOUNT @PAGEUPPERBOUNDINSERT INTO @INDEXTABLE(NID) SELECT OrderID FROM Orders ORDER BY OrderID DESCSELECT ORDERID,CUSTOMERID,CONVERT(CHAR(10),ORDERDATE,120) AS ORDERDATE,
SHIPCITY,SHIPCOUNTRY,ISNULL(SHIPREGION,'无') AS SHIPREGION
FROM ORDERS A
INNER JOIN @INDEXTABLE T
ON A.ORDERID=T.NID
WHERE T.ID>@PAGELOWERBOUND AND T.ID<=@PAGEUPPERBOUND
ORDER BY T.IDENDSET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO得到总记录的存储过程:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROC GetOrderCount
AS
--得到ORDERS表的总记录数
SELECT COUNT(*) FROM ORDERSGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
<%@ Page language="c#" Codebehind="OrdersPage.aspx.cs" AutoEventWireup="false" Inherits="NetTest.OrdersPage" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>NorthWind数据库Orders表的分页例子</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<link href="Css/BasicLayout.css" rel="stylesheet" type="text/css">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<script language="javascript">
function SelectAll()
{
var len=document.Form1.elements.length;
var i;
for (i=0;i<len;i++)
{
if (document.Form1.elements[i].type=="checkbox")
{
document.Form1.elements[i].checked=true;
}
}
}
function UnSelectAll()
{
var len=document.Form1.elements.length;
var i;
for (i=0;i<len;i++)
{
if (document.Form1.elements[i].type=="checkbox")
{
document.Form1.elements[i].checked=false;
}
}
}
</script>
<form id="Form1" method="post" runat="server">
<table align="center" border="0" width="60%">
<tr>
<td align="right">
<asp:RegularExpressionValidator id="RegularExpressionValidator1" runat="server" ErrorMessage="页数只能为数字" Display="None"
ControlToValidate="txtPage" ValidationExpression="\d{0,10}"></asp:RegularExpressionValidator>
<asp:ValidationSummary id="ValidationSummary1" runat="server" ShowMessageBox="True" ShowSummary="False"></asp:ValidationSummary></td>
</tr>
<TR>
<TD align="center"><font style="FONT-SIZE: 10pt">共</font>
<asp:label id="lbTotalPage" runat="server" CssClass="pageLinks"></asp:label>
<font style="FONT-SIZE: 10pt">页/共
<asp:label id="lbTotalCount" runat="server" CssClass="pageLinks"></asp:label>
记录 当前</font>
<asp:label id="lbCurrentPage" runat="server" CssClass="pageLinks"></asp:label>
<font style="FONT-SIZE: 10pt">页</font>
<font style="FONT-SIZE: 10pt">跳转到</font>
<asp:dropdownlist id="ddlPage" runat="server" AutoPostBack="True" Width="65px"></asp:dropdownlist>
<font style="FONT-SIZE: 10pt">页</font>
<asp:TextBox id="txtPage" runat="server" Width="43px"></asp:TextBox>
<asp:Button id="btnGo" runat="server" CssClass="redButtonCss" Width="34px" Text="GO"></asp:Button><FONT size="2">
</FONT>
</TD>
</TR>
<tr>
<td align="right"><INPUT type="button" value="全部选择" onclick="SelectAll()"><FONT face="宋体">
</FONT><INPUT type="button" value="全部取消" onclick="UnSelectAll()"><FONT face="宋体">
</FONT>
<asp:Button id="btnDelete" runat="server" Text="删除"></asp:Button><FONT face="宋体">
</FONT>
</td>
</tr>
<tr>
<td>
<asp:DataGrid id="dbOrders" runat="server" AutoGenerateColumns="False" Width="100%" BorderStyle="None"
BorderColor="#93BEE2" PageSize="20" AllowCustomPaging="True" AllowPaging="True" DataKeyField="OrderID">
<ItemStyle Font-Size="X-Small" HorizontalAlign="Center" Height="25px" BackColor="#CCFFFF"></ItemStyle>
<HeaderStyle Font-Size="X-Small" Font-Bold="True" HorizontalAlign="Center" Height="25px" BackColor="#6699FF"></HeaderStyle>
<Columns>
<asp:TemplateColumn>
<ItemTemplate>
<FONT face="宋体">
<asp:CheckBox id="cbFlag" runat="server"></asp:CheckBox></FONT>
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="OrderID" HeaderText="订单编号"></asp:BoundColumn>
<asp:BoundColumn DataField="CustomerID" HeaderText="客户编号"></asp:BoundColumn>
<asp:BoundColumn DataField="OrderDate" HeaderText="订单日期"></asp:BoundColumn>
<asp:BoundColumn DataField="ShipCity" HeaderText="运输城市"></asp:BoundColumn>
<asp:BoundColumn DataField="ShipCountry" HeaderText="运输国家"></asp:BoundColumn>
<asp:BoundColumn DataField="ShipRegion" HeaderText="运输区域"></asp:BoundColumn>
<asp:ButtonColumn Text="删除" CommandName="Delete"></asp:ButtonColumn>
<asp:TemplateColumn>
<ItemTemplate>
<FONT face="宋体">
<asp:Button id="btnADelete" runat="server" CssClass="redButtonCss" CommandName="Delete" Text="删除"></asp:Button></FONT>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<PagerStyle Visible="False"></PagerStyle>
</asp:DataGrid>
</td>
</tr>
<tr>
<TD align="center" height="30"><B><asp:linkbutton id="FirstPage" runat="server" CssClass="pageLinks" Text="[First Page]" CommandName="First"
OnCommand="NavigationLink_Click">第一页</asp:linkbutton>
<asp:linkbutton id="PreviousPage" runat="server" CssClass="pageLinks" Text="[Previous Page]" CommandName="Prev"
OnCommand="NavigationLink_Click">上一页</asp:linkbutton>
<asp:linkbutton id="NextPage" runat="server" CssClass="pageLinks" Text="[Next Page]" CommandName="Next"
OnCommand="NavigationLink_Click">下一页</asp:linkbutton>
<asp:linkbutton id="LastPage" runat="server" CssClass="pageLinks" Text="[Last Page]" CommandName="Last"
OnCommand="NavigationLink_Click">末一页</asp:linkbutton></B>
</TD>
</tr>
</table>
</form>
</body>
</HTML>
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using DataBaseClass;
using System.Data.SqlClient;
namespace NetTest
{
/// <summary>
/// OrdersPage 的摘要说明。
/// </summary>
public class OrdersPage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label lbTotalPage;
protected System.Web.UI.WebControls.Label lbTotalCount;
protected System.Web.UI.WebControls.Label lbCurrentPage;
protected System.Web.UI.WebControls.DropDownList ddlPage;
protected System.Web.UI.WebControls.LinkButton FirstPage;
protected System.Web.UI.WebControls.LinkButton PreviousPage;
protected System.Web.UI.WebControls.LinkButton NextPage;
protected System.Web.UI.WebControls.DataGrid dbOrders;
protected System.Web.UI.WebControls.LinkButton LastPage;
private ClassDataGrid CDG=new ClassDataGrid();//数据访问层的类,定义的的一个实例
protected System.Web.UI.WebControls.Button btnGo;
protected System.Web.UI.WebControls.TextBox txtPage;
protected System.Web.UI.WebControls.ValidationSummary ValidationSummary1;
protected System.Web.UI.WebControls.RegularExpressionValidator RegularExpressionValidator1;
protected System.Web.UI.WebControls.Button btnDelete;
protected Int32 CurrentPageNumber = 1;//初始化,设置当前页为1,随时都要使用
private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
BindData();
btnDelete.Attributes ["onclick"]="javascript:return confirm('您确认要删除吗?');";
}
} #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.ddlPage.SelectedIndexChanged += new System.EventHandler(this.ddlPage_SelectedIndexChanged);
this.btnGo.Click += new System.EventHandler(this.btnGo_Click);
this.dbOrders.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dbOrders_ItemCommand);
this.dbOrders.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dbOrders_DeleteCommand);
this.dbOrders.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dbOrders_ItemDataBound);
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public Int32 GetCountOrders()
{
Int32 Tcount=Int32.Parse(CDG.GetOrderCount());//此处用到了数据访问层,得到总记录数,代码要自己写一下
return Tcount;
}
protected void NavigationLink_Click ( Object sender, CommandEventArgs e )//此事件为HTML代码里注册(事件为OnCommand)
{
switch ( e.CommandName )//根据设置的CommandName属性判断
{
case "First":
CurrentPageNumber = 1;
break;
case "Last":
CurrentPageNumber = Int32.Parse ( lbTotalPage.Text );
break;
case "Next":
CurrentPageNumber = Int32.Parse ( lbCurrentPage.Text ) + 1;
break;
case "Prev":
CurrentPageNumber = Int32.Parse ( lbCurrentPage.Text ) - 1;
break;
}
BindData();
}
public void BindData()
{
lbCurrentPage.Text = CurrentPageNumber.ToString();
int PageSize=int.Parse(dbOrders.PageSize.ToString());
//绑定DATAGRID,此处用到数据访问层,只要可以绑定就可以了(DataSet,DataTable都可以),这里的代码要自己写一下
SqlDataReader dr=CDG.GetOrders(PageSize,CurrentPageNumber);
dbOrders.DataSource=Tools.ConvertDataReaderToDataTable(dr);
dbOrders.DataBind(); Double TotalPages = 1;//初始化,总页数为1
Double ModePages=0;//取模余数(判断总页数是否要加1)
Int32 TotalRecords = GetCountOrders();//调用上面的得到总记录数的方法
TotalPages = TotalRecords / PageSize;//得到总页数(总记录数除以每页的记录数)
ModePages=TotalRecords%PageSize;//得到取模的余数(总记录数取模每页的记录数)
if(ModePages>0)//如果取模数不等于0,则把总页数加1
{
TotalPages+=1;
}
if(ModePages==0)//如果取模数等于0,不做任何事(也可以不用判断)
{
} lbTotalPage.Text = TotalPages.ToString();//显示页面上的总页数
lbTotalCount.Text=TotalRecords.ToString();//显示页面上的总记录数 //以下为判断点击的按钮(第一页,上一页,下一页,末一页)是否可以用
if ( CurrentPageNumber == 1 )
{
PreviousPage.Enabled = false;
FirstPage.Enabled=false;
if ( TotalPages > 1 )
{
NextPage.Enabled = true;
LastPage.Enabled=true;
}
else
{
NextPage.Enabled = false;
LastPage.Enabled=false;
}
}
else
{
PreviousPage.Enabled = true;
FirstPage.Enabled=true; if ( CurrentPageNumber == TotalPages )
{
NextPage.Enabled = false;
LastPage.Enabled=false;
}
else
{
NextPage.Enabled = true;
LastPage.Enabled=true;
}
} ddlPage.Items.Clear();//清楚跳转的页数(如果不清除,里面的记录将会循环增加)
int PCount=int.Parse(lbTotalPage.Text);//得到总页数,为了循环
for(int i=1;i<=PCount;i++)
{
ddlPage.Items.Add(i.ToString());
}
ddlPage.Items.FindByText(CurrentPageNumber.ToString()).Selected=true;//把当前页显示在列表框的第一个
}
{
int PageSize=int.Parse(ddlPage.SelectedValue.ToString());
int PageCount=Int32.Parse(lbTotalPage.Text.Trim().ToString());
if(PageSize<1)
{
CurrentPageNumber=1;
}
else if(PageSize>PageCount)
{
CurrentPageNumber=PageCount;
}
else
{
CurrentPageNumber=PageSize;
}
BindData();
} private void btnGo_Click(object sender, System.EventArgs e)
{
int PageSize=int.Parse(txtPage.Text.Trim().ToString());
int PageCount=Int32.Parse(lbTotalPage.Text.Trim().ToString());
if(PageSize<1)
{
CurrentPageNumber=1;
}
else if(PageSize>PageCount)
{
CurrentPageNumber=PageCount;
}
else
{
CurrentPageNumber=PageSize;
}
BindData();
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
try
{
string SelectedString = "";//定义一个变量
foreach(DataGridItem data in dbOrders.Items)
{
for(int i=0;i<data.Cells[0].Controls.Count;i++)//循环DataGrid的第一个单元格
{
if(data.Cells[0].Controls[i].GetType().ToString()=="System.Web.UI.WebControls.CheckBox")//判断类型是否为CheckBox
{
if(((CheckBox)data.Cells[0].Controls[i]).Checked==true)//如果CheckBox被选中
{
SelectedString += dbOrders.DataKeys[data.ItemIndex].ToString() + ",";
//因为设置了DataGrid的DataKeyField属性(编号),所以可以根据Datakeys得到选中CheckBox的那行所对应的那条记录的编号
//以逗号分隔,加入同一个字符串中
}
}
}
}
if(SelectedString!="")
{
bool result=true;
SelectedString = SelectedString.Substring(0,SelectedString.Length-1);//截取最后一个逗号
string[] Arr = SelectedString.Split(',');//分割字符串,放入数组中
for(int i=0;i<Arr.Length;i++)//循环数组
{
if(Arr[i].Trim()!="")//如果值不为空
{
//result=CDG.DeleteOrder(Int32.Parse(arrselected[i]));//删除一条记录
//这里的DeleteUser是我写的数据层的方法,返回的是bool,如果删除成功,返回true;
//此处的删除方法可以根据自己的需求写,所以这里就不写了
}
}
if(result)
{
Response.Write("<script>alert('用户删除成功')</script>");
}
}
BindData();
}
catch
{
Response.Write("<script>alert('过程出现错误,删除失败,请查找原因')</script>");
}
} private void dbOrders_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType==ListItemType.Item)//这是判断属性生成器里添加的删除,弹出一个删除对话框
{
if(e.Item.ItemType==ListItemType.Item)//这个删除的事件在DeleteCommand中执行
{
LinkButton btn=(LinkButton)e.Item.Cells[7].Controls[0];
btn.Attributes.Add ("onclick",
"return confirm (\"确定要删除此项记录吗?\");");
}
}
if(e.Item.ItemType==ListItemType.Item || e.Item.ItemType==ListItemType.AlternatingItem)//这是判断模板列中里添加的删除按钮(Button,ImageButton也可以),弹出一个删除对话框
{
Button button = (Button) e.Item.FindControl("btnADelete");//这个事件在ItemCommand事件中执行
//ImageButton button = (ImageButton) e.Item.FindControl("Imagebutton1");
button.Attributes.Add ("onclick",
"return confirm (\"确定要删除此项记录吗?\");");
}
} private void dbOrders_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
//写你的删除事件,下面是举例,具体根据自己的实际情况而定
int OrderID=int.Parse(dbOrders.DataKeys[e.Item.ItemIndex].ToString());//得到该行的对应的编号
// bool result=CDG.DeleteOrder(OrderID);
// if(result)
// {
// Response.Write("<script>alert('用户删除成功')</script>");
// }
} private void dbOrders_ItemCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
int OrderID=int.Parse(dbOrders.DataKeys[e.Item.ItemIndex].ToString());//得到该行的对应的编号
if(e.CommandName.Equals("Delete"))//此属性为设置的控件的CommandName,刚才设置的是Delete,表示删除(其他也可以)
{
//写你的删除事件,下面是举例,具体根据自己的实际情况而定
//bool result=CDG.DeleteOrder(OrderID);
// if(result)
// {
// Response.Write("<script>alert('用户删除成功')</script>");
// }
}
} }
}
你看看哪个完全符合你的要求的?再说你这是提问题吗?整个一个 “项目要求”。项目经理也不会提你这么苛刻的要求把。