微软企业库mysql分页存储_微软企业库调用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层业务逻辑(调用存储过程)

/// 分页存储过程

///

/// 表名

/// 字段名(全部字段为*)

/// 排序字段(必须!支持多字段)

/// 条件语句(不用加where)

/// 每页多少条记录

/// 指定当前为第几页

///

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();

}

分页控件样式：

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">

.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;        }