To read the article online, visit http://www.4GuysFromRolla.com/articles/012010-1.aspx

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

By Scott Mitchell


Introduction


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, @Price is 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 Products table with a StoreId column that specifies what store each product belongs to, the following SQL query would suffice:

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:

IDValue
11
23
39

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: Employees and Departments. The records in the Departments table define the various departments in the company - IT, Sales, Marketing, and so forth. The records in the Employees table model the employees at the company, and include attributes like Name, Salary, and 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 employees in the selected departments are displayed.

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 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 Selecting event, 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 Command object from the Selecting event 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:

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


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 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!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

    Further Readings:


  • Filtering Database Data with Parameters
  • Creating a User Defined Function in SQL for Comma-Delimited Searches
  • Examining the Data Source Control's Events
  • Creating Custom Parameter Controls
  • Article Information
    Article Title: ASP.NET.Accessing and Updating Data in ASP.NET: Filtering Data Using a CheckBoxList
    Article Author: Scott Mitchell
    Published Date: January 20, 2010
    Article URL: http://www.4GuysFromRolla.com/articles/012010-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers