[ExtNet] GridPanel如何实现服务器端分页、排序、查询?Oracel存储过程分页

[ExtNet] GridPanel怎么实现服务器端分页、排序、查询?--Oracel存储过程分页

A.前端aspx文件

    <ext:Store ID="StoreIma" runat="server" OnRefreshData="StoreIma_OnRefreshData">        <Reader>            <ext:JsonReader>                <Fields>                    <ext:RecordField Name="ID" Mapping="ID" Type="String" />                    <ext:RecordField Name="RANDOM_STRING" Mapping="RANDOM_STRING" Type="String" />                </Fields>            </ext:JsonReader>        </Reader>        <Proxy>            <ext:PageProxy>            </ext:PageProxy>        </Proxy>    </ext:Store>

    <ext:GridPanel ID="GpItem" runat="server" Width="1024" Height="500" StripeRows="true" StoreID="StoreIma">        <ColumnModel ID="ColumnModel1" runat="server">            <Columns>                <ext:Column Header="ID号" DataIndex="ID" Width="120">                </ext:Column>                <ext:Column Header="RANDOM_STRING" DataIndex="RANDOM_STRING" Width="320">                </ext:Column>            </Columns>        </ColumnModel>        <SelectionModel>            <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" />        </SelectionModel>        <LoadMask ShowMask="true" />        <BottomBar>            <ext:PagingToolbar ID="PagingToolBar1" StoreID="StoreIma" PageSize="100" runat="server" />        </BottomBar>    </ext:GridPanel>

protected void StoreIma_OnRefreshData(object sender, StoreRefreshDataEventArgs e)        {            int PageSize = this.PagingToolBar1.PageSize; //获取当前在页面中PagingToolBar 的PageSize的值            int Count = 0;            int CurPage = e.Start / PageSize + 1; //获取当前的页码是多少，也就是第几页            var store = this.GpItem.GetStore();            Model.PageBar page = new Model.PageBar();            page.Tables = "myTestTable";            page.Filter = "  1=1 ";            page.PrimaryKey = "ID";            page.Sort = "ASC";            page.CurrentPage = CurPage;            page.PageSize =PageSize;            page.Fields = "ID,RANDOM_STRING";            DataTable dt = GetListByPage(page); //连接数据库            e.Total = page.Count;            //this.PagingToolBar1.DisplayMsg = CurPage + " - " + PageSize + "页 共 " + e.Total + " 条";            if (page.Count > 0)            {                store.DataSource = dt;//绑定数据                store.DataBind();            }            else            {                X.Msg.Alert("查询结果", "<font style='color:red;'>没有找到任何数据!</font>").Show();            }        }        /// <summary>        /// 分页获取数据列表        /// </summary>        /// <param name="model">分页实体对象</param>        /// <returns>数据列表</returns>        public DataTable GetListByPage(Model.PageBar model)        {            OracleParameter[] parameters = {                    new OracleParameter("p_tableName", OracleType.NVarChar),//名字                    new OracleParameter("p_strWhere", OracleType.NVarChar),//查询条件(注意: 不要加 where)                    new OracleParameter("p_orderColumn", OracleType.NVarChar),//排序字段                    new OracleParameter("p_orderStyle", OracleType.NVarChar), //排序类型                    new OracleParameter("p_curPage", OracleType.Number), //当前第几页，页码                    new OracleParameter("p_pageSize", OracleType.Number),//每页显示数据条数                    new OracleParameter("p_fields", OracleType.NVarChar),//要显示的字段名(不要加select)                    new OracleParameter("p_totalRecords", OracleType.Number), //总记录数                    new OracleParameter("p_totalPages", OracleType.Number),//总页码                    new OracleParameter("v_cur",OracleType.Cursor)};            parameters[0].Direction = ParameterDirection.Input;            parameters[1].Direction = ParameterDirection.Input;            parameters[2].Direction = ParameterDirection.Input;            parameters[3].Direction = ParameterDirection.Input;            parameters[4].Direction = ParameterDirection.Input;            parameters[5].Direction = ParameterDirection.Input;            parameters[6].Direction = ParameterDirection.Input;            parameters[7].Direction = ParameterDirection.Output;            parameters[8].Direction = ParameterDirection.Output;            parameters[9].Direction = ParameterDirection.Output;            parameters[0].Value = model.Tables;            parameters[1].Value = model.Filter;            parameters[2].Value = model.PrimaryKey;            parameters[3].Value = model.Sort;            parameters[4].Value = model.CurrentPage;            parameters[5].Value = model.PageSize;            parameters[6].Value = model.Fields;            DataSet ds = new DataSet();            OracleConnection connection = new OracleConnection(OracleHelper.ConnectionStringERPTransaction);            OracleCommand cmd = new OracleCommand("PCK_System.USP_GetRecordByPage", connection);            cmd.CommandType = CommandType.StoredProcedure;            OracleDataAdapter adapter = new OracleDataAdapter(cmd);            connection.Open();            if (parameters != null && parameters.Length > 0)                adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());            OracleDataReader read = cmd.ExecuteReader(CommandBehavior.SchemaOnly);//ODP驱动时如果查询的字段有参数则会报ORA-01036: 非法的变量名/编号错误,先执行以下ExecuteReader可避免              read.Close();            read.Dispose();            connection.Close();            adapter.Fill(ds);                        //返回记录总数            model.Count = int.Parse(parameters[7].Value.ToString());            //返回结果集            return ds.Tables[0];        }

C.存储过程SQL

create or replace package PCK_System is  -- Author  : eb5mj  -- Created : 2011-1-26 11:31:28  -- Purpose : 系统包  type T_Curor is ref Cursor;  Procedure USP_GetRecordByPage  (      p_tableName        in  varchar2,   --表名      p_strWhere         in  varchar2,   --查询条件      p_orderColumn      in  varchar2,   --排序的列      p_orderStyle       in  varchar2,   --排序方式      p_curPage          in  Number,  --当前页      p_pageSize         in  Number,  --每页显示记录条数      p_fields           in  varchar2,   --要查询的列      p_totalRecords     out Number,     --总记录数      p_totalPages       out Number,     --总页数      v_cur              out T_Curor     --返回的结果集   );end PCK_System;create or replace package body PCK_System isPROCEDURE USP_GetRecordByPage       (p_tableName        in  varchar2,   --表名        p_strWhere         in  varchar2,   --查询条件        p_orderColumn      in  varchar2,   --排序的列        p_orderStyle       in  varchar2,   --排序方式        p_curPage          in  Number,     --当前页        p_pageSize         in  Number,     --每页显示记录条数        p_fields           in  varchar2,   --要查询的列        p_totalRecords     out Number,     --总记录数        p_totalPages       out Number,     --总页数        v_cur              out T_Curor)    --返回的结果集IS   v_sql VARCHAR2(1000) := '';      --sql语句   v_startRecord Number(4);         --开始显示的记录条数   v_endRecord Number(4);           --结束显示的记录条数BEGIN   --记录中总记录条数   v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' where ';   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN       v_sql := v_sql || p_strWhere;   END IF;   EXECUTE IMMEDIATE v_sql INTO p_totalRecords;/*   --验证页面记录大小   IF p_pageSize < 0 THEN       p_pageSize := 0;   END IF;*/   --根据页大小计算总页数   IF MOD(p_totalRecords,p_pageSize) = 0 THEN       /*p_totalPages := p_totalRecords / p_pageSize;*/       p_totalPages:=trunc(p_totalRecords,p_pageSize);   ELSE       /*p_totalPages := p_totalRecords / p_pageSize + 1;*/       p_totalPages:=trunc(p_totalRecords,p_pageSize)+1;   END IF;/*   --验证页号   IF p_curPage < 1 THEN       p_curPage := 1;   END IF;   IF p_curPage > p_totalPages THEN       p_curPage := p_totalPages;   END IF;*/   --实现分页查询   v_startRecord := (p_curPage - 1) * p_pageSize + 1;   v_endRecord := p_curPage * p_pageSize;   v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||            '(SELECT '|| p_fields ||' FROM ' || p_tableName;   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN       v_sql := v_sql || ' WHERE ' || p_strWhere;   END IF;   IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN       v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;   END IF;   v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '            || v_startRecord;   DBMS_OUTPUT.put_line(v_sql);   OPEN v_cur FOR v_sql;END USP_GetRecordByPage;   end PCK_System;

D.生成10W条数据
create table myTestTable as
select rownum as id,
               to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
               trunc(dbms_random.value(0, 100)) as random_id,
               dbms_random.string('x', 20) random_string
          from dual
        connect by level <= 100000;
E.Page

public class PageBar    {        private string _Tables;        public string Tables        {            get { return _Tables; }            set { _Tables = value; }        }        private string _Filter;        public string Filter        {            get { return _Filter; }            set { _Filter = value; }        }        private string _PrimaryKey;        public string PrimaryKey        {            get { return _PrimaryKey; }            set { _PrimaryKey = value; }        }        private string _Sort;        public string Sort        {            get { return _Sort; }            set { _Sort = value; }        }        private int _CurrentPage;        public int CurrentPage        {            get { return _CurrentPage; }            set { _CurrentPage = value; }        }        private int _PageSize;        public int PageSize        {            get { return _PageSize; }            set { _PageSize = value; }        }        private string _Fields;        public string Fields        {            get { return _Fields; }            set { _Fields = value; }        }        private int _Count;        public int Count        {            get { return _Count; }            set { _Count = value; }        }

F:结果

但是点击最后一页的时候 提示异常.，还请各位指教