Extracting the User Selections

After the user has made their selections, you need to extract the information selected and pass it to your data source. Because all the controls are managed via classes, you can accomplish this easily and generically using one function call. Just as the list box was displayed with two lines of code, each of the selected values can be retrieved with one. For example string szDepartments = GetCriteria(Criteria.Department);

assigns a value to the szDepartments variable that looks like this: "(12,45,23)". This is a comma-delimited string that can be used in a SQL IN clause to restrict the departments that are extracted from the database. The GetCriteria() method is partially shown in Listing 4-36. This section shows how the ListBoxManager is handled. Each element in the ArrayList is checked for its class type and then cast to that type. If the Index property, which identifies the control to which it's associated, matches the enumerated value passed to it, then you extract the selected data in a fashion appropriate to the control. All the other control collection types are handled similarly.

Listing 4-36. GetCriteria() Method

ListBoxManager oListBoxManager = null; CheckedListBox oListBox = null; string szData = string.Empty;

foreach(object oltem in aControlList) {

if (oItem is ListBoxManager) {

oListBoxManager = ((ListBoxManager) oltem);

return szData;

The selection-extraction approach appropriate to a ListBox control is the ParseIt() method shown in Listing 4-37. This method receives a CheckedListBox control and a Boolean indicating whether quotes should surround each item, which column—0 (ID) or 1 (description)—to return, and another Boolean to indicate if all or only the checked items are returned.

Listing 4-37. ParseIt() Method public static string ParseIt(CheckedListBox oList, bool bQuotes, int sCol, bool bCheckedOnly)

string szResult = String.Empty; string szQuotes = String.Empty; string szData = String.Empty;

System.Text.StringBuilder oResult = new System.Text.StringBuilder(); //use quotes or not szQuotes = (bQuotes) ? : String.Empty;

//count all or just the checked items IList oListItems = (bCheckedOnly) ?

(IList)oList.CheckedItems : (IList)oList.Items;

foreach (object oItem in oListItems) {

ListItem oCheckBoxItem = oItem as ListItem;

(oListBoxManager.Index == iIndex)

oListBoxManager = ((ListBoxManager) oItem);

oListBox = ((CheckedListBox) oListBoxManager.ListBoxControl);

szData = ParseIt(oListBox, false, 0, true); break;

//depending on the column selected, extract the requested property switch (sCol) {

case 0: szData break;

oCheckBoxItem.Value;

case 1:

szData = oCheckBoxItem.Text; break;

case 2:

szData = oCheckBoxItem.OtherText; break;

if (oResult.L ength > 0) oResult.Append(",");

oResult.AppendFormat("{0}{1}{2}", szQuotes, szData, szQuotes);

szResult = (oResult.Length == 0) ? oResult.ToString() : string.Format("({0})", oResult.ToString());

return szResult;

To allow the ParseIt() method to handle ListBox, instead of CheckedListBox, controls, you overload it so that the first parameter receives an object of type ListBox and, when iterating through the chosen options, refer to the SelectedItems collection instead of the CheckedItems collection. That's the only difference.

After the data as been extracted from the control, you can pass it to a stored procedure. There is a catch here, however. SQL Server, Sybase, and Oracle stored procedures, among others, don't allow you to pass a parameter to an IN clause as shown in Listing 4-38.

Listing 4-38. Invalid Passing of a Parameter to an IN Clause DECLARE @Data Varchar(1000) SET @Data = '1,2'

SELECT * FROM Employees WHERE EmployeeID IN @Data

Therefore, you need to parse the data using a SQL Server function. Avoid using dynamic SQL if at all possible; it's slow, and you're just asking for problems. The way to handle this situation in SQL Server is shown in Listing 4-39.

Listing 4-39. SQL Server Function to Parse to a Virtual Table

DECLARE @Data Varchar(l000)

SELECT * FROM Employees WHERE EmployeeID IN (SELECT data FROM dbo.fnc_NumericCodes(@Data, ','))

The function fnc_NumericCodes returns a virtual table containing the parsed values from the delimited string passed into it. You can see this by executing the code in Listing 4-40.

Listing 4-40. Extracting a Virtual Table

DECLARE @Data Varchar(l000)

SELECT data

FROM dbo.fnc_NumericCodes(@Data, ',')

This produces the result set shown in Figure 4-11.

Figure 4-11. fnc_NumericCodes result set

The IN clause performs a subquery on this result set, which returns the matching rows in the Employee table. The code for this stored procedure is shown in Listing 4-41.

Listing 4-41. fnc_NumericCodes Function CREATE FUNCTION dbo.fnc NumericCodes

@Items varchar(4000),

^Delimiter varchar(l) )

RETURNS @DataTable TABLE (data int) AS

BEGIN

DECLARE @Pos int

DECLARE @DataPos int

DECLARE @DataLen smallint

DECLARE @Temp varchar(4000)

DECLARE @DataRemain varchar(4000)

DECLARE @OneItem varchar(4000)

BEGIN

SET @DataLen = 4000 - DATALENGTH(@DataRemain) / 2

SET @Temp = @DataRemain + SUBSTRING(@Items, @DataPos, @DataLen)

SET @DataPos = @DataPos + @DataLen

SET @Pos = CHARINDEX(@Delimiter, @Temp)

BEGIN

INSERT @DataTable (data) VALUES(@OneItem)

SET @Temp = SUBSTRING(@Temp, @Pos + 1, LEN(@Temp)) SET @Pos = CHARINDEX(@Delimiter, @Temp)

SET @DataRemain = @Temp

SET @DataRemain = @Items

INSERT @DataTable(data) VALUES (LTRIM(RTRIM(@DataRemain))) RETURN

fnc_NumericCodes is a table-valued function that receives a string of delimited values and a delimiter character (usually a comma). By iterating through this string, it breaks off each distinct value and inserts it into a table variable. As the function name suggests, it only returns numeric values, because the table variable is defined with a column called data that is of type int. You may wish to perform an IN search on a list of string values also. The code for this is very similar and is included in the download code for the book.

You can accomplish the same results as an IN clause by using CHARINDEX (or INSTR in Oracle). Both examples are shown in Listing 4-42.

Listing 4-42. Using CHARINDEX and INSTR 'SOL Server

DECLARE @Data varchar(l000)

SELECT EmployeeID FROM Employees

WHERE CHARINDEX(',' + CONVERT(varchar(l0), EmployeeID) + ',', @Data) <> 0

'Oracle

SELECT EmployeeID FROM Employees

WHERE INSTR(',1,2,3,',','||EmployeeID||',') <> 0

Each of these SQL statements returns the rows that match on the indicated column value. Because these approaches use string values and convert numeric table data to strings to perform the match, they aren't as efficient as subquerying numeric values against a numeric column in a temporary table. Test both approaches to see which one works best for you.

0 0

Post a comment