Paging large amounts of Data in a Gridview

Question

Ok, please bear with me as I can be a bit of a wood duck at times...

I have a gridview in asp.net that will be pulling back many thousand of records. This is all well and good apart from the performance aspect of things. I am binding my Gridview to a dataset and this pulls back every record in the query. I want to change this so that the gridview only pulls back the records that it is currently displaying and then when the user moves to the next page it goes and gets the next chuck of data etc.

Below is how I normally bind my gridviews and handle the paging and sorting, which works very well for me with small data amounts, but not so good for large data amounts. I use SubSonic as my DAL, which is cool. Can anyone point me in the right direction on how best to achieve paging as described above?

Thanks in advance...

public SortDirection SortDir
{
    get
    {
        if (ViewState["sortDirection"] == null)
        {
            ViewState["sortDirection"] = SortDirection.Ascending;
        } return (SortDirection)ViewState["sortDirection"];
    }
    set
    {
        ViewState["sortDirection"] = value;
    }
}

DataSet ds = new DataSet();
DataView dv = new DataView();

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
        GridView1.DataSource = dv;
        GridView1.DataBind();
    }
}

private DataView BindGrid()
{
    ds = new Query(AnthemWeb.DAL.Item.Schema).ExecuteDataSet();

    if (ViewState["sortExpr"] != null)
    {
        dv = new DataView(ds.Tables[0]);
        dv.Sort = (string)ViewState["sortExpr"];
    }
    else
    {
        dv = ds.Tables[0].DefaultView;
    }

    return dv;
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataSource = BindGrid();
    GridView1.DataBind();
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
    string stExp = e.SortExpression;
    string stDir = string.Empty;
    if (SortDir == SortDirection.Ascending)
    {
        SortDir = SortDirection.Descending;
        stDir = "DESC";
    }
    else
    {
        SortDir = SortDirection.Ascending;
        stDir = "ASC";
    }

    ViewState["sortExpr"] = e.SortExpression + " " + stDir;
    GridView1.DataSource = BindGrid();
    GridView1.DataBind();
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
    int selectedRowIndex;
    selectedRowIndex = GridView1.SelectedIndex;
    GridViewRow row = GridView1.Rows[selectedRowIndex];
    string ID = row.Cells[0].Text;

    Response.Redirect("customer-details.aspx?ID=" + ID, false);
}

Answer 1

The Fill() method of DbDataAdapter class has a convenient overload for this very purpose :

public int Fill (DataSet dataSet, int startRecord, int maxRecords, string srcTable)

In this overload, you can provide the record number to start with and the maximum records to retrieve from that starting point. This enables you to retrieve only a subset of records from the datasource based on the current page index. All you need to keep track of is the current record index that is displayed.

So you would need to modify your DAL to provide this overload. I haven't used SubSonic so I can't tell if that feature exists in it.

Answer 2

what version of ASP.NET do you use? There is a DataPager control that comes with ListView in .NET 3.5 See system.web.ui.webcontrols.datapager

SubSonic also supports paging, you should call to the Paged method inline in the query. See SubSonic Paging

Answer 3

One thing you can do is to buffer the data in the webserver and stream your data-pages to the web-browser. You can acheive this using GridView conttrol and by creating a background thread that pulls data from your database to fill the buffer using SqlDataReader. Then browser pulls data-pages (200 rows of first page, seconds page and so on) from webserver using AJAX until all rows in the buffer is transmitted to the browser and store it in JavaScript string array.

I find this strategy effective and tested to a maximum of 300,000 rows with 18 columns. One advantage is paging the data does not depends on your database. You can even perform sorting against the buffer (which can be a DataTable) instead of hitting the database again.

To find out more, you can follow this link. Hope this helps.