Tip 12: Allowing for Variable Selections
Despite your best efforts, users have come back with a requestin addition to filtering on demographic information, they also want to filter on customer accounting statuses (paid to date, overdue 30 days, etc.) from a lookup table. The end user may want to select one status, more than one status, or all of them. The status code exists in the Customer
|An aesthetically-pleasing Web page design will attract users. A Web page built for navigation will keep them coming back.|
This will be a nice little exercise to repeat the process of building a plan of attack. The exercise also demonstrates a reusable technique for handling a variable number of selections.
- You'll modify the Web page to include a data-bound control capable of processing multiple selections. Though some developers may opt for a sophisticated third-party control, this example will use a simple ASP.NET 2.0 CheckedListBox Web control.
- You'll write code to read the CheckedListBox control for selected values into an XML string.
- You'll modify the data access class to pass this XML string as a parameter to the stored procedure.
- Finally, you'll modify the stored procedure code to handle the XML parameter.
Since these tasks began with the user interface and ended with the database, you might be tempted to make the changes in that order. However, there's a subtle level of increased efficiency by starting from the back-end and working forward. Design from the outside-in, and then build from the inside-out.
First add an XML parameter to the stored procedure. Again, the XML string will contain one column for statuses that the user selects. SQL Server 2005 supports a new XML data type, which makes this process quite easy. The stored procedure will convert the XML string to a table variable, for a subsequent join into the Customer table.
Prior to SQL Server 2005, developers often converted XML selections to a SQL table by using the sp_xml_preparedocument
system stored procedure and the OPENXML
function. While still functional, this methodology has two issues. First, it can be memory-intensive. The stored procedure sp_xml_preparedocument
returns a memory pointer to a COM XML document object model, so using OPENXML
on large XML documents can tax a server. Second, developers cannot use sp_xml_preparedocument
inside a T-SQL User-defined function, making it difficult to develop generic, reusable SQL functionality for this task.
Included in all the new XML functionality in SQL Server 2005 is a general capability called XQuery and a specific method called nodes()
. These allow developers to strip out, or shred, specific pieces of data from an XML string into a table. This functionality uses fewer resources and you can accomplish the task in one line of code.
contains a T-SQL 2005 table-valued UDF called XMLtoTable
, which takes an XML string as a parameter and returns a table variable containing the integer keys that were stored in the XML string in the IDpk
column. If you want the name of the selected column in the XML string to be variable, you can modify the UDF to pass the name of the column as a parameter.
The code in Listing 4
uses the nodes()
method to query starting from the IDpk
node of the XML string and place the result into an alias with a table/(column) format. From there, the SELECT
statement uses the value function to read the value of IDpk
into an integer result set.
INSERT INTO @tPKList
SELECT Tbl.col.value('.','int') as IntPK
FROM @XMLString.nodes('//IDpk' ) Tbl(col)
You can now incorporate the UDF into the main query against the Customers
-- uses the new parameter XMLString
-- that contains the list of statuses
SELECT <column list>
JOIN [dbo].[XMLtoTable](@XMLString) StatusList
ON StatusList.IntPK = customers.statusfk
Now that you've tackled the database end, the remaining pieces are even easier. You can modify the data access class in Listing 2
to include the new parameter.
// code also adds XMLStatuses as a parameter
// to GetCustomers in Listing 4
new SqlParameter("@XMLString", XMLStatuses));
You're almost there! In the last step you'll handle the Web page by populating a CheckedListBox control and then reading from it.
The Web page needs code to populate the control. For simple demonstration purposes, the code will populate a DataTable manuallyin production, this would pull from a data class that reads the back-end database for customer status codes.
DataTable dtStatus = new DataTable();
dtStatus.Rows.Add(1, "Up to Date");
dtStatus.Rows.Add(2, "Overdue 30 days");
dtStatus.Rows.Add(3, "Overdue 60 days");
dtStatus.Rows.Add(4, "Overdue 90 days");
dtStatus.Rows.Add(5, "Overdue 120 days");
dtStatus.Rows.Add(6, "Account suspended");
// Set the data binding, and the text/value fields
this.chkStatusList.DataSource = dtStatus;
this.chkStatusList.DataTextField = "descript";
this.chkStatusList.DataValueField = "statuspk";
Finally, you'll need code to read the CheckedListBox for items that the user selected and return an XML string. The code below creates a temporary DataSet, reads through the collection of items in the control, and inserts selected items into the temporary DataSet. The code then returns an XML representation of the temporary DataSet containing the selections, using the GetXML
private string GetStatuses()
DataTable dtSelected = new DataTable();
foreach (ListItem oItem in this.chkStatusList.Items)
if (oItem.Selected == true)
DataSet ds = new DataSet();