ASP.NET调用Oracle存储过程实现快速分页

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;

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;

{  
this.style.display=’none’; Codehighlighter1_2319_2685_Open_Text.style.display=’none’; 
Codehighlighter1_2319_2685_Closed_Image.style.display=’inline’; 
Codehighlighter1_2319_2685_Closed_Text.style.display=’inline’;  
}" src="http://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif" 
align="top" />{  
this.style.display=’none’; Codehighlighter1_2319_2685_Closed_Text.style.display=’none’; 
Codehighlighter1_2319_2685_Open_Image.style.display=’inline’; 
Codehighlighter1_2319_2685_Open_Text.style.display=’inline’;  
}" src="http://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif" 
align="top" />/**//// <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)  
{  
this.style.display=’none’; Codehighlighter1_2824_4736_Open_Text.style.display=’none’; 
Codehighlighter1_2824_4736_Closed_Image.style.display=’inline’; 
Codehighlighter1_2824_4736_Closed_Text.style.display=’inline’;  
}" src="http://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif" 
align="top" />{  
this.style.display=’none’; Codehighlighter1_2824_4736_Closed_Text.style.display=’none’; 
Codehighlighter1_2824_4736_Open_Image.style.display=’inline’; 
Codehighlighter1_2824_4736_Open_Text.style.display=’inline’;  
}" src="http://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif" 
align="top" />{  
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];  
}