How to filter an asp:DataGrid

Question

How can I filter the results shown in an asp.net Datagrid?

html

    <asp:TextBox ID="SearchText" runat="server" OnTextChanged="UpdateFilter" />
    <asp:DataGrid ID="gvProjList" runat="server" CellPadding="4" 
        ForeColor="#333333" GridLines="None" style="text-align: left" 
        Width="400px" AllowSorting="true">
        <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
    </asp:DataGrid>

c#

    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            DataTable dt ....
            ....
            gvProjList.DataSource = dt;
            gvProjList.DataBind();
         }
     }


    protected void UpdateFilter(object sender, EventArgs e)
    {
        // ????
        // ViewState["LastFilter"] = SearchText.Text;
        // gvProjList.DataBind();

    }

Answer 1

Well, it's all about the DataTable.

Filtering in General

You can either handle this by changing the SQL/store procedure/etc you use to load data into the DataTable, or (and be careful here) by loading all the data and then using ADO.Net's built-in filtering capabilities.

For the latter case, DataTable.Select springs to mind (Article on DataTable.Select and Filtering in ADO.Net).

Search in Particlar

However, in the context of a search, the best practice is to do the work in the database, meaning in your SQL. Otherwise you have to pull all the data across the wire each time a search occurs, and then filter it on the client. Unless your table is always going to be very small, that's a very real performance problem. And even then it smells pretty bad, becuase if it's big enough to need a search, you're already dealing with the type of table that could grow.

It's much better to load just what you need. This can lead to some annoyingly involved stored procedures. This is one of the problems where ORM tools really shine because they make it so easy to do very dynamic queries without necessarily having to mess with big sprocs with optional parameters and so on.

Hope that helps!

Answer 2

Is there a reason you are executing your SQL query in Page_Load(...)? If not I would highly recommend using a SqlDataSource control configured with a SQL statement including a parameter for your filter and a ControlParameter object in the SqlDataSource.SelectParameters collection. All of this can be done declaratively it and is much more efficient for both the SQL and ASP.NET servers.

Best part is, it can all be done in markup:

<asp:TextBox ID="SearchText" runat="server" OnTextChanged="UpdateFilter" />
<asp:DataGrid ID="gvProjList" runat="server" CellPadding="4" ForeColor="#333333"
    GridLines="None" Style="text-align: left" Width="400px" AllowSorting="true" DataSourceID="SqlDataSource1">
    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
</asp:DataGrid>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT * FROM [Products] WHERE [Description] LIKE '%' + @SearchText + '%';">
    <SelectParameters>
        <asp:ControlParameter DbType="String" ControlID="SearchText" PropertyName="Text"
            DefaultValue="" />
    </SelectParameters>
</asp:SqlDataSource>

Answer 3

It the easy way but something like this should work

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

protected void UpdateFilter(object sender, EventArgs e)
{
    BindGrid();
}

private void BindGrid()
{
    DataTable dt = new DataTable();
    //...
    dt.DefaultView.RowFilter = "SomeColumnInTheDataTable = '" + SearchText.Text + "'";
    gvProjList.DataSource = dt.DefaultView;
    gvProjList.DataBind();
}