Accessing and Updating Data in ASP.NET: Filtering Data Using a CheckBoxList

Introduction

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

SELECT ... FROM Products WHERE ProductName LIKE @ProductName AND StoreId = @StoreId

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:

SELECT ... FROM Products WHERE ProductName LIKE @ProductName AND StoreId IN (1,3,9)

Unfortunately, the IN keyword does not work with parameters. The following statement will fail:

DECLARE @StoreIds nvarchar(100) = '1,3,9' SELECT ... FROM Products WHERE ProductName LIKE @ProductName AND StoreId IN (@StoreIds)

The good news is that there is a (relatively) easy workaround. The IN keyword can use a query against a table, like so:

SELECT ... FROM Products WHERE ProductName LIKE @ProductName AND StoreId IN (SELECT StoreId FROM SomeTable)

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. The 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 Split UDF you can filter the results based on a parameter, like so:

DECLARE @StoreIds nvarchar(100) = '1,3,9' SELECT ... FROM Products WHERE ProductName LIKE @ProductName AND StoreId IN (SELECT Value FROM dbo.Split(@StoreIds,','))

The subquery SELECT Value FROM dbo.Split(@StoreIds,',') returns a table with three rows:

ID	Value
1	1
2	3
3	9

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

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 SelectCommand:

SELECT e.Name, e.Salary, d.DepartmentName FROM Employees e    INNER JOIN Departments d ON       e.DepartmentID = d.DepartmentID WHERE e.DepartmentID IN (SELECT Value FROM dbo.Split(@DepartmentIDs, ',')) ORDER BY e.Name

The query joins the Employees and 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 the 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 @DepartmentIDs 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 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:

Protected Sub dsEmployeesByDepartments_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles dsEmployeesByDepartments.Selecting    'Determine which checkboxes have been checked    Dim selectedDepartmentIDs As New List(Of String)    For Each li As ListItem In cblDepartments.Items       If li.Selected Then          selectedDepartmentIDs.Add(li.Value)       End If    Next    'Convert the List of DepartmentIDs into a comma-delimited string    Dim departmentIDsAsCommaDelimitedList As String = String.Join(",", selectedDepartmentIDs.ToArray())    'Assign departmentIDsAsCommaDelimitedList to the @DepartmentIDs parameter    e.Command.Parameters("@DepartmentIDs").Value = departmentIDsAsCommaDelimitedList End Sub

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 @DepartmentIDs parameter.

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

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 parameter. 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:

Public Class CheckBoxListParameter    Inherits Parameter    Public Property ControlID() As String       Get          Dim val As Object = MyBase.ViewState("ControlID")          If val Is Nothing Then             Return String.Empty          Else             Return val.ToString()          End If       End Get       Set(ByVal value As String)          If (Me.ControlID <> value) Then             ViewState("ControlID") = value             MyBase.OnParameterChanged()          End If       End Set    End Property    Protected Overrides Function Evaluate(ByVal context As HttpContext, ByVal control As Control) As Object       'Make sure the page developer has specified a value for the ControlID property       If String.IsNullOrEmpty(Me.ControlID) Then          Throw New ArgumentException("ControlID parameter not specified.")       End If       'Find the control referenced by the ControlID property       Dim referencedControl As Control = FindControl(control, Me.ControlID)       If referencedControl Is Nothing Then          Throw New InvalidOperationException(String.Format("Could not find CheckBoxList control '{0}'.", Me.ControlID))       ElseIf Not TypeOf referencedControl Is CheckBoxList Then          Throw New InvalidOperationException(String.Format("Control '{0}' is not a CheckBoxList control.", Me.ControlID))       End If       'Gather up the selected values of the CheckBoxList       Dim selectedDepartmentIDs As New List(Of String)       For Each li As ListItem In CType(referencedControl, CheckBoxList).Items          If li.Selected Then             selectedDepartmentIDs.Add(li.Value)          End If       Next       'Return a comma-delimited string of the selected values       Return String.Join(",", selectedDepartmentIDs.ToArray())    End Function End Class

The 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 String.Join 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:

<%@ Register Assembly="skmParameters" Namespace="skmParameters" TagPrefix="skm" %>

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 SqlDataSource control's SelectCommand, use markup like so:

<asp:SqlDataSource ID="dsEmployeesByDepartments" runat="server" ...      SelectCommand="SELECT ... FROM ... WHERE DepartmentID IN (SELECT Value FROM dbo.Split(@DepartmentIDs,','))">    <SelectParameters>       <skm:CheckBoxListParameter Name="DepartmentIDs" ControlID="cblDepartments" />    </SelectParameters> </asp:SqlDataSource>

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.

Happy Programming!