locked
Question about using DataTable to populate GridView RRS feed

  • 問題

  • 你好

    我用開data table 去populate asp.net 既grid view 的
    用datatable 去populate grid view
    要自己 handle 個paging 問題
    please correct me if i am wrong.
    下面係我handle paging果d code

    protected void gv_Example_PageIndexChanging(object sender,

    Code Snippet

    GridViewPageEventArgs e)
        {
            gv_Example.PageIndex = e.NewPageIndex;
            ExampleHelper exampleInfo = new ExampleHelper();
            gv_Example.DataSource = exampleInfo.GetListofRecords((string)

    Session["Client"]); //呢個 method 用run 個sql query 去 retrive 個data 的
            gv_Example.DataBind();
        }



    每一次d user click 去另外一個page 時
    呢個method 都要retrive 過d data
    我想知有無方法可以係asp.net 度 keep 住個datatable
    等佢 choose 另外d page from the same grid view 時唔駛 call 多次果個method 去 database 度由頭retrieve 過d data

    我唔想用object data source.

    唔知有無d 好d 既方法可以令到個web page more efficient 呢?

    謝謝大家
    2008年6月9日 上午 11:09

解答

  •  

    其實,從SQL2005開始,SQL Query 係support 取得某個paging中的data,例如,個Table有五萬隻records,而你個grid view只係想每次display十隻,你都唔想個dataTable一次過取得五萬隻data再去只display其中十隻,所以你要改個sql query,令個query只係return 果第幾頁的果十隻record.

     

    下面是其中一個stored Procedure的例子。

    Code Snippet
    DECLARE @PageNum AS INT;
    DECLARE @PageSize AS INT;
    SET @PageNum = 2;
    SET @PageSize = 10;
     
    WITH OrdersRN AS
    (    
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
              ,OrderID
              ,OrderDate
              ,CustomerID
              ,EmployeeID
    FROM dbo.Orders
    )
     
    SELECT *   
    FROM OrdersRN 
    WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
                       AND @PageNum * @PageSize 
    ORDER BY OrderDate,OrderID;

     

     

     
    2008年6月10日 上午 02:34
  • Please go to the following link to get the Custom Paging in GridView.

     

    http://www.codeproject.com/KB/webforms/GridViewCustomPaging.aspx

    2008年6月10日 上午 10:15

所有回覆

  •  

    其實,從SQL2005開始,SQL Query 係support 取得某個paging中的data,例如,個Table有五萬隻records,而你個grid view只係想每次display十隻,你都唔想個dataTable一次過取得五萬隻data再去只display其中十隻,所以你要改個sql query,令個query只係return 果第幾頁的果十隻record.

     

    下面是其中一個stored Procedure的例子。

    Code Snippet
    DECLARE @PageNum AS INT;
    DECLARE @PageSize AS INT;
    SET @PageNum = 2;
    SET @PageSize = 10;
     
    WITH OrdersRN AS
    (    
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
              ,OrderID
              ,OrderDate
              ,CustomerID
              ,EmployeeID
    FROM dbo.Orders
    )
     
    SELECT *   
    FROM OrdersRN 
    WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
                       AND @PageNum * @PageSize 
    ORDER BY OrderDate,OrderID;

     

     

     
    2008年6月10日 上午 02:34
  • Hi Ken

    Thanks for your information, 好可惜我仲係用緊 sql 2000
    要等多幾個月到一年先會用sql 2008 因為 upgrade 要d 時間去做testing

    傳統用grid view 佢會自動display 佢total 有幾多個page 的
    之後d user 就click 去個page 度佢就自己轉la
    如果我take 佢個subset (10 個一頁) 我可以點做先可以令到個grid view display 另外果幾個page 係個grid view 底部呢?

    我要唔要寫多個query 去 自己寫返d page number 出o黎

    e.g. return 個page number 之後自己計返有幾多個page

    唔可意思有好多問題問

    Thanks very much for your help.
    2008年6月10日 上午 08:03
  • Well, from the following link, you may see that you only need to update the index and re-bind the datasource to the gridview, and the gridview could display the right Paging data. But as I said, you better to limited the result in a fixed number, for performance issue.

     

    http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.pageindexchanging.aspx

     

    2008年6月10日 上午 09:16
  • Hi Ken

    thanks for your information, 我明白個performance issue, 所以我都用你suggest 既方法 用query 去return subset 既data 出o黎.
    但當我要做返d paging 出o黎比 user click 落去 <--- 我唔係咁清楚呢一part 應該點做. 有無方法可以做返 bind 個total number record  as int 只係去個 grid view paging 度?
    or 我要自己寫個drop down list 去 計呢?

    thanks for your help,

    2008年6月10日 上午 09:31
  • Please go to the following link to get the Custom Paging in GridView.

     

    http://www.codeproject.com/KB/webforms/GridViewCustomPaging.aspx

    2008年6月10日 上午 10:15
  • Hi Ken

    Thanks very much for your information, When I have any more question, i will post again.

    Thansk very much for your help.
    2008年6月10日 上午 10:57