Gridview Filtering built in function?

Question

Does .NET have some type of built in function that allows filtering on Gridviews? I've always programmed my filters by using a datasource generated by a dynamic stored procedure that takes parameters. But in order to keep the filter row always present I have to place the code to create the controls that are used to filter in three different places when paging is allowed(Page_load,Gridview_Databound,Page_SaveStateComplete) It just seems like there must be a better way. If so, How?

Answer 1

Only if you do the work. Check out

http://blog.evonet.com.au/post/Creating-a-Stylish-looking-Gridview-with-Filtering.aspx

---

As noted in comments, this site is not available anymore. The following is taken directly from Bartek Marnane's blog entry you can find on web.archive.com :

Step 1: Create the Gridview and Datasource

Create a simple Gridview and Datasouce. In this example I am using a SQL Datasource, but I recommend using a ObjectDataSource for production environments. Set the ConnectionString to the value in your web.config file and the ItemStyle-Width for each of your fields depending on the type of data and how much space you have.

<asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="true" DataSourceID="dsGridview" Width="650px" PageSize="20"
CssClass="Gridview">
<Columns>
    <asp:BoundField DataField="id" HeaderText="id" SortExpression="id"
        ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" />
    <asp:BoundField DataField="FirstName" HeaderText="Sort" SortExpression="FirstName"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="LastName" HeaderText="Sort" SortExpression="LastName"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="Department" HeaderText="Sort" SortExpression="Department"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="Location" HeaderText="Sort" SortExpression="Location"
        ItemStyle-Width="150px" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
SelectCommand="SELECT * FROM [T_Employees]" />

Step 2: Create the table used for the Gridview headers

Now we create a simple table to hold the headings and filter drop down boxes.

<table style="width: 650px" border="0" cellpadding="0" cellspacing="1"
class="GridviewTable">
<tr>
    <td style="width: 50px;">
        ID
    </td>
    <td style="width: 150px;">
        First Name
    </td>
    <td style="width: 150px;">
        Last Name
    </td>
    <td style="width: 150px;">
        Department
    </td>
    <td style="width: 150px;">
        Location
    </td>
</tr>
<tr>
    <td style="width: 50px;">
    </td>
    <td style="width: 150px;">
    </td>
    <td style="width: 150px;">
    </td>
    <td style="width: 150px;">
        <asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment"
            AutoPostBack="true" DataValueField="department" runat="server" Width="130px"
            Font-Size="11px" AppendDataBoundItems="true">
            <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
    </td>
    <td style="width: 150px;">
        <asp:DropDownList ID="ddlLocation" DataSourceID="dsPopulateLocation"
            AutoPostBack="true" DataValueField="location" runat="server" Width="130px"
            Font-Size="11px" AppendDataBoundItems="true">
            <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
    </td>
</tr>
<tr>
    <td colspan="5">
        <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False"
            AllowPaging="True" AllowSorting="true" DataSourceID="dsGridview"
            Width="650px" PageSize="10" CssClass="Gridview">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="Sort" SortExpression="id"
                    ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" />
                <asp:BoundField DataField="FirstName" HeaderText="Sort"
                    SortExpression="FirstName" ItemStyle-Width="150px" />
                <asp:BoundField DataField="LastName" HeaderText="Sort"
                    SortExpression="LastName" ItemStyle-Width="150px" />
                <asp:BoundField DataField="Department" HeaderText="Sort"
                    SortExpression="Department" ItemStyle-Width="150px" />
                <asp:BoundField DataField="Location" HeaderText="Sort"
                    SortExpression="Location" ItemStyle-Width="150px" />
            </Columns>
        </asp:GridView>
    </td>
</tr>
</table>

For the last cell, set the td colspan value to the number of fields in your Gridview. Move your Gridview into that last cell.

Step 3: Create the style sheet

The stylesheet that I use has the following items:

.GridviewDiv {font-size: 62.5%; font-family: 'Lucida Grande',
    'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
Table.Gridview{border:solid 1px #df5015;}
.GridviewTable{border:none}
.GridviewTable td{margin-top:0;padding: 0; vertical-align:middle }
.GridviewTable tr{color: White; background-color: #df5015; height: 30px; text-align:center}
.Gridview th{color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;
    padding:0.5em 0.5em 0.5em 0.5em;text-align:center}
.Gridview td{border-bottom-color:#f0f2da;border-right-color:#f0f2da;
    padding:0.5em 0.5em 0.5em 0.5em;}
.Gridview tr{color: Black; background-color: White; text-align:left}
:link,:visited { color: #DF4F13; text-decoration:none }

You should be able to just copy this into your css file without it affecting your exiting style sheets, although be careful if you have already set :link and :visited in your site.

Step 4: Add the filtering drop down boxes and data sources

In the table created in Step 2, add a dropdownlist to each of the cells in the second row that contain the field you want to filter. Make sure eac dropdownlist is smaller than the cell it is going into, otherwise your table borders will not be aligned. Set up a datasource which gets the each possible value of that field within your table. I do this by running a DISTINCT for all values in the table I am filtering:

<asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment"
AutoPostBack="true" DataValueField="department" runat="server" Width="130px" Font-Size="11px"
AppendDataBoundItems="true">
    <asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateDepartment" runat="server"
ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>" SelectCommand="SELECT
DISTINCT Department from [T_Employees]"></asp:SqlDataSource>

Create as many dropdownlists as fields that you would like to filter by.

A few things to note:

Set the AppendDataBoundItems=True property for your dropdownlist as it will be filled at runtime. Set the AutoPostBack=True property so that the Gridview is refreshed when the selection changes. Make sure your ListItem for ‘All’ has ‘%’ as the value. Your filter expression will be SELECT * FROM [TABLE NAME] Where [FieldName] like ‘{0}%’ where {0} is the value in your dropdownlist. If your dropdownlist is set to all then the query string will be SELECT * FROM [TABLE NAME] Where [FieldName] like ‘%%’ which, in SQL returns all values.

Step 5: Add filtering to your Gridview’s Datasource

Add a FilterExpress so your Gridview’s Datasource such as

[Field1] like ‘{0}%’ and [Field2] like ‘{1}%’ and [Field3] like ‘{2}%’ and [Field4] like ‘{3}%’ and … etc

Your fields then need to be added to the FilterParameters section in the same order as your filter expression. The FilterParameters section references the SelectedValue of your dropdownlists.

<asp:SqlDataSource ID="dsGridview" runat="server"
    ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
    SelectCommand="SELECT * FROM [T_Employees]" FilterExpression="Department like '{0}%'
    and Location like '{1}%'">
    <FilterParameters>
        <asp:ControlParameter Name="Department" ControlID="ddldepartment"
            PropertyName="SelectedValue" />
        <asp:ControlParameter Name="Location" ControlID="ddllocation"
            PropertyName="SelectedValue" />
    </FilterParameters>
</asp:SqlDataSource>

That's it!