Paging a Filtered GridView

In many forums there are people struggling with filtering and paging in gridview.
When separated, both filtering and paging works smoothly.
But when trying to change the page index of a filtered result set, all hell will break loose.
Mostly a new page with the unfiltered result set comes into play.

Here's a sample on how to do it.


The ASPX

<table>
    <tr>
        <td>
            Search By Product Name:
        </td>
        <td>
            <asp:TextBox ID="txtProductName" runat="server"></asp:TextBox>
        </td>
        <td>
            <asp:Button ID="btnBindGridView" runat="server" 
                        Text="Populate Grid" onclick="btnBindGridView_Click" />
        </td>
    </tr>
    <tr>
        <td colspan="3">
            <asp:GridView ID="gvwProducts" runat="server" 
                            AllowPaging="true"
                            AutoGenerateColumns="false"
                            PageSize="3" 
                            OnPageIndexChanging="gvwProducts_PageIndexChanging">
                <Columns>
                    <asp:BoundField DataField="ProductName" HeaderText="Product Name"/>
                    <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity"/>
                    <asp:BoundField DataField="UnitPrice" HeaderText="Price"/>
                </Columns>
            </asp:GridView>
        </td>
    </tr>
</table>

The ASPX.CS

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

protected void btnBindGridView_Click(object sender, EventArgs e)
{
    BindGridView();
}

protected void gvwProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvwProducts.PageIndex = e.NewPageIndex;
    BindGridView();
}

private void BindGridView()
{
    DataTable dtProducts = new DataTable();
    string strSelectCommand = "SELECT * FROM Products" 
            + ((txtProductName.Text.Trim() == "") ? "" : (" WHERE ProductName LIKE '" + txtProductName.Text.Trim() + "%'"));
    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
    {
        using (SqlDataAdapter adapProducts = new SqlDataAdapter(strSelectCommand, sqlConn))
        {
            adapProducts.Fill(dtProducts);
        }
    }
    gvwProducts.DataSource = dtProducts;
    gvwProducts.DataBind();
}

DataBase used --> Northwind
Extra Namespace used -->System.Data.SqlClient;

Hope this helps!

Related Posts :