Display filtered subsets of data in the GridView control using DropDownList embedded in GridView column HeaderTemplate.
Recently, while working on one of the company’s web based projects, I needed to display
filtered subsets of the data in the GridView control. Naturally, the first thing
that came to mind was using FilterExpression property of SqlDataSource, and on
a test page, it was working just fine, but when I set GridView property AllowPaging
to “true,” the problems began. As soon as I tried to navigate pages on filtered
GridView, several things went wrong: First, DropDownList did not retain the value
selected earlier. Second, GridView was losing the applied filter. At this point,
ViewState came to the rescue.
The final solution was easy to implement, maintain and extend, so I decided to share
it with the developers’ community.
Firstly, let’s prepare the working environment by placing two SqlDataSources and
a GridView on WebForm.
In this example I am using a connection to Northwind database – Customers table.
In this example I am using a connection to Northwind database – Customers table.
Here is how SqlDataSource for GridView looks like:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [CompanyName], [ContactName], [ContactTitle],
[Country], [Phone], [Address], [City] FROM [Customers]">
</asp:SqlDataSource>
Then, I modified the “Country” field of the GridView to TemplateField so it gave
me the ability to place DropDownList in the HeaderTemplate:
This is what my GridView looks like after the changes:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
CellPadding="4" DataSourceID="SqlDataSource1"
ForeColor="#333333" GridLines="None"
AllowPaging="True" OnPageIndexChanged="GridView1_PageIndexChanged">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
...
<asp:TemplateField HeaderText="Country" SortExpression="Country">
<HeaderTemplate>
Country
<asp:DropDownList ID="ddCountry"
DataTextField="Country"
AutoPostBack="true"
OnSelectedIndexChanged="FilterDropDown_IndexChanged"
OnPreRender="SetValue"
DataSourceID="SqlDataSource2" runat="server"/>
</HeaderTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Country") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Country") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
...
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
Secondly, I configured the SqlDataSource to return distinct list of the countries
from the Customers table and it looks like this:
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT 'All Countries' AS Country UNION SELECT DISTINCT [Country] as Country FROM [Customers]">
</asp:SqlDataSource>
As you can see, so far everything is simple; the only trick I used here is to UNION
“All Countries” string with the rest of the data to add “All Countries” to DropDownList
items collection, so the user will be able to remove the filter when needed.
Now we should add methods FilterDropDown_IndexChanged and SetValue of the DropDownList to the code behind the page like this:
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
}
protected void SetValue(object sender, EventArgs e)
{
}
and we are ready to test the page.
After you run the page you will see something like this:
When you test the result after filtering you will notice that after clicking on page
number links, the filtering will not persist. It is a good time to start a little
coding.
To preserve selected values between page postbacks, we will need a page level variable.
I chose the Hashtable.
public partial class GridFiltering : System.Web.UI.Page
{
Hashtable filter;
...
}
This variable is being initialized on every page load from ViewState of the page.
protected void Page_Load(object sender, EventArgs e)
{
filter = ViewState["FilterArgs"] == null ? new Hashtable() : (Hashtable)ViewState["FilterArgs"];
}
The keystone of the application is a ApplyGridFilter method with the following code:
protected void ApplyGridFilter()
{
string args = " ";
int i = 0;
foreach (object key in filter.Keys)
{
if (i == 0)
{
args = key.ToString() + filter[key].ToString();
}
else
{
args += " AND " + key.ToString() + filter[key].ToString();
}
i++;
}
SqlDataSource1.FilterExpression = args;
//Filter needs to be saved between postbacks
ViewState.Add("FilterArgs", filter);
}
As you can see from the snipet above, everything is pretty simple: Local variable
args being initialized to hold filter value. Default value of args is a space
character.
Note: If you use empty string (“”) in FilterExpression in your GridView, you will not
see any records. To remove the filter, FilterExpression should be set to space
(“ ”)
Then we iterate trough every key in filter Hashtable (if there are any) and build
filter string in the following format “Field Name =/LIKE/<> ’value’ “ if
there is more than one filter provided we will append the Field Name value pare using AND operator. After all filter values are combined
in the string, we assign a value to SqlDataSource. FilterExpression and save
Hashtable back to Page ViewState. The logical question is: how do they get into
Hashtable in the first place?
To answer the question, let’s look at the code in DropDownList methods.
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
if (dd.SelectedItem.Text != "ALL Countries")
{
if(filter.ContainsKey("Country")){
filter["Country"] = "='" + dd.SelectedItem.Text + "'";
}else{
filter.Add("Country", "='" + dd.SelectedItem.Text + "'");
}
}
else
{
filter.Remove("Country");
}
ApplyGridFilter();
GridView1.PageIndex = 0;
}
As you can see from the html code provided at the beginning of this article, we set
AutoPostBack property of DropDownList to "true". This will trigger Page postback every time DropDownList SelectedIndex changes. We get the value of
the selected item in the DropDownList by casting sender, then we add Key and
Value if key does not exist or reset value of the existing key to a new value.
When the Hashtable filter contains desired value we call ApplyGridFilter method (described
above), and reset GridView PageIndex property. We are almost done. The last thing
to do for us is to make sure that DropDownList selected value stays intact with
applied FilterExpression value. This effect is achieved using SetValue method
on OnPreRender event of the DropDownList.
protected void SetValue(object sender, EventArgs e)
{
DropDownList ddl = (DropDownList)sender;
if (filter != null)
{
if (filter.ContainsKey("Country"))
{
foreach (ListItem li in ddl.Items)
{
if (li.Text == filter["Country"].ToString().Substring(2, filter["Country"].ToString().Length - 3))
{
li.Selected = true;
return;
}
}
}
}
}
Right before the DropDownList is drawn on the page, we look at our filter Hashtable
and if we find the value corresponding to this DropDownList we set its SelectedItem.
This is it. Hope you find it helpful.
By the way, I’m attaching full aspx and cs files with the code used in this article.
No comments:
Post a Comment