Applies To:WebGrid for ASP.NET
Author:John Juback
Published On:3/9/2005

A popular feature of ComponentOne True DBGrid for .NET is the filter bar, which is a separate row of editable cells between the column headers and the first data row. By typing into a filter bar cell (or selecting a value from a dropdown list), end users can apply filter criteria to one or more columns to limit the set of displayed rows. Although ComponentOne WebGrid for ASP.NET (a.k.a. C1WebGrid) does not have a built-in filter bar, adding one is relatively straightforward.



This article demonstrates how to implement a filter bar like the one shown above. You should be familiar with setting up a data source in ASP.NET and binding it to a C1WebGrid component.


We begin with a C1WebGrid component bound to a data source that uses the following query:


SELECT Salesperson,
YEAR(OrderDate) AS OrderYear,
Country,
SUM(ExtendedPrice) AS Amount
FROM Invoices
GROUP BY Salesperson, YEAR(OrderDate), Country
ORDER BY Salesperson, YEAR(OrderDate) DESC, Country

In order to mimic the behavior of the WinForms grid, we will make the C1WebGrid component scroll vertically by setting the following properties at design time:


Height450px
Width620px
VScrollBarStyleAutomatic

The height is an arbitrary value. The width was set to the sum of the individual column widths, plus some extra space to accommodate the vertical scroll bar. The automatic setting causes the vertical scroll bar to appear only when there are more rows than will fit in the available area.


We will actually use the grid's footer row for the filter bar. First, we enable the footer row by setting the ShowFooter property to true, then make it a fixed, nonscrolling row by adding the following code to the handler for the grid's ItemCreated event:


private void C1WebGrid1_ItemCreated
(
object sender, C1.Web.C1WebGrid.C1ItemEventArgs e
)
{
if (e.Item.ItemType == C1ListItemType.Footer)
{
// Make the footer a nonscrolling row, 32 pixels high
e.Item.Fixed = true;
e.Item.Height = Unit.Pixel(32);
}
}

The trick is to set the Fixed property of the footer item to true, which causes the footer to be rendered directly below the grid's header.


We will also use the ItemCreated event to create the combo boxes used to specify column values and the command button used to apply filter criteria. Adding a control to a filter bar cell involves the following steps:



  1. Derive a TableCell object from the Controls collection of the underlying TableRow object.

  2. Derive the cell width from the grid's Column collection.

  3. Create, initialize, and populate the control.

  4. Add the control to the Controls collection of the TableCell object derived in step 1.


The following code adds a dropdown combo box to the Salesperson column:


if (e.Item.ItemType == C1ListItemType.Footer)
{
// Derive a TableCell object for column 0 (Salesperson)
TableCell td = (TableCell) e.Item.Controls[0];
 
// Derive the cell width of the first grid column
Unit u = C1WebGrid1.Columns[0].ItemStyle.Width;
 
// Fill the datasets used to populate the dropdown
oleDbDataAdapter2.Fill(dsSalespersons1);
 
// Create and initialize a new combo box
DropDownList combo = new DropDownList();
combo.Width = u;
combo.CssClass = "filter";
 
// Add a default list item to indicate no selection
combo.Items.Add(new ListItem("(All)", ""));
 
// Copy field values from the data source to the combo
DataRowCollection rows = dsSalespersons1.Tables[0].Rows;
for (int i = 0; i < rows.Count; i )
{
string s = rows[i]["Salesperson"].ToString();
combo.Items.Add(new ListItem(s));
}
 
// Add the combo to the TableCell for column 0
td.Controls.Add(combo);
}

Note that you must fill the salesperson dataset before populating the combo box. Alternatively, if the range of allowable values is known in advance (as in the Year column), you can create the list items directly without iterating through a dataset.


Generally, you will want to provide several dropdown controls, so it pays to create utility routines that you can call multiple times. See the source code listing at the end of the article for examples.


The command button is created and attached to the filter bar row in a similar fashion. Its Click handler examines the contents of the dropdown combo boxes and builds a query string. This string is stored in the Session object along with the current values of each dropdown control. Before exiting, the handler redirects to the current page.


When the page reloads, the query string is retrieved from the Session object. It is then assigned to the RowFilter property of the DataView component that serves as the grid's data source. When the grid is rebound, the ItemCreated event fires again, at which time the dropdowns are recreated, and their selected items are retrieved from the Session object. See the source code listing at the end of the article for details.


The following figure shows a grid with filter criteria specified for the Year and Country columns. Note that the vertical scroll bar is not shown since the grid height is less than its preset value.



You can extend the techniques presented in this article to implement other kinds of dropdown controls, such as calendars. You can also use text boxes to gather input for SQL LIKE clauses.


Click the following link to download the complete sample project for this article:


    C1WebGridFilterBar.zip


private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
if (Session["FilterExpression"] != null)
dataView1.RowFilter = Session["FilterExpression"].ToString();
 
oleDbDataAdapter1.Fill(dsInvoices1);
C1WebGrid1.DataBind();
}
}
 
private DropDownList BaseCreateDropDownList(Unit width)
{
DropDownList combo = new DropDownList();
combo.Width = width;
combo.CssClass = "filter";
combo.Items.Add(new ListItem("(All)", ""));
return combo;
}
 
private void MaybeSelectItem(DropDownList combo, string key)
{
if (Session[key] != null)
{
string match = Session[key].ToString();
 
if (match != "")
combo.Items.FindByText(match).Selected = true;
}
}
 
private DropDownList CreateDropDownList
(
DataSet ds, string field, string key, Unit width
)
{
DropDownList combo = BaseCreateDropDownList(width);
DataRowCollection rows = ds.Tables[0].Rows;
 
for (int i = 0; i < rows.Count; i )
{
string s = rows[i][field].ToString();
combo.Items.Add(new ListItem(s));
}
 
MaybeSelectItem(combo, key);
return combo;
}
 
private DropDownList CreateDropDownList
(
string[] values, string key, Unit width
)
{
DropDownList combo = BaseCreateDropDownList(width);
 
for (int i = 0; i < values.GetLength(0); i )
{
string s = values[i];
combo.Items.Add(new ListItem(s));
}
 
MaybeSelectItem(combo, key);
return combo;
}
 
private void C1WebGrid1_ItemCreated
(
object sender, C1.Web.C1WebGrid.C1ItemEventArgs e
)
{
if (e.Item.ItemType == C1ListItemType.Footer)
{
// Fill datasets used in dropdowns
oleDbDataAdapter3.Fill(dsCountries1);
oleDbDataAdapter2.Fill(dsSalespersons1);
 
// Column 0: Salesperson
TableCell td = (TableCell) e.Item.Controls[0];
Unit u = C1WebGrid1.Columns[0].ItemStyle.Width;
DropDownList combo = CreateDropDownList(
dsSalespersons1, "Salesperson", "FilterSalesperson", u);
td.Controls.Add(combo);
 
// Column 1: Year
td = (TableCell) e.Item.Controls[1];
u = C1WebGrid1.Columns[1].ItemStyle.Width;
string[] arrYears = {"2004", "2003", "2002"};
combo = CreateDropDownList(arrYears, "FilterYear", u);
td.Controls.Add(combo);
 
// Column 2: Country
td = (TableCell) e.Item.Controls[2];
u = C1WebGrid1.Columns[2].ItemStyle.Width;
combo = CreateDropDownList(
dsCountries1, "Country", "FilterCountry", u);
td.Controls.Add(combo);
 
// Column 3: Filter button
td = (TableCell) e.Item.Controls[3];
u = C1WebGrid1.Columns[3].ItemStyle.Width;
u = new Unit(u.Value / 2, u.Type);
Button button = new Button();
button.Text = "Filter";
button.CssClass = "filter";
button.Width = u;
button.Click = new EventHandler(button_Click);
td.Controls.Add(button);
 
// Make the footer a nonscrolling row, 32 pixels high
e.Item.Fixed = true;
e.Item.Height = Unit.Pixel(32);
}
}
 
private void button_Click(object sender, EventArgs e)
{
Button button = (Button) sender;
C1GridItem item = (C1GridItem) button.Parent.Parent;
DropDownList sales = (DropDownList) item.Controls[0].Controls[0];
DropDownList year = (DropDownList) item.Controls[1].Controls[0];
DropDownList country = (DropDownList) item.Controls[2].Controls[0];
StringBuilder sb = new StringBuilder();
string strSales = "";
string strYear = "";
string strCountry = "";
 
if (sales.SelectedIndex > 0)
{
strSales = sales.SelectedItem.Value;
sb.AppendFormat("{1}Salesperson='{0}'",
strSales, (sb.Length > 0) ? " AND " : "");
}
 
if (year.SelectedIndex > 0)
{
strYear = year.SelectedItem.Value;
sb.AppendFormat("{1}OrderYear={0}",
strYear, (sb.Length > 0) ? " AND " : "");
}
 
if (country.SelectedIndex > 0)
{
strCountry = country.SelectedItem.Value;
sb.AppendFormat("{1}Country='{0}'",
strCountry, (sb.Length > 0) ? " AND " : "");
}
 
Session["FilterSalesperson"] = strSales;
Session["FilterYear"] = strYear;
Session["FilterCountry"] = strCountry;
Session["FilterExpression"] = sb.ToString();
Response.Redirect(Page.Request.Url.AbsolutePath);
}