aspnetPager+oracle 存储过程分页

create or replace package DEZAICN_COMMON_PAGE is

  type type_cur is ref cursor; --定义游标变量用于返回记录集

  procedure COMMONPAGE(TABLES     VARCHAR2,
                       PK         VARCHAR2,
                       SORT       in out VARCHAR2,
                       PAGENUMBER in out NUMBER,
                       PAGESIZE   NUMBER,
                       FIELDS     VARCHAR2 := '*',
                       FILTER     VARCHAR2 := null,
                       --GROUPS VARCHAR2 :=null,
                       ISCOUNT NUMBER := 0,
                       v_cur   out type_cur);

end DEZAICN_COMMON_PAGE;
/
create or replace package body DEZAICN_COMMON_PAGE is

  procedure COMMONPAGE(TABLES     VARCHAR2,
                       PK         VARCHAR2,
                       SORT       in out VARCHAR2,
                       PAGENUMBER in out NUMBER,
                       PAGESIZE   NUMBER,
                       FIELDS     VARCHAR2 := '*',
                       FILTER     VARCHAR2 := null,
                       --GROUPS VARCHAR2 :=null,
                       ISCOUNT NUMBER := 0,
                       v_cur   out type_cur) as
    STRFILTER VARCHAR2(1000);
    STRSQL    VARCHAR2(1000);
    STARTID   NUMBER;
    ENDID     NUMBER;
  
  begin
    --
    --select * from GL_NEWS order by GN_Update_DATE DESC
    --exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
    --  TABLES:=TRIM(TABLES);
    -- FIELDS:=TRIM(FIELDS);
    IF FILTER IS NOT NULL THEN
      STRFILTER := ' Where ' || FILTER || '';
    ELSE
      STRFILTER := '';
    END IF;
    IF (ISCOUNT = 1) THEN
      STRSQL := 'Select COUNT(*) FROM ' || TABLES || ' ' || STRFILTER; --只获得记录条数
    ELSE
      -- 是否只统计记录数
    
      IF (SORT IS NULL) THEN
        SORT := PK || 'DESC';
      END IF;
      IF PAGENUMBER < 1 THEN
        PAGENUMBER := 1;
      END IF;
      IF PAGENUMBER = 1 THEN
        --第一页提高性能
        STRSQL := 'Select * FROM (Select ' || FIELDS || ' FROM ' || TABLES || ' ' ||
                  STRFILTER || ' orDER BY ' || SORT || ') Where ROWNUM <= ' ||
                  PAGESIZE;
      ELSE
        STARTID := TO_CHAR((PAGENUMBER - 1) * PAGESIZE + 1);
        ENDID   := TO_CHAR(PAGENUMBER * PAGESIZE);
        IF FIELDS = '*' THEN
        
          STRSQL := 'Select * FROM (Select ' || TABLES ||
                    '.*,ROWNUM ROWN FROM (Select * FROM ' || TABLES || ' ' ||
                    STRFILTER || ' orDER BY ' || SORT || ')' || TABLES ||
                    ' Where ROWNUM <= ' || ENDID || ')  D
       Where ROWN >= ' || STARTID;
        ELSE
          STRSQL := 'Select  ' || FIELDS || ' FROM (Select ' || FIELDS ||
                    ',ROWNUM ROWN FROM (Select * FROM ' || TABLES || ' ' ||
                    STRFILTER || ' orDER BY ' || SORT || ')' || TABLES ||
                    ' Where ROWNUM <= ' || ENDID || ')  D
       Where ROWN >= ' || STARTID;
        END IF;
      END IF;
    END IF;
    --EXECUTE IMMEDIATE STRSQL;
    OPEN v_cur FOR STRSQL;
  
  end COMMONPAGE;

end DEZAICN_COMMON_PAGE;
/

///

       /// 模糊查询
       ///

       ///
       ///
       ///
       ///
       ///
       ///
       ///
       ///
       ///
       ///
       ///
       public IList SEARCHLIST(string itemcode,int pageIndex, int pageSize)
       {
           oracleParameter[] parameters ={
             new oracleParameter("TABLES",OracleType.VarChar,255),
             new oracleParameter("PK",OracleType.VarChar,1000),
             new oracleParameter("SORT",OracleType.VarChar,255),
             new oracleParameter("PAGENUMBER",OracleType.Number),
             new oracleParameter("PAGESIZE",OracleType.Number),
             new oracleParameter("FILTER",OracleType.VarChar,2000),
             new oracleParameter("FIELDS",OracleType.VarChar,255),
             new oracleParameter("v_cur",OracleType.Cursor),
             new oracleParameter("ISCOUNT",OracleType.Number)};

           string sqlSelect = " 1=1 ";


           if (!string.IsNullOrEmpty(itemcode))
           {
               sqlSelect += " and ITEM_CODE='" + itemcode.ToString() + "'";
           }


        



           parameters[0].Value = "DEZAICN_OR_SHIP";// "test";
           parameters[1].Value = "transaction_id";//主键
           parameters[2].Value = "TRANSACTION_DATE DESC";// "id desc";//排序
           parameters[3].Value = pageIndex;//索引页
           parameters[4].Value = pageSize;//多少条为一页
           parameters[5].Value = sqlSelect;// strWhere;//查询条件组合
           parameters[6].Value = "*";// "id,name";//查询的字段
           parameters[7].Direction = ParameterDirection.Output;//输出
           parameters[8].Value = 0;

          


           try
           {
               IList Ilst = new List();

               using (OracleDataReader dr = oracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.StoredProcedure, "DEZAICN_COMMON_PAGE.COMMONPAGE", parameters))
               {
                   while (dr.Read())
                   {
                       Ilst.Add(POPULATER(dr));
                   }
               }
               return Ilst;
           }
           catch (OracleException e)
           {
               throw e;
           }
       }

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                string P = Request.QueryString["P"].ToString();

                string itemcode = string.Empty;

              
                if (string.IsNullOrEmpty(P) != true)
                {
                    string[] parameters = Request.QueryString["P"].ToString().Split(',');

                    for (int i = 0; i < parameters.Length; i++)
                    {
                        itemcode = parameters[0].Trim();


                    }

                    SDS.BLL.SSO.SHIP bllShip = new BLL.SSO.SHIP();

                    anpBottom.RecordCount = bllShip.SEARCHCOUNT(itemcode);


                }

              
            }

            ShowSearchList();
        }

SDS.BLL.SO.SHIP bllShip = new BLL.SSO.SHIP();

                gdvList.DataSource = bllShip.SEARCHLIST(itemcode, anpBottom.CurrentPageIndex, anpBottom.PageSize);

                gdvList.DataBind();

  protected void anpBottom_PageChanged(object sender, EventArgs e)
        {
            ShowSearchList();
        }

-- =============================================
-- Author:Dezai
-- Create date:2010-07-11 14:45:00
-- Description:查询分页集合(通用存储过程)
-- =============================================
Alter PROCEDURE [dbo].[SDS_COMMON_PAGE]
    (
        @tblName VARCHAR(255), -- 表名
        @strGetFields VARCHAR(1000) = '*', -- 需要返回的列
        @fldName VARCHAR(255)='', -- 排序的字段名
        @PageSize INT = 10, -- 页尺寸
        @PageIndex INT = 1, -- 页码
        @doCount BIT = 0, -- 返回记录总数, 非 0 值则返回
        @OrderType BIT = 0, -- 设置排序类型, 非 0 值则降序
        @strWhere VARCHAR(1500) = '' -- 查询条件 (注意: 不要加 Where)
    )
AS

    SET NOCOUNT ON

        DECLARE @strSQL VARCHAR(5000) -- 主语句
        DECLARE @strTmp VARCHAR(110) -- 临时变量
        DECLARE @strOrder VARCHAR(400) -- 排序类型


        IF(@doCount != 0)
            BEGIN
                IF @strWhere !=''
                    SET @strSQL = 'Select COUNT(1) AS Total FROM [' + @tblName + '] Where '+@strWhere
                ELSE
                    SET @strSQL = 'Select COUNT(1) AS Total FROM [' + @tblName + ']'
            END
            --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
        ELSE
            BEGIN
                IF @OrderType != 0
                    BEGIN
                        SET @strTmp = '<(Select MIN'
                        SET @strOrder = ' orDER BY [' + @fldName +'] DESC'
                        --如果@OrderType不是0，就执行降序，这句很重要
                    END
                ELSE
                    BEGIN
                        SET @strTmp = '>(Select MAX'
                        SET @strOrder = ' orDER BY [' + @fldName +'] ASC'
                    END

                IF @PageIndex = 1
                    BEGIN
                        IF @strWhere != ''
                            SET @strSQL = 'Select TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM [' + @tblName + '] Where ' + @strWhere + ' ' + @strOrder
                        ELSE
                            SET @strSQL = 'Select TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['+ @tblName + '] '+ @strOrder
                        --如果是第一页就执行以上代码，这样会加快执行速度
                    END
                ELSE
                    BEGIN
                        --以下代码赋予了@strSQL以真正执行的SQL代码
                        SET @strSQL = 'Select TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['
                        + @tblName + '] Where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) FROM (Select TOP ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] FROM [' + @tblName + ']' + @strOrder + ') AS tblTmp)'+ @strOrder
                        IF @strWhere != ''
                        SET @strSQL = 'Select TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['
                        + @tblName + '] Where [' + @fldName + ']' + @strTmp + '(['
                        + @fldName + ']) FROM (Select TOP ' + str((@PageIndex-1)*@PageSize) + ' ['
                        + @fldName + '] FROM [' + @tblName + '] Where ' + @strWhere + ' '
                        + @strOrder + ') AS tblTmp) AND ' + @strWhere + ' ' + @strOrder
                    END
            END

        EXEC (@strSQL)

    SET NOCOUNT OFF

   ///

       /// 收藏夹列表
       ///

       ///
       ///
       ///
       ///
       public IList FavList(int agentID,int pageIndex,int pageSize,string orderField)
       {
           SqlParameter[] parameters =
                {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@doCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar, 1500)
                };

           parameters[0].Value = "ITEM_FAVORIATE_V";
           parameters[1].Value = "*";
           parameters[2].Value = orderField;
           parameters[3].Value = pageSize;
           parameters[4].Value = pageIndex;
           parameters[5].Value = 0;
           parameters[6].Value = 1;
           parameters[7].Value = "ItemID is not null and Agent_ID=" + agentID.ToString().Trim() ;

           try
           {
               IList Ilst = new List();
               using (SqlDataReader dr = SqlHelper.ExecuteReader(SDS.DAL.SqlHelper.strPOP, CommandType.StoredProcedure, "SDS_COMMON_PAGE", parameters))
               {
                   while (dr.Read())
                   {
                       Ilst.Add(Populater(dr));
                   }
               }
               return Ilst;
           }
           catch (Exception e)
           {
               throw e;
           }
       }