Show number of results in gridview based on dropdown?

Question

I have a datatable which returns around 30,000 records. I am displaying these records in an ASP:GridView control. I have a dropdown control in which I want the user to be able to select the number of records to display in the GridView. The default should be All, but it could also have values such as 20, 50, 100 for example. I am not quite sure how to do this.

What if I don't have Paging turned on. Will PageSize still work?

I hardcoded GridView.PageSize = 1 and it still returned all the records.

Answer 1

Use the PageSize property of the gridview. Link.

Answer 2

You can add the value of the dropdown (20, 50, 100) to your query so that you only select the top (20, 50, 100) records.

Are you going to be using paging as well?

Answer 3

Here's what I would do. First, I would add a column to the DataTable that would serve as a counter if it isn't there already. Then, you bind your GridView to a DataView. Here's some code to demonstrate what I'm talking about:

            //add column with counter
            table.Columns.Add("counter", typeof(int));
            for (int i = 0; i < table.Rows.Count; i++)
            {
                table.Rows[i]["counter"] = i+1;
            }

Then, get the default view from the DataTable and set the RowFilter:

            DataView view = table.DefaultView;
            view.RowFilter = "counter <= 100"; //or whatever number the user selected

Finally, just bind the GridView to the DataView directly.

Answer 4

Paging should always be dealt with as early as possible in the data selection. You don't want to retrieve 30,000 records from the DB to the app server to then only show 50 of them.

Something like: (for selecting page 3, 50 per page)

select top 50 from x where pk not in (select top 100 pk from x)

Which translates as:

CREATE PROCEDURE sspGetItems (@pageSize int, @pageNum int)
AS
SELECT TOP @pageSize 
FROM x
WHERE pk NOT IN 
(
 SELECT TOP (@pageNum - 1) * @pageSize pk FROM x
)

Obviously more care will need to be taken around sorting etc.

Answer 5

If you want that PageSize works you have to set AllowPaging of GridView to true. Obviously you have to create the method to manage the event PageIndexChanging.

Like this:

protected void myGvw_OnPageIndexChanging(object sender, GridViewPageEventArgs e) 
{
     myGvw.PageIndex = e.NewPageIndex;
     // then recall the method that bind myGvw
}

The DropDown could have the property AutoPostBack set to true and on his OnSelectedIndexChanged you must set

myGvw.PageSize = Convert.ToInt32(myDropDown.SelectedValue)