推荐: 使用 ADO.NET 访问 Oracle 9i 存储过程的介绍
http://www.microsoft.com/china/MSDN/library/data/dataAccess/DMSDNorsps.mspx
ASP.NET调用oracle存储过程实现快速分页的步骤如下:
Oracle 9i 包定义:
create
or
replace package MaterialManage
is
TYPE T_CURSOR IS REF CURSOR;
Procedure Per_QuickPage
(
TbName in varchar2, -- 表名
FieldStr in varchar2, -- 字段集
RowFilter in varchar2, -- 过滤条件
SortStr in varchar2, -- 排序集
RownumFieldStr in varchar2, -- 分页条件
TotalCount out number, -- 总记录数
Cur_ReturnCur out T_CURSOR -- 返回的游标
);
end MaterialManage;
TYPE T_CURSOR IS REF CURSOR;
Procedure Per_QuickPage
(
TbName in varchar2, -- 表名
FieldStr in varchar2, -- 字段集
RowFilter in varchar2, -- 过滤条件
SortStr in varchar2, -- 排序集
RownumFieldStr in varchar2, -- 分页条件
TotalCount out number, -- 总记录数
Cur_ReturnCur out T_CURSOR -- 返回的游标
);
end MaterialManage;
Oracle 9i 包主体:
create
or
replace package body MaterialManage
is
Procedure Per_QuickPage
(
TbName in varchar2, -- 表 名
FieldStr in varchar2, -- 字段集
RowFilter in varchar2, -- 过滤条件
SortStr in varchar2, -- 排序集
MinRowNum in number, -- 分页小值
MaxRowNum in number, -- 分页大值
TotalCount out number, -- 总记录数
Cur_ReturnCur out T_CURSOR
)
is
v_SourceTb1 varchar2( 3000); -- 动态表名1
v_SourceTb2 varchar2( 3000); -- 动态表名2
v_SourceTb3 varchar2( 3000); -- 动态表名3
v_SourceTb4 varchar2( 3000); -- 动态表名4
v_TotalCount varchar2( 50); -- 总记录数
v_sql varchar2( 3000); -- 动态sql
begin
v_SourceTb1 : = ''( select '' || FieldStr || '' from '' || TbName || '') SourceTb1 '';
v_SourceTb2 : = ''( select * from '' || v_SourceTb1 || '' where '' || RowFilter || '' '' || SortStr || '') SourceTb2 '';
v_SourceTb3 : = ''( select rownum as Rowindex,SourceTb2. * from '' || v_SourceTb2 || '' where rownum <= '' || MaxRowNum || '') SourceTb3 '';
v_SourceTb4 : = ''( select * from '' || v_SourceTb1 || '' where '' || RowFilter || '') SourceTb4 '';
v_sql : = '' select count( *) as TotalCount from '' || v_SourceTb4;
execute immediate v_sql into v_TotalCount;
TotalCount : = v_TotalCount;
v_sql : = '' select * from '' || v_SourceTb3 || '' where RowIndex >= '' ||MinRowNum;
open Cur_ReturnCur for v_sql;
end Per_QuickPage;
END MaterialManage;
Procedure Per_QuickPage
(
TbName in varchar2, -- 表 名
FieldStr in varchar2, -- 字段集
RowFilter in varchar2, -- 过滤条件
SortStr in varchar2, -- 排序集
MinRowNum in number, -- 分页小值
MaxRowNum in number, -- 分页大值
TotalCount out number, -- 总记录数
Cur_ReturnCur out T_CURSOR
)
is
v_SourceTb1 varchar2( 3000); -- 动态表名1
v_SourceTb2 varchar2( 3000); -- 动态表名2
v_SourceTb3 varchar2( 3000); -- 动态表名3
v_SourceTb4 varchar2( 3000); -- 动态表名4
v_TotalCount varchar2( 50); -- 总记录数
v_sql varchar2( 3000); -- 动态sql
begin
v_SourceTb1 : = ''( select '' || FieldStr || '' from '' || TbName || '') SourceTb1 '';
v_SourceTb2 : = ''( select * from '' || v_SourceTb1 || '' where '' || RowFilter || '' '' || SortStr || '') SourceTb2 '';
v_SourceTb3 : = ''( select rownum as Rowindex,SourceTb2. * from '' || v_SourceTb2 || '' where rownum <= '' || MaxRowNum || '') SourceTb3 '';
v_SourceTb4 : = ''( select * from '' || v_SourceTb1 || '' where '' || RowFilter || '') SourceTb4 '';
v_sql : = '' select count( *) as TotalCount from '' || v_SourceTb4;
execute immediate v_sql into v_TotalCount;
TotalCount : = v_TotalCount;
v_sql : = '' select * from '' || v_SourceTb3 || '' where RowIndex >= '' ||MinRowNum;
open Cur_ReturnCur for v_sql;
end Per_QuickPage;
END MaterialManage;
由于oracle有个rownum特性,所以分页的时候就是利用rownum来实现。如果大家还有什么更好的办法记得告诉我一声,多谢了,因为我测试了上面的分页方法效率并不是很高。
存储过程返回了两个参数:TotalCount :当前条件下的总记录数 Cur_ReturnCur :游标类型,就是所要读取的记录的集合
下面是ASP.NET中调用的代码:
///
<summary>
/// 调用存储过程实现快速分页
/// </summary>
/// <param name="TbName"> 表名称 </param>
/// <param name="FieldStr"> 字段名称 </param>
/// <param name="RowFilter"> 过滤条件 </param>
/// <param name="SortStr"> 排序字段 </param>
/// <param name="MinPageNum"> 分页小值 </param>
/// <param name="MaxPageNum"> 分页大值 </param>
/// <param name="TotalCount"> 总记录(需要返回) </param>
/// <returns> DataTable </returns>
public DataTable QuickPage( string TbName, string FieldStr, string RowFilter, string SortStr, int MinRowNum, int MaxRowNum, ref int RecordCount)
{
OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings[ " OracleConnstr "].ToString());
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = " MaterialManage.Per_QuickPage ";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( " TbName ",OracleType.VarChar, 50); // 表 名
cmd.Parameters[ " TbName "].Direction = ParameterDirection.Input;
cmd.Parameters[ " TbName "].Value = TbName;
cmd.Parameters.Add( " FieldStr ",OracleType.VarChar, 3000); // 字段集
cmd.Parameters[ " FieldStr "].Direction = ParameterDirection.Input;
cmd.Parameters[ " FieldStr "].Value = FieldStr;
cmd.Parameters.Add( " RowFilter ",OracleType.VarChar, 3000); // 过滤条件
cmd.Parameters[ " RowFilter "].Direction = ParameterDirection.Input;
cmd.Parameters[ " RowFilter "].Value = RowFilter;
cmd.Parameters.Add( " SortStr ",OracleType.VarChar, 3000); // 排序字段
cmd.Parameters[ " SortStr "].Direction = ParameterDirection.Input;
cmd.Parameters[ " SortStr "].Value = SortStr;
cmd.Parameters.Add( " MinRowNum ",OracleType.Number); // 分页小值
cmd.Parameters[ " MinRowNum "].Direction = ParameterDirection.Input;
cmd.Parameters[ " MinRowNum "].Value = MinRowNum;
cmd.Parameters.Add( " MaxRowNum ",OracleType.Number); // 分页大值
cmd.Parameters[ " MaxRowNum "].Direction = ParameterDirection.Input;
cmd.Parameters[ " MaxRowNum "].Value = MaxRowNum;
cmd.Parameters.Add( " TotalCount ",OracleType.Number); // 页总记录数
cmd.Parameters[ " TotalCount "].Direction = ParameterDirection.Output;
cmd.Parameters[ " TotalCount "].Value = 0;
cmd.Parameters.Add( " Cur_ReturnCur ",OracleType.Cursor); // 返回的游标
cmd.Parameters[ " Cur_ReturnCur "].Direction = ParameterDirection.Output;
DataSet Ds = new DataSet();
OracleDataAdapter adapter= new OracleDataAdapter(cmd);
adapter.Fill(Ds);
conn.Close();
// 总记录数
RecordCount = int.Parse(cmd.Parameters[ " TotalCount "].Value.ToString());
return Ds.Tables[ 0];
}
/// 调用存储过程实现快速分页
/// </summary>
/// <param name="TbName"> 表名称 </param>
/// <param name="FieldStr"> 字段名称 </param>
/// <param name="RowFilter"> 过滤条件 </param>
/// <param name="SortStr"> 排序字段 </param>
/// <param name="MinPageNum"> 分页小值 </param>
/// <param name="MaxPageNum"> 分页大值 </param>
/// <param name="TotalCount"> 总记录(需要返回) </param>
/// <returns> DataTable </returns>
public DataTable QuickPage( string TbName, string FieldStr, string RowFilter, string SortStr, int MinRowNum, int MaxRowNum, ref int RecordCount)
{
OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings[ " OracleConnstr "].ToString());
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = " MaterialManage.Per_QuickPage ";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( " TbName ",OracleType.VarChar, 50); // 表 名
cmd.Parameters[ " TbName "].Direction = ParameterDirection.Input;
cmd.Parameters[ " TbName "].Value = TbName;
cmd.Parameters.Add( " FieldStr ",OracleType.VarChar, 3000); // 字段集
cmd.Parameters[ " FieldStr "].Direction = ParameterDirection.Input;
cmd.Parameters[ " FieldStr "].Value = FieldStr;
cmd.Parameters.Add( " RowFilter ",OracleType.VarChar, 3000); // 过滤条件
cmd.Parameters[ " RowFilter "].Direction = ParameterDirection.Input;
cmd.Parameters[ " RowFilter "].Value = RowFilter;
cmd.Parameters.Add( " SortStr ",OracleType.VarChar, 3000); // 排序字段
cmd.Parameters[ " SortStr "].Direction = ParameterDirection.Input;
cmd.Parameters[ " SortStr "].Value = SortStr;
cmd.Parameters.Add( " MinRowNum ",OracleType.Number); // 分页小值
cmd.Parameters[ " MinRowNum "].Direction = ParameterDirection.Input;
cmd.Parameters[ " MinRowNum "].Value = MinRowNum;
cmd.Parameters.Add( " MaxRowNum ",OracleType.Number); // 分页大值
cmd.Parameters[ " MaxRowNum "].Direction = ParameterDirection.Input;
cmd.Parameters[ " MaxRowNum "].Value = MaxRowNum;
cmd.Parameters.Add( " TotalCount ",OracleType.Number); // 页总记录数
cmd.Parameters[ " TotalCount "].Direction = ParameterDirection.Output;
cmd.Parameters[ " TotalCount "].Value = 0;
cmd.Parameters.Add( " Cur_ReturnCur ",OracleType.Cursor); // 返回的游标
cmd.Parameters[ " Cur_ReturnCur "].Direction = ParameterDirection.Output;
DataSet Ds = new DataSet();
OracleDataAdapter adapter= new OracleDataAdapter(cmd);
adapter.Fill(Ds);
conn.Close();
// 总记录数
RecordCount = int.Parse(cmd.Parameters[ " TotalCount "].Value.ToString());
return Ds.Tables[ 0];
}
v
本文转自钢钢博客园博客,原文链接:http://www.cnblogs.com/xugang/archive/2008/03/03/1088348.html,如需转载请自行联系原作者