微软企业库调用Oracle分页存储过程

存储过程：

 

CREATE OR REPLACE PACKAGE pkg_tableType

IS

procedure FY

(

TableName varchar2, -- 表名

getFields varchar2, -- 字段名(全部字段为*)

OrderField varchar2, -- 排序字段(必须!支持多字段)

whereCondition varchar2, -- 条件语句(不用加where)

pageSize int, -- 每页多少条记录

pageIndex int, -- 指定当前为第几页

recordCount out int , --返回总记录条数

cur_out out sys_refcursor --返回的是数据集 );

END; -------------创建包体--------------------

CREATE OR REPLACE PACKAGE body pkg_tableType

IS

procedure FY(

TableName varchar2, -- 表名

getFields varchar2, -- 字段名(全部字段为*)

OrderField varchar2, -- 排序字段(必须!支持多字段)

whereCondition varchar2, -- 条件语句(不用加where)

pageSize int, -- 每页多少条记录

pageIndex int, -- 指定当前为第几页

recordCount out int , --返回总记录条数

cur_out out sys_refcursor --返回的是数据集

) i

s

v_sql varchar2(500);

StartRecord int;

EndRecord int;

sPageIndex int;

pageCount int;

begin

  if whereCondition is null then  

 v_sql := 'select count(*) from ' || TableName;  

 else   v_sql := 'select count(*) from ' || TableName ||' where ' || whereCondition;

  end if;  

 execute immediate v_sql into recordCount; --计算总记录数  

 pageCount:=CEIL((recordCount||0.0)/PageSize); --计算总页数   

sPageIndex:=pageIndex;   --处理开始点和结束点

 if(pageIndex<=0)then   sPageIndex:=1;

  elsif(pageIndex>pageCount)then   sPageIndex:=pageCount;

  end if;   

StartRecord := (sPageIndex-1)*PageSize + 1;  

 EndRecord := StartRecord +pageSize - 1;   --合成sql  

 if whereCondition is not null then     

v_sql:='select * from (select row_number() over (order by '||OrderField||' ) as rn,d.* from '     ||TableName||' d where '||+whereCondition||') where rn>'||StartRecord||' and rn<='||EndRecord;   else     v_sql:='select * from (select row_number() over (order by '||OrderField||' ) as rn,d.* from '     ||TableName||' d) where rn>'||StartRecord||' and rn<='||EndRecord;   end if;     open cur_out for v_sql;   end FY; END ;

 

DAL层业务逻辑（调用存储过程）

/// 分页存储过程      

   /// </summary>        

 /// <param name="TableName">表名</param>   

      /// <param name="getFields">字段名(全部字段为*)</param>  

       /// <param name="OrderField">排序字段(必须!支持多字段)</param>    

     /// <param name="whereCondition">条件语句(不用加where)</param>   

      /// <param name="pageSize">每页多少条记录</param>        

 /// <param name="pageIndex">指定当前为第几页</param>    

     /// <returns></returns>      

   public static DataSet GetProductsByPagerProc(string TableName, string getFields, string OrderField, string whereCondition, int pageSize, int pageIndex, out int recordCount)   

      {           

            Database db = DBHelper.CreateDataBase();   

            StringBuilder sb = new StringBuilder();

            sb.Append("Shop_Product_Query.Pagination");

            DbCommand cmd = db.GetStoredProcCommand(sb.ToString());

            db.AddInParameter(cmd, "TableName", DbType.String, TableName);//输入参数

            db.AddInParameter(cmd, "getFields", DbType.String, getFields);//输入参数

            db.AddInParameter(cmd, "OrderField", DbType.String, OrderField);//输入参数

            db.AddInParameter(cmd, "whereCondition", DbType.String, whereCondition);//输入参数

            db.AddInParameter(cmd, "pageSize", DbType.Int32, pageSize);//输入参数

            db.AddInParameter(cmd, "pageIndex", DbType.Int32, pageIndex);//输入参数

            db.AddOutParameter(cmd, "recordCount", DbType.Int32, Int32.MaxValue);//输出参数

            DataSet ds = db.ExecuteDataSet(cmd);

            recordCount = int.Parse(db.GetParameterValue(cmd, "recordCount").ToString());//企业库获取输出参数

            return ds;         } 

 

页面层调用DAL：

public void DataBind()

        {

             int intPageIndex = this.AspNetPager1.CurrentPageIndex;

            int intPageSize = this.AspNetPager1.PageSize;

            int count = 0;

            DataSet dt = Sys_ParamService.GetProductsByPagerProc("products", "PRODUCTCODE,PRODUCTNAME,HAOBAIPRICE", "productcode", "productname like '%蒙牛%'",

                                                    intPageSize, intPageIndex, out count);

            this.AspNetPager1.RecordCount = count;

            GridView1.DataSource = dt;

            GridView1.DataBind();         }

        //分页事件

        protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)         {

            this.AspNetPager1.CurrentPageIndex = e.NewPageIndex;

            DataBind();

        }

 

分页控件样式：

<table cellpadding="0" cellspacing="0" align="left" width="99%" class="border">

            <tr>

                <td align="left">

                    <webdiyer:AspNetPager ID="AspNetPager1" CssClass="paginator" CurrentPageButtonClass="cpb"

                        runat="server" AlwaysShow="True" FirstPageText="首页" LastPageText="尾页" NextPageText="下一页"                         PageSize="3" PrevPageText="上一页" ShowCustomInfoSection="Left" CustomInfoTextAlign="Center"                         LayoutType="Table" CustomInfoHTML="当前第%CurrentPageIndex%/%PageCount%页 每页%PageSize%条 共%RecordCount%条记录"                         OnPageChanging="AspNetPager1_PageChanging" PageIndexBoxType="DropDownList" ShowPageIndexBox="Always">                     </webdiyer:AspNetPager>                 </td>             </tr>         </table>

 

<style type="text/css">

            .paginator         {

            font: 12px Arial, Helvetica, sans-serif;

            padding: 10px 20px 10px 0;

            margin: 0px;         }

        .paginator a         {             border: solid 1px #ccc;             color: #0063dc;             cursor: pointer;             text-decoration: none;         }         .paginator a:visited         {             padding: 1px 6px;             border: solid 1px #ddd;             background: #fff;             text-decoration: none;         }         .paginator .cpb         {             border: 1px solid #F50;             font-weight: 700;             color: #F50;             background-color: #ffeee5;         }         .paginator a:hover         {             border: solid 1px #F50;             color: #f60;             text-decoration: none;         }         .paginator a, .paginator a:visited, .paginator .cpb, .paginator a:hover         {             float: left;             height: 16px;             line-height: 16px;             min-width: 10px;             _width: 10px;             margin-right: 5px;             text-align: center;             white-space: nowrap;             font-size: 12px;             font-family: Arial,SimSun;             padding: 0 3px;         }     </style>