ASP.NET GridView Pagination using MySQL LIMIT

Question

I have a GridView that I associate at run time with a dataset through a query. I handle pagination using events PageIndexChangingand PageIndexChangedto set the GridView property of PageIndex to postback from the pager. Although this works correctly, the problem is the performance s> 7800 rows in this table. It should return the entire result set for each paging operation.

If I use the MySQL clause LIMIT offset,pagesize, I get the results at <50ms. My question is: how can I use the LIMIT clause in my query to speed up pagination? I somehow need to calculate the offset dynamically. I know the page size at runtime through GridView.PageSize.

The UPDATE . My new problem is that the GridView's grid controls have disappeared, apparently because only 10 records are returned from each request (PageSize = 10)

Thanks Mark

Answer 1

You also know the page number, right? Offset using pagenumber * pagesize.

Answer 2

, , 4 Rolla:

http://www.4guysfromrolla.com/webtech/082901-1.shtml#postadlink

Answer 3

, . . , ( SQL Server , MySQl): http://www.nikhedonia.com/notebook/entry/efficient-paging-for-gridview/

Answer 4

ObjectDataSource (http://msdn.microsoft.com/en-us/library/9a4kyhcx%28v=vs .90%29.aspx) , , , , .

ObjectDataSource :

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
  OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
  SelectMethod="GetProductsPaged" EnablePaging="True"
  SelectCountMethod="TotalNumberOfProducts">
</asp:ObjectDataSource>

Name - , GetProductsPaged TotalNumberOfProducts. ProductsBLL :

public class ProductsBLL
{
   public int TotalNumberOfProducts()
   {
    int recordCount = 0;
    if (databaseObj.OpenDatabase())
    {
       recordCount = databaseObj.DBTotalNumberOfProducts(searchQuery);
       databaseObj.CloseDatabase();
    }
    return recordCount;
  }

  public DataTable GetProductsPaged(int startRowIndex, int maximumRows)
  {
    DataTable pagedDataDV = null;
    if (databaseObj.OpenDatabase())
    {
       pagedDataDV = databaseObj.DBGetProductsPaged(searchQuery, startRowIndex, maximumRows);
       databaseObj.CloseDatabase();
    }
    return pagedDataDV;
  }
}

DBTotalNumberOfProducts . DBGetProductsPaged MySQL LIMIT. LIMIT startRowIndex maximumRows. maximumRows , .

, :

GridData.DataSourceID = "ObjectDataSource1";
GridData.DataBind();

ObjectDataSource: http://msdn.microsoft.com/en-us/library/bb445504.aspx