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

红五月手抄报,搞笑的动态图片,空杯来酒

A.前端aspx文件

i.store定义 [html] 
<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: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> ii.Gridpanel定义
[html] 
<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> 

    <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>B.后端aspx.cs文件
[csharp] view plaincopyprint?
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]; 
        } 

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

[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 is 
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)    --返回的结果集  
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; 

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 is
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)    --返回的结果集
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

[csharp]
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; } 
        } 

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:结果

 

 

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

 

 

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复