存储过程： CREATEORREPLACEPACKAGEpkg_tableType IS procedureFY ( TableNamevarchar2,--表名 getFieldsvarchar2,--字段名(全部字段为*) OrderFieldvarchar2,--排序字段(必须!支持多字段) whereConditionvarchar2,--条件语句(不用加where) pageSizeint,--每页多少条记录 pageIndexint,--指定当前为第几页 recordCountoutint,--返回总记录条数 cur_outoutsys_refcursor--返回的是数据集 ); END; -------------创建包体-------------------- CREATEORREPLACEPACKAGEbodypkg_tableType IS procedureFY( TableNamevarchar2,--表名 getFieldsvarchar2,--字段名(全部字段为*) OrderFieldvarchar2,--排序字段(必须!支持多字段) whereConditionvarchar2,--条件语句(不用加where) pageSizeint,--每页多少条记录 pageIndexint,--指定当前为第几页 recordCountoutint,--返回总记录条数 cur_outoutsys_refcursor--返回的是数据集 ) i s v_sqlvarchar2(500); StartRecordint; EndRecordint; sPageIndexint; pageCountint; begin ifwhereConditionisnullthen v_sql:='selectcount(*)from'||TableName; else v_sql:='selectcount(*)from'||TableName||'where'||whereCondition; endif; executeimmediatev_sqlintorecordCount;--计算总记录数 pageCount:=CEIL((recordCount||0.0)/PageSize);--计算总页数 sPageIndex:=pageIndex; --处理开始点和结束点 if(pageIndex<=0)then sPageIndex:=1; elsif(pageIndex>pageCount)then sPageIndex:=pageCount; endif; StartRecord:=(sPageIndex-1)*PageSize+1; EndRecord:=StartRecord+pageSize-1; --合成sql ifwhereConditionisnotnullthen v_sql:='select*from(selectrow_number()over(orderby'||OrderField||')asrn,d.*from' ||TableName||'dwhere'||+whereCondition||')wherern>'||StartRecord||'andrn<='||EndRecord; else v_sql:='select*from(selectrow_number()over(orderby'||OrderField||')asrn,d.*from' ||TableName||'d)wherern>'||StartRecord||'andrn<='||EndRecord; endif; opencur_outforv_sql; endFY; 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>
