Filtering Data with the DataGrid

As we mentioned earlier, when users view data in a DataGrid, they often want to filter the data to look at only a subset of the entire selection. You can allow them to view only a portion of the data without returning to the database with a revised query. The DataView object supports a RowFilter property for limiting the data, which is employed when the view is used for data-binding or enumeration.

Note

Consider providing for data filtering and sorting without return trips to the database for throughput. Be aware, however, that storing data in Session has an impact on the amount of memory used on the server. Also, using ViewState to enable the data to make a round trip has an impact on the size of the page and the post data that will be submitted in subsequent requests.

In Code Listing 3-15, we return to the data example of displaying states and their time zones. A DropDownList has been added with a corresponding handler that updates the RowFilter of the DataView. Notice that when the RowFilter is set to the empty string, no filtering is performed.

Code Listing 3-15: DataGridFilter.aspx

 <%@Import namespace="System.Data" %>
<script language="C#" runat="server">
DataTable data;
DataView view;
protected void Page_Load(object o, EventArgs e) {
    GetData();
    datagrid1.DataSource = view;
    if(!IsPostBack) {
        datagrid1.DataBind();
    }
}
 
void GetData() { 
    view = Session["view"] as DataView;
    if(view != null) {
        return;
    }
    data = new DataTable();
    data.Columns.Add(new DataColumn("TheID", typeof(Int32)));
    data.Columns.Add(new DataColumn("Name", typeof(string)));
    data.Columns.Add(new DataColumn("TimeZone", typeof(string)));
 
    DataRow dr;
    dr = data.NewRow();
    dr[0] = 1; dr[1] = "Washington"; dr[2] = "Pacific";
    data.Rows.Add(dr);
    dr = data.NewRow();
    dr[0] = 2; dr[1] = "Utah"; dr[2] = "Mountain";
    data.Rows.Add(dr);
    dr = data.NewRow();
    dr[0] = 3; dr[1] = "Wisconsin"; dr[2] = "Central";
    data.Rows.Add(dr);
    dr = data.NewRow();
    dr[0] = 4; dr[1] = "New York"; dr[2] = "Eastern";
    data.Rows.Add(dr);
    dr = data.NewRow();
    dr[0] = 5; dr[1] = "Florida"; dr[2] = "Eastern";
    data.Rows.Add(dr);
 
    Session["data"] = data;
    view = new DataView(data);
    return;
}
 
void FilterData(object o, EventArgs e) {
    string selectedZone = TimeZoneFilter.SelectedItem.Value;
    if(selectedZone == "Pacific") {
        view.RowFilter = "TimeZone = ’Pacific’";
    }
    else if(selectedZone == "Mountain") {
        view.RowFilter = "TimeZone = ’Mountain’";
    }
    else if (selectedZone == "Central" ) {
        view.RowFilter = "TimeZone = ’Central’";
    }
    else if (selectedZone == "Eastern") {
        view.RowFilter = "Timezone = ’Eastern’";
    }
    datagrid1.DataBind();
}
 
</script>
 
<form runat="server">
    <asp:DataGrid runat="server"   
        AutoGenerateColumns="false">
        <Columns>
            <asp:ButtonColumn CommandName="Select" Text="Select" />
            <asp:BoundColumn DataField="TheID" ReadOnly="true" 
                HeaderText="id" />
            <asp:BoundColumn DataField="Name" HeaderText="year" />
            <asp:BoundColumn DataField="TimeZone" 
                HeaderText="make" />
 
        </Columns>
    </asp:DataGrid><br/>
    <b>Filter by time zone:</b>
    <asp:DropDownList runat="server" 
        OnSelectedIndexChanged="FilterData" 
        AutoPostBack="true" >
        <asp:ListItem>None</asp:ListItem>
        <asp:ListItem>Pacific</asp:ListItem>
        <asp:ListItem>Mountain</asp:ListItem>
        <asp:ListItem>Central</asp:ListItem>
        <asp:ListItem>Eastern</asp:ListItem>
    </asp:DropDownList>
</form>