Creating a great looking Gridview with filtering

31 May
May 31, 2008

Matt Berseth (http://www.mattberseth.com/) has some of the best ASP.NET 2.0 articles around, and his examples are always well documented.

Matt recently wrote some articles about how to a design good looking Gridview’s (Building a vs2008 Styled Grid and Gridview Themes based on Google’s Personalized Homepage) which was exactly what I was after.

I have been looking for quite some time for a good looking Gridview and Matt’s were great, although they didn’t include any filtering. Nearly all of my Gridviews have filtering enabled and so here is a quick tutorial to show you how to design a stylish Gridview based on Matt’s design with filtering enabled for some of the columns.

Creating a great looking Gridview with filtering

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!

Tags:
© Copyright - Evonet