本文介绍了使用ASP.NET Datagrid控件进行数据展示的方法，并详细阐述了在Oracle10g和SQLServer2005中实现自动分页的具体步骤与SQL语句。此外，还提供了一个SQL Server 2005的存储过程示例，用于实现自动分页查询。
datagrid模板及Oracle10g和SQLServer2005的分页方法
<DIV id="Div1" style="OVERFLOW: auto; WIDTH: 965px; HEIGHT: 480px">
<asp:datagrid id="myDataGrid" Width="100%" runat="server" CssClass="xpTable" EnableViewState="true"
BorderWidth="0px" GridLines="Both" CellPadding="1" CellSpacing="1" BackColor="White" BorderColor="#79A2DD"
AllowPaging="True" AllowCustomPaging="True" PagerStyle-Visible="False" DataKeyField="operationdata"
OnItemCommand="SelectItemCommand" AutoGenerateColumns="False" BorderStyle="None">
<SelectedItemStyle Wrap="False" ForeColor="#f0f0f0" BackColor="#79A2DD"></SelectedItemStyle>
<EditItemStyle Wrap="False"></EditItemStyle>
<AlternatingItemStyle Wrap="False" BackColor="White"></AlternatingItemStyle>
<ItemStyle Wrap="False" CssClass="MTGridText"></ItemStyle>
<HeaderStyle Wrap="False" HorizontalAlign="Left" Height="22px" BorderWidth="0px" CssClass="MTGridTitle"></HeaderStyle>
<FooterStyle Wrap="False" HorizontalAlign="Left" Height="22px" BorderWidth="0px" ForeColor="Black"
BorderStyle="None" VerticalAlign="Middle"></FooterStyle>
<Columns>
<asp:BoundColumn HeaderText="序号" Visible="False" DataField="序号" SortExpression="序号">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="项目编号" DataField="programerid" Visible="False" SortExpression="programerid">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:ButtonColumn HeaderText="业务操作" ItemStyle-HorizontalAlign="Center" ButtonType="LinkButton" Visible="true"
Text="<img border=0 alt='现在办理' src='../images/dot3.gif' style='BORDER-RIGHT:#F0F0F0 1px solid;BORDER-TOP:#F0F0F0 1px solid;BORDER-LEFT:#F0F0F0 1px solid;CURSOR:hand;BORDER-BOTTOM:#F0F0F0 1px solid' onmouseover='mover2(this);' onmouseout='mout2(this);' onmousedown='mdown(this);' onmouseup='mup(this);'>"
CommandName="AddToCart">
<HeaderStyle Wrap="False" Width="6%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:ButtonColumn>
<asp:BoundColumn HeaderText="项目/批次名称" DataField="programername" SortExpression="programername">
<HeaderStyle Wrap="False" Width="10%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="业务代码" DataField="typeid" Visible="False" SortExpression="typeid">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="项目重要程度" DataField="xmzycd" Visible="true">
<HeaderStyle Wrap="False" Width="5%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="项目办理状态" Visible="true" DataField="mqzt">
<HeaderStyle Wrap="False" Width="5%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="科室编号" DataField="officeid" Visible="False" SortExpression="officeid">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="办理科室" DataField="officename" SortExpression="officename">
<HeaderStyle Wrap="False" Width="8%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="办文编号" DataField="operationdata" Visible="False" SortExpression="operationdata">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="节点编号" DataField="siteid" Visible="false" SortExpression="siteid">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="当前节点名称" DataField="sitename" Visible="true" SortExpression="sitename">
<HeaderStyle Wrap="False" Width="10%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="操作人员" DataField="username" Visible="False" SortExpression="username">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="受理时间" DataField="starttime" Visible="true" SortExpression="starttime">
<HeaderStyle Wrap="False" Width="10%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="提交时间" DataField="endtime" Visible="true" SortExpression="endtime">
<HeaderStyle Wrap="False" Width="10%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="提示信息" DataField="warning" Visible="true" SortExpression="warning">
<HeaderStyle Wrap="False" Width="10%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="操作节点" DataField="operator_id" Visible="False" SortExpression="operator_id">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:ButtonColumn HeaderText="项目查询" Visible="False" ButtonType="LinkButton" Text="查询" CommandName="AddToCart">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:ButtonColumn>
<asp:BoundColumn HeaderText="流程编号" DataField="flowid" Visible="False">
<HeaderStyle Wrap="False" Width="1%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
<asp:BoundColumn HeaderText="业务类别" DataField="typename" Visible="True" SortExpression="typename">
<HeaderStyle Wrap="False" Width="10%"></HeaderStyle>
<ItemStyle Wrap="False"></ItemStyle>
<FooterStyle Wrap="False"></FooterStyle>
</asp:BoundColumn>
</Columns>
<PagerStyle NextPageText="" BackColor="#ffffff" PrevPageText=""></PagerStyle>
</asp:datagrid>
</DIV>
(数据绑定方法)
OracleDataAdapter da=new OracleDataAdapter(mycmd);
DataSet ds=new DataSet();
da.Fill(ds);
myDataGrid.DataSource=ds.Tables[0];
myDataGrid.DataBind();
ds=null;
(数据库中查询自动分页机制)Oracle10g
procedure getquerydata(
CurrentPage in number,
PageSize in number,
--TotalPage in number,
QuerySQL in varchar2,
OutRS out BAG_KD_QUERY.t_rs_query
)
as
--T_TotalPage number :=0;
T_str varchar2(3000):='';
FirstRec int;
LastRec int;
begin
--计算显示页码的首条记录号与最后一条记录号
FirstRec:=(CurrentPage -1)*PageSize+1;
LastRec:=CurrentPage*PageSize;
--设置要查询的sql语句
--T_str:='select * from(select rownum as kd_rownum_id,kd_query_table.* from ('|| QuerySQL ||') kd_query_table) where kd_rownum_id>='||FirstRec||' and kd_rownum_id<='||LastRec||'';
T_str:='select * from(select rownum as 序号,kd_query_table.* from ('|| QuerySQL ||') kd_query_table) where 序号>='||FirstRec||' and 序号<='||LastRec||'';
--open cursor return recordset
open outRs for T_str;
end getquerydata ;
(数据库中查询自动分页机制)SQLServer2005
我们可以使用row_number函数来实现查询表中指定范围的记录，
一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询t_table表中第2条和第3条记录：
with t_rowtable
as
(
select row_number() over(order by field1) as row_number,* from t_table
)
select * from t_rowtable where row_number>1 and row_number < 4 order by field1
-------------------------------------------------------------------------------------------
当然，不使用row_number函数也可以实现查询指定范围的记录，就是比较麻烦。
一般的方法是使用颠倒Top来实现，例如，查询t_table表中第2条和第3条记录，可以先查出前3条记录，
然后将查询出来的这三条记录按倒序排序，再取前2条记录，最后再将查出来的这2条记录再按倒序排序，
就是最终结果。SQL语句如下：
select * from (select top 2 * from( select top 3 * from t_table order by field1) a order by field1 desc) b order by field1
----SQLServer2005分页一例：
SELECT *
FROM (SELECT row_number() OVER (ORDER BY jqmj) AS row_number, *
FROM (SELECT *
FROM REPORT_GHJSYD
WHERE gh_layername = 'MZXM') AS xp_query_data1) AS xp_query_data2
WHERE row_number > 1 AND row_number < 19
ORDER BY jqmj
----创建存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <hsg>
-- Create date: <2009-07-07>
-- Description: <自动分页查询的存储过程>
-- =============================================
ALTER PROCEDURE [dbo].[sp_xp_sqlquerydata]
@CurrentPage int,
@PageSize int,
--@TotalPage int,
@QuerySQL varchar(max),
@OrderByField varchar(max)
AS
BEGIN
declare @QuerySQL_Text as varchar(max),
@FirstRec int,
@LastRec int;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--SELECT *
--FROM REPORT_GHJSYD
--WHERE gh_layername = 'MZXM'
--计算当前分页的第一行记录数和最后一行记录数
set @FirstRec=(@CurrentPage -1)*@PageSize+1;
set @LastRec=@CurrentPage*@PageSize;
-- Insert statements for procedure here
set @QuerySQL_Text='SELECT *
FROM
( SELECT row_number() OVER(ORDER BY '+@OrderByField+') AS row_number, *
FROM
(
'+@QuerySQL+'
) AS xp_query_data1
) AS xp_query_data2
WHERE row_number >='+convert(varchar,@FirstRec)+' AND row_number <='+convert(varchar,@LastRec)+'
ORDER BY '+@OrderByField+' '
--执行组合的SQL语句
select @QuerySQL_Text;
--sp_sqlexec @QuerySQL_Text
declare @execstr nvarchar(max);
set @execstr = CONVERT(nvarchar(max),@QuerySQL_Text);
exec(@execstr);
END
----执行存储过程
USE [GHXB]
GO
DECLARE @return_value int
declare @sqlstr varchar(max)
declare @orderbyfield varchar(max)
set @orderbyfield='id';
set @sqlstr='select * from report_c10';
EXEC @return_value = dbo.sp_xp_sqlquerydata 3,10,@sqlstr,@orderbyfield
SELECT 'Return Value' = @return_value
GO
扫一扫
90
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
