1

Creating a great looking Gridview with filtering in ASP.Net

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.

 

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>

 

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 happy coding

Rohit Tripathi

Phasellus facilisis convallis metus, ut imperdiet augue auctor nec. Duis at velit id augue lobortis porta. Sed varius, enim accumsan aliquam tincidunt, tortor urna vulputate quam, eget finibus urna est in augue.

1 comment:

Post a Comment