Accessing and Updating Data in ASP.NET: Filtering Data Using a CheckBoxListBy Scott Mitchell
Filtering Database Data with Parameters, an earlier installment in this article series, showed how to filter the data returned by ASP.NET's data source controls. In a nutshell, the data source controls can include parameterized queries whose parameter values are defined via parameter controls. For example, the SqlDataSource can include a parameterized
SelectCommand, such as:
SELECT * FROM Books WHERE Price > @Price. Here,
@Priceis a parameter; the value for a parameter can be defined declaratively using a parameter control. ASP.NET offers a variety of parameter controls, including ones that use hard-coded values, ones that retrieve values from the querystring, and ones that retrieve values from session, and others.
Perhaps the most useful parameter control is the ControlParameter, which retrieves its value from a Web control on the page. Using the ControlParameter we can filter the data returned by the data source control based on the end user's input. While the ControlParameter works well with most types of Web controls, it does not work as expected with the CheckBoxList control. The ControlParameter is designed to retrieve a single property value from the specified Web control, but the CheckBoxList control does not have a property that returns all of the values of its selected items in a form that the CheckBoxList control can use. Moreover, if you are using the selected CheckBoxList items to query a database you'll quickly find that SQL does not offer out of the box functionality for filtering results based on a user-supplied list of filter criteria.
The good news is that with a little bit of effort it is possible to filter data based on the end user's selections in a CheckBoxList control. This article starts with a look at how to get SQL to filter data based on a user-supplied, comma-delimited list of values. Next, it shows how to programmatically construct a comma-delimited list that represents the selected CheckBoxList values and pass that list into the SQL query. Finally, we'll explore creating a custom parameter control to handle this logic declaratively. Read on to learn more!
Filtering Data Based On A Set Of Values
Drop-down lists are a very common user interface element used to filter data. For example, when searching for a product on Amazon.com you can search the entire store or limit your search to a particular area, such as Books, Electronics, Toys, and so on. These various stores are listed in a drop-down list, allowing the end user to search against a particular store. Creating a SQL query to perform such a filtered search is pretty straightforward. Assuming that there is a
Productstable with a
StoreIdcolumn that specifies what store each product belongs to, the following SQL query would suffice:
But what if Amazon wanted to let its shoppers search across multiple stores? Perhaps a user might want to search for all products with a particular name in the Books, Electronics, and DVDs stores. To accommodate this, Amazon might use a list of checkboxes, with one checkbox for each store. The user could then type in their search criteria and select the stores to search. But how would you create a SQL query to filter the products based on the user's store selections?
SQL includes an
IN keyword, which can be used in the following way:
IN keyword does not work with parameters. The following statement will fail:
The good news is that there is a (relatively) easy workaround. The
IN keyword can use a query against a table, like so:
You can build a User-Defined Function (UDF) that takes a comma-delimited string as an input and returns a table that contains one record for each input. In
Creating a User Defined Function in SQL for Comma-Delimited Searches author
Corey Aldebol shows how to build such a UDF, which he named
Split UDF takes in two
inputs - a delimited string and the delimiter character - and returns a table with one record for each value in the delimited string. Using the
you can filter the results based on a parameter, like so:
SELECT Value FROM dbo.Split(@StoreIds,',') returns a table with three rows:
For step-by-step instructions on creating and using the
Split UDF, refer to
Creating a User Defined Function in SQL for Comma-Delimited Searches.
Filtering Data Based On a CheckBoxList's Selections
The demo available for download at the end of this article illustrates how to implement such a multi-category filtering user interface in an ASP.NET page using a CheckBoxList control. The demo includes a database with two tables:
Departments. The records in the
Departmentstable define the various departments in the company - IT, Sales, Marketing, and so forth. The records in the
Employeestable model the employees at the company, and include attributes like
DepartmentID, among others.
To let users view users in one or more departments, we could build an ASP.NET page that contains a CheckBoxList control that's populated with the records from the
Departments table and a GridView control that displays the
Employees table's records whose
DepartmentID values belong to one of the
selected departments from the CheckBoxList. The screen shot below shows this demo in action. Note that each department is listed as a checkbox. The Executive and Marketing
departments are selected, which displays those employees in the Marketing and Executive departments.
The GridView in this demo is populated using a SqlDataSource control that contains the following
The query joins the
Departments tables, but only returns those employees whose
DepartmentID value is in the set of
values returned by the
Split UDF. The
Split UDF is passed in a parameter named
@DepartmentIDs, which is a comma-delimited list of
DepartmentID values selected from the CheckBoxList.
There's only one challenge left remaining - converting the CheckBoxList's selected items into a comma-delimited string and assigning that string to the
parameter. Ideally, we'd be able to use the ControlParameter to craft a comma-delimited string containing the values of the selected items in the CheckBoxList. However, the
ControlParameter doesn't offer such functionality. The good news is that we can craft and assign this comma-delimited string to the parameter programmatically, which we'll
see how to do in the next section. Following that, we'll look at building a custom parameter control to handle this for us declaratively.
Programmatically Converting a CheckBoxList's Selected Items Into a Comma-Delimited String
In Examining the Data Source Control's Events we explored the various events that are raised by the data source controls when the perform their various operations. When getting data the data source control raises its
Selectingevent, which gives us (the page developer) an opportunity to inspect or modify the request before it gets transmitted. When using a SqlDataSource control we can modify the
Commandobject from the
Selectingevent handler, which allows us to set parameter values, if needed.
In this case we need to set the
@DepartmentIDs parameter to the comma-delimited list of selected values in the CheckBoxList. This can be accomplished in the
Selecting event handler with the following code:
The first block of code enumerates the items in the
cblDepartments CheckBoxList. For each selected list item - that is, for each checked checkbox - the value
of that list item is added to a list of strings,
selectedDepartmentIDs. Once this list has been computed, the next step is to turn it into a comma-delimited
string. This is accomplished using the
String.Join method, which combines an array
of strings into a single string, delimiting each array element with a specified string (in this case, a comma).
Finally, this comma-delimited list is assigned to the
That's all there is to it! With the above code in the SqlDataSource control's
Selecting event handler, whenever the SqlDataSource goes to the database to
retrieve data it will set the value of the
@DepartmentIDs parameter to the comma-delimited string of selected items in the CheckBoxList.
Creating a Custom Parameter Control - CheckBoxListParameter
While programmatically crafting and assigning this comma-delimited string to the parameter certainly works, it requires a bit of code. One of the major benefits of the data source controls, though, is that they can be used entirely declaratively. The variety of parameter controls allow us to filter data based on querystring values, session, or other Web controls on the page without having to write a line of code, so why should it be any different when filtering against a CheckBoxList? We can obviate the need for writing code by creating our own custom parameter control.
In Creating Custom Parameter Controls, an earlier article in this series, we looked at creating custom
parameter controls. Creating a custom parameter control entails creating a class that extends the
Parameter class, which is the base class for all data source
parameter controls. The
Parameter spells out the core functionality of every parameter control. At a minimum, a parameter control must provide an
Evaluate() method that returns the value for the
Evaluate method simply returns the value for the parameter and, as such, the code for this method is typically very short and simple.
The download available at the end of this article includes a collection of custom parameter controls in a Class Library project named skmParameters. One of the parameter
controls is named CheckBoxListParameter. As its name suggests, this control operates on a CheckBoxList control and returns a comma-delimited list of the selected values in
a specified CheckBoxList. The most pertinent portions of the
CheckBoxListParameter class follow. Note that some methods have been omitted for brevity.
Selecting event handler with the following code:
CheckBoxListParameter class contains a single property named
ControlID, which the page developer uses to specify the
ID of the
CheckBoxList control to use. The
Evaluate method starts by ensuring that the
ControlID property is set and that is references a valid CheckBoxList control
on the page. After those checks the code should look similar - it's identical to the code we put in the SqlDataSource control's
Selecting event handler earlier
in this article. Namely, the code loops through the items in the CheckBoxList, adding the value of each selected item to a List of string. It then uses
to convert that List into a comma-delimited string.
To use the CheckBoxListParameter in an ASP.NET page you must first add the
skmParameters.dll file to your web application's
Bin folder. Next,
add the following
@Reference directive at the top of the pages where you intend on using the custom parameter controls:
Finally, add the appropriate declarative markup in the data source control's parameters section. For example, to use the CheckBoxListParameter to set a parameter value for the
SelectCommand, use markup like so:
Note how the CheckBoxListParameter markup defines the name of the parameter via its
Name attribute and the
ID of the CheckBoxList control on the
page via its
ControlID property. That's all there is to it - no code needed.