ASP.NET GridView 使用Oracle Procedure 分頁

ASP.NET GridView 使用Oracle Procedure 分頁

1.創建Procedure(P_QUERYSPLIT)

 CREATE OR REPLACE PROCEDURE ZSHR.P_QUERYSPLIT(
 SQLSCRIPT IN VARCHAR2,PAGESIZE IN NUMBER,PAGEINDEX IN NUMBER,
 TOTALCOUNT OUT NUMBER,TOTALPAGE OUT NUMBER,V_CUR OUT SYS_REFCURSOR)IS
 v_PageIndex number;
 v_SQL_Count varchar2(4000);
 v_SQL varchar2(4000);
 v_StartIndex number :=1;
 v_EndIndex number :=1;

/******************************************************************************
   NAME:       P_QUERYSPLIT
   PURPOSE:   

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2009/3/27          1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     P_QUERYSPLIT
      Sysdate:         2009/3/27
      Date and Time:   2009/3/27, 上午 08:10:51, and 2009/3/27 上午 08:10:51
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
  v_SQL_Count := 'SELECT COUNT(*) FROM ('|| SQLSCRIPT ||') A ';
  EXECUTE IMMEDIATE v_SQL_Count INTO TOTALCOUNT;

  TOTALPAGE := CEIL(TOTALCOUNT/PAGESIZE);
  v_PageIndex := PAGEINDEX;
  IF v_PageIndex > TOTALPAGE THEN
     v_PageIndex := TOTALPAGE;
  END IF;

  v_StartIndex := (v_PageIndex - 1) * PAGESIZE + 1;
  v_EndIndex := v_PageIndex * PAGESIZE;

  v_SQL:= 'SELECT * FROM (';
  v_SQL:= v_SQL||' SELECT A.*, ROWNUM RN ';
  v_SQL:= v_SQL||' FROM ('|| SQLSCRIPT ||') A ';
  v_SQL:= v_SQL||' WHERE ROWNUM <= '|| v_EndIndex;
  v_SQL:= v_SQL||') WHERE RN >= '|| v_StartIndex;
 
  OPEN V_CUR for v_SQL;
  --DBMS_OUTPUT.PUT_LINE(v_SQL);
   EXCEPTION
     --WHEN NO_DATA_FOUND THEN
     --  NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       --RAISE;
       TOTALCOUNT := 0;
       TOTALPAGE := 0;
END P_QUERYSPLIT;

 

2.ASP.NET創建立方法,返回DataTable

        /// <summary>
        /// Oracle Procedure分頁,返回DataTable,總記錄筆數,總頁碼數
        /// </summary>
        /// <param name="strSQL">傳入Table or SQL</param>
        /// <param name="iPageSize">每頁顯示記錄數</param>
        /// <param name="iPageIndex">當前頁碼</param>
        /// <param name="iTotalCount">總記錄筆數</param>
        /// <param name="iTotalPage">總頁碼數</param>
        /// <returns>DataTable,總記錄筆數,總頁碼數</returns>
        public DataTable ExecuteDataTable(string strSQL,int iPageSize,int iPageIndex, out int iTotalCount,out int iTotalPage)
        {
            string spName = "P_QUERYSPLIT";
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(spName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            OracleParameter SQLSCRIPT_IN = new OracleParameter("SQLSCRIPT", OracleType.VarChar);
            SQLSCRIPT_IN.Direction = ParameterDirection.Input;
            SQLSCRIPT_IN.Value = strSQL;
            cmd.Parameters.Add(SQLSCRIPT_IN);

            OracleParameter PAGESIZE_IN = new OracleParameter("PAGESIZE", OracleType.Number);
            PAGESIZE_IN.Direction = ParameterDirection.Input;
            PAGESIZE_IN.Value = iPageSize;
            cmd.Parameters.Add(PAGESIZE_IN);

            OracleParameter PAGEINDEX_IN = new OracleParameter("PAGEINDEX", OracleType.Number);
            PAGEINDEX_IN.Direction = ParameterDirection.Input;
            PAGEINDEX_IN.Value = iPageIndex;
            cmd.Parameters.Add(PAGEINDEX_IN);

            OracleParameter TOTALCOUNT_OUT = new OracleParameter("TOTALCOUNT", OracleType.Number);
            TOTALCOUNT_OUT.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(TOTALCOUNT_OUT);

            OracleParameter TOTALPAGE_OUT = new OracleParameter("TOTALPAGE", OracleType.Number);
            TOTALPAGE_OUT.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(TOTALPAGE_OUT);

            OracleParameter V_CUR_OUT = new OracleParameter("V_CUR", OracleType.Cursor);
            V_CUR_OUT.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(V_CUR_OUT);

            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();

            try
            {
                conn.Open();
                da.Fill(dt);
            }
            catch (Exception e)
            {
                string msg = e.Message;
                Logs.Save("ExecuteDataTable() Error," + spName + "|" + msg);
            }
            finally
            {
                iTotalCount = int.Parse(TOTALCOUNT_OUT.Value.ToString());
                iTotalPage = int.Parse(TOTALPAGE_OUT.Value.ToString());

                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return dt;
        }

 

3.ASP.NET頁Bind到GrdiView並實現分頁  

    private int iTotalCount; //總記錄筆數
    private int iTotalPage; //總頁碼數
    private int iPageIndex; //當前頁碼
    private int iPageSize = 10; //每頁顯示記錄數

 

    protected void Button1_Click(object sender, EventArgs e)
    {
        bind();
    }
    protected void GridView1_DataBound(object sender, EventArgs e)
    {
        PageDropDownList.Items.Clear();
        for (int i = 0; i < iTotalPage; i++)
        {
            int pageNumber = i + 1;
            ListItem item = new ListItem(pageNumber.ToString());
            if (i == iPageIndex-1)
            {
                item.Selected = true;
            }
            PageDropDownList.Items.Add(item);
        }
       
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            int iID = (iPageIndex - 1) * iPageSize + i + 1;
            GridView1.Rows[i].Cells[0].Text = iID.ToString();
        }
    }
    protected void PageDropDownList_SelectedIndexChanged(object sender, EventArgs e)
    {
        bind();
    }

    private void bind()
    {
        DLL.DAL.DataAccess myGet = new DLL.DAL.DataAccess();
        DataTable dt = new DataTable();

        iPageIndex = PageDropDownList.SelectedIndex < 1 ? 1 : PageDropDownList.SelectedIndex+1;

        string strSQL = "SELECT * FROM TABLENAME ORDER BY ID DESC,SEQ ASC";
        dt = myGet.ExecuteDataTable(strSQL, iPageSize, iPageIndex, out iTotalCount, out iTotalPage);
        //Response.Write("[" + iTotalPage + " / " + iTotalCount + "]");
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }