ASP.NET GridView Pagination using MySQL LIMIT

I have a GridView which I bind at runtime to a dataset via a query. I handle the pagination using the PageIndexChanging and PageIndexChanged events to set the GridView's PageIndex property on postback from the pager. While this functions correctly, the problem is the performance with >7800 rows in this table. It has to return the entire result set for every paging operation.开发者_JS百科

If I use the MySQL LIMIT offset,pagesize clause, I get results in <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 via GridView.PageSize.

UPDATE: My new problem is that the GridView pagination controls disappeared, presumably because only 10 records return from each query (PageSize=10)

Thanks, Mark

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

You will have to manually turn on the paging links if you are filtering the result within your query, here is a good article from the 4 guys at Rolla:

https://web.archive.org/web/20210309192630/http://www.4guysfromrolla.com/webtech/082901-1.shtml#postadlink

To fix your pager, you'll have to create it manually. See this article which attempts the same (it uses SQL Server for DB but you can implement the ideas in MySQl as well): http://www.nikhedonia.com/notebook/entry/efficient-paging-for-gridview/

The ObjectDataSource control (http://msdn.microsoft.com/en-us/library/9a4kyhcx%28v=vs.90%29.aspx) is a mechanism for custom paging and can handle paging through large amount of data. Custom paging ensures that only the precise set of records needed for a particular page of data is retrieved from the database at a time.

The usage of the ObjectDataSource control is as follows:

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

The TypeName is the name of the class which contains the GetProductsPaged and TotalNumberOfProducts functions. A possible representation of the ProductsBLL class is as follows:

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;
  }
}

The function DBTotalNumberOfProducts gets the total number of rows present for the given query. The function DBGetProductsPaged obtains the data from the database for the query using MySQL LIMIT keyword. The LIMIT makes use of the values in the startRowIndex and the maximumRows variables. The value of the maximumRows will be a constant and should be the same as the page size of the grid.

The data can be bound to the grid control by making use of the following code:

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

The following site will give more information on the ObjectDataSource control: http://msdn.microsoft.com/en-us/library/bb445504.aspx