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.
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.
Filter works great by itself, but when you sort any other column, the filter does not persist.
I'll answer my own question for those who will inevitably ask.
don't forget to add an "OnSorting=GridView1_Sorting" to your gridview much like the author's already provided OnPageIndexChaged.
This will persist the filter over the column sorting events as well as paging. (done in VB here)
Protected Sub GridView1_Sorting(sender As Object, e As GridViewSortEventArgs)' small distinction here...
ApplyGridFilter()
End Sub 'GridView1_Sorting
i am using access datasource this code is not working <asp:SqlDataSource...........
No such tag found
i am using access datasource this code is not working <asp:SqlDataSource...........
No such tag found
i am using access datasource this code is not working <asp:SqlDataSource...........
No such tag found
I am facing a problem with this code that when i am using paging in grid-view, and i have more data then my page size than it is not showing the data acording to filter value in the second page. How to fix this problem. If have any sollution please reply me asap.