ASP.NET 2.0 Instant Results
.pdf
Customer Support Site
children and their children. If you select the Power Printers from the second drop-down, you’ll see records that belong to the root category Rocks Hardware (like the Warranty Card), the Power Printers, and all of its child categories. The list no longer displays records that are linked to the 3D Printers category. Finally, if you select the 850 T5 from the last drop-down, you’ll see the downloads that are linked to that category directly, or to its parent or grandparent but no longer those related to the 850 or V70 category.
Hierarchical data selection as in the preceding example has always been difficult in SQL Server, until the release of SQL Server 2005, which introduces a concept called Common Table Expressions (CTE). A CTE is a temporary result with a name that can be used in other expressions and code. It’s a bit like an in-memory table that you can link to other tables. The good thing about CTEs is that they support recursion, which allows you to perform very powerful queries with just a few lines of code.
To see CTEs at work, you need to take one step back and look at the source for the ObjectDataSource control in the Downloads page and see how it gets its data:
<asp:ObjectDataSource ID=”odsDownloads” runat=”server” SelectMethod=”GetDownloadList” TypeName=”Download”>
<SelectParameters>
<asp:ControlParameter ControlID=”lstCategoryLevel1” Name=”categoryId”
PropertyName=”SelectedValue” Type=”Int32” />
<asp:Parameter Direction=”InputOutput” Name=”recordsAffected” Type=”Int32” /> </SelectParameters>
</asp:ObjectDataSource>
So far, not much is new. The data source control is linked to the GetDownloadList method in the Download class. You also see a recordsAffected parameter that returns the number of products returned from the database. You see where this is used later.
GetDownloadList gets its records from a method with the same name in the DownloadDB class, counts the number of records and assigns that to an output parameter, and then returns the DataSet like this:
Public Shared Function GetDownloadList(ByVal categoryId As Integer, _ ByRef recordsAffected As Integer) As DataSet
Dim dsDownloads As DataSet = DownloadDB.GetDownloadList(categoryId) recordsAffected = dsDownloads.Tables(0).Rows.Count
Return dsDownloads End Function
The GetDownloadList method in the DownloadDB class has code similar to the GetProductList method you saw earlier. It’s the stored procedure that gets the requested downloads where things get interesting (and a bit more complicated). Take a look at the SELECT statement for that procedure:
SELECT TOP 100 Id,
Title,
Description,
CategoryId,
DownloadUrl FROM
Download WHERE
CategoryId IN
(
257
Chapter 8
SELECT DISTINCT Id FROM fnSelectChildCategories(@categoryId) UNION
SELECT DISTINCT Id FROM fnSelectParentCategories(@categoryId)
)
OR @categoryId IS NULL ORDER BY
Title
The first part, the SELECT and the FROM clause, looks pretty normal, and so does the ORDER BY clause. It’s the WHERE clause that looks odd. First of all, you see an IN statement. The IN statement in the T-SQL language is a convenient way to select multiple records; for example, by their ID. The following SELECT statement returns downloads in the categories with in ID of 3, 7, or 8:
SELECT Id, Description FROM Download WHERE CategoryId IN (3, 7, 8)
The second part of the WHERE clause uses a UNION statement to combine the results of the two inner SELECT statements. Ignoring the actual implementation of the two SELECT statements for now, assume that the first SELECT returns something like 3, 7, 8 and the other SELECT returns 4, 7, 9. The end result for the outer WHERE clause is then
WHERE CategoryId IN (3, 4, 7, 8, 9)
Notice that the duplicate values (7) have been removed automatically. If you don’t want that to happen, use UNION ALL instead.
Now on to the hardest part: fnSelectChildCategories and fnSelectParentCategories. These two are user-defined functions (UDFs) that return a table to the calling code. They also accept a parameter of type int. These functions are capable of returning the parent IDs or the child IDs plus its own ID of a given category. So, given Figure 8-14, imagine you called fnSelectParentCategories with the ID of the category V70; you’ll get the IDs of the categories V70, Power Printers, and Rocks Hardware. To see how this works, take a look at the fnSelectParentCategories function that returns a category’s parents:
CREATE FUNCTION fnSelectParentCategories
(
@categoryId int
)
RETURNS @theCategoryTable TABLE (Id int, Description nvarchar(100))
AS
BEGIN
WITH CategoriesCte(Id, Description, ParentCategoryId) AS
(
SELECT Id, Description, ParentCategoryId FROM Category
WHERE Id = @categoryId
UNION ALL
SELECT C.Id, C.Description, C.ParentCategoryId
258
Customer Support Site
FROM Category AS C
JOIN CategoriesCte AS E
ON C.Id = E.ParentCategoryId
)
INSERT INTO @theCategoryTable (Id, Description)
SELECT Id, Description FROM CategoriesCte
RETURN
END
First, the function signature is defined, with the function’s name and its input parameter called @categoryId. The RETURNS statement that follows tells calling code that this function returns a table object with an ID and a Description column. The table returned from this function can be used like any other normal table; you can select from it, join it with other tables, and so on.
The second part of the function might be new to you, so it’s important to look at it closely. The WITH statement indicates the start of a Common Table Expression (CTE). This code example shows a recursive CTE, although it’s also possible to use CTEs without recursion.
A recursive CTE consists of two parts: the anchor member and the recursive member. In the preceding code, the first SELECT statement is the anchor member. When the function is executed, this SELECT statement is fired first. Then for each record in the result set of that expression, the recursive member is triggered. Then for each record that the recursive member added to the result set, the recursive member is triggered again, until no more records are encountered.
Given the example of the V70 printer again, look at Figure 8-15, which displays the results from the CTE for the category V70.
Id |
ParentCategoryId |
Category |
Member |
67 |
64 |
V70 |
Anchor |
|
|
|
First recursion |
64 |
55 |
Power Printers |
Recursive |
|
|
|
Second recursion |
55 |
null |
Rocks Hardware |
Recursive |
Figure 8-15
When the anchor member’s SELECT statement runs, it adds the first record to the result set with an ID of 67 and a ParentCategoryId of 64. The recursive member then runs, and selects the categories whose ID matches the ParentCategoryId of the V70 record. This is only one record, the one for the Power Printers, which has an ID of 64 and a ParentCategoryId of 55. This record is also added to the result set. The
259
Chapter 8
SELECT statement is then repeated for the record that has just been added, and this time it selects the parent category for the Power Printers record, which results in the Rocks Hardware category being added to the result set.
The function fnSelectChildCategories, which selects a category’s child records, works in pretty much the same way. However, because a parent category can have multiple child records that in turn can have even more child records, the result set that is returned is likely to be larger.
The stored procedure that selects the downloads from the database selects both the child records and the parent records using a UNION statement. This way, the full path of a category is returned, including its parent and grandparent, and all of its children and their children. If you only want to retrieve the child records for a category, remove the UNION statement and the line that selects from the fnSelect ParentCategories function from the stored procedure sprocDownloadSelectList.
Now that you have seen how the stored procedure sprocDownloadSelectList gets its records from the database, the next part you need to look at is how the Downloads page is able to figure out for which selected drop-down it should return records. Take another look at the ObjectDataSource that gets the downloads, but this time focus on the <SelectParameters> node that has a single <asp:ControlParameter>:
<asp:ObjectDataSource ID=”odsDownloads” runat=”server” SelectMethod=”GetDownloadList” TypeName=”Download”>
<SelectParameters>
<asp:ControlParameter ControlID=”lstCategoryLevel1” Name=”categoryId” PropertyName=”SelectedValue” Type=”Int32” />
<asp:Parameter Direction=”InputOutput” Name=”recordsAffected” Type=”Int32” /> </SelectParameters>
</asp:ObjectDataSource>
Initially, the ControlID of the ControlParameter is set to lstCategoryLevel1. This means that if you choose an item from the first drop-down, the page will refresh and you’ll see the downloads that belong to that category and all of its child categories using the recursive CTE you just saw. However, when you then select a category from the second drop-down, the downloads list should display records that are connected to that category instead. The code in the code-behind for the page is responsible for that:
Protected Sub lstCategoryLevel2_SelectedIndexChanged(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles lstCategoryLevel2.SelectedIndexChanged
If Not lstCategoryLevel2.SelectedValue = “” Then
‘Enable the third drop-down lstCategoryLevel3.Visible = True
‘Next, bind the odsDownloads to this drop-down. Dim myParam As ControlParameter = _
CType(odsDownloads.SelectParameters(0), ControlParameter) myParam.ControlID = “lstCategoryLevel2”
lstCategoryLevel3.DataBind() Else
Dim myParam As ControlParameter = _ CType(odsDownloads.SelectParameters(0), ControlParameter)
myParam.ControlID = “lstCategoryLevel1” lstCategoryLevel3.Visible = False
End If End Sub
260
Customer Support Site
This code fires when you select a different option in the second drop-down. Inside the event handler for SelectedIndexChanged of that drop-down the SelectedValue is checked. If there is a value (which means a valid category was chosen), the SelectParameter you saw earlier for the ObjectDataSource is dynamically changed to the second drop-down:
Dim myParam As ControlParameter = _ CType(odsDownloads.SelectParameters(0), ControlParameter)
myParam.ControlID = “lstCategoryLevel2”
The first line of code gets a reference to the first SelectParameter of the data source control (the second parameter is an output parameter used to find out how many records were returned from the database). The SelectParameters collection returns a more generic Parameter object, so CType is used to cast it to the appropriate ControlParameter type. Once myParam contains a ControlParameter, you can access its ControlID property and assign it the ID of the second drop-down. This causes the ObjectDataSource to get the SelectedValue from that drop-down, which is then passed to its SelectMethod, the Get DownloadList method of the Download class. This in turns causes the DataList to display the downloadable files that are related to the chosen category.
This same principle is repeated for the third and first drop-down as well. This way, you can be sure that the DataList always displays records that are related to the category chosen in the last affected drop-down.
This code example shows that the code you define for controls in the page’s markup is not set in stone. You can easily modify the controls at run time using code in various events and methods in the code-behind. This can be very useful if you want to change a page’s behavior at run time.
The final part in the Downloads page you need to look at is the Selected event of the odsDownloads control. This event fires when the control is done retrieving data from its data source and is an ideal place to display a message to the user indicating if and how many records were returned from the database. The GetDownloadList method in the Download class has an output parameter (indicated by the keyword ByRef) that returns the number of affected records to the calling code. Inside the Selected event for the data source, this output parameter is retrieved from the OutputParameters collection of the ObjectDataSourceStatusEventArgs argument passed to the method:
Protected Sub odsDownloads_Selected(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles _ odsDownloads.Selected
Dim recordsAffected As Integer = _ Convert.ToInt32(e.OutputParameters.Item(“recordsAffected”))
‘ The rest of the code is omitted End Sub
The remainder of the code in the event handler is used to build up a message that is shown on the page to tell the user how many records were found.
This concludes the discussion of the Downloads page. With this page in place, users can browse the list of downloads, filtering the number of downloads to those they are most interested in.
Another feature of the site that allows the user to search for content is the Frequently Asked Questions page, which is discussed next.
261
Chapter 8
Searching Frequently Asked Questions
The page with the frequently asked questions behaves differently from the pages you have seen so far. Instead of drilling down in the list of FAQs using drop-downs for the categories, the FAQs page allows a user to search the entire FAQs table with a Boolean query that supports AND and OR logic. So searching for driver AND failure brings up all frequently asked questions that contain the words driver and failure, whereas searching for driver OR failure brings up the FAQs that have at least one of those words in them.
The commercial versions of SQL Server 2005 support a concept called Full Text Indexing. This is a very smart search technology enabling you to ask much more sophisticated questions than simple Boolean queries. However, Full Text Indexing is not available in the Express Edition of SQL Server, so you’ll have to pay for the full version if you want to use this feature. Search the SQL Server books online or search Microsoft’s MSDN web site (http://msdn.microsoft.com) for the article “SQL Server 2005 Full-Text Search: Internals and Enhancements” for more information about Full Text Indexing.
The markup of the FAQs page is very simple. It contains some introduction text, a text box for the search term, a button to initiate the search action, and two placeholders that display a message to the user about the number of results found. It also contains a DataList control that displays the frequently asked questions and the answers. You might notice the absence of a data source control in the markup of the page. The page doesn’t have one, and all data binding is done in the code-behind of the page, in the button’s Click event:
Protected Sub btnSearch_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles btnSearch.Click dlFaqs.DataSource = Faq.GetFaqList(txtSearchTerm.Text) dlFaqs.DataBind()
If dlFaqs.Items.Count > 0 Then lblSearchedFor2.Text = txtSearchTerm.Text plcRecords.Visible = True
plcNoRecords.Visible = False Else
lblSearchedFor1.Text = txtSearchTerm.Text plcNoRecords.Visible = True plcRecords.Visible = False
End If End Sub
This code calls the GetFaqList method of the Faq class. That method, which is examined in a moment, returns a DataSet that is assigned to the DataList control’s DataSource property. Because no Object DataSource controls are used that handle data binding automatically, you have to explicitly call DataBind on the DataList. This causes the DataList to be rendered, displaying the FAQs returned from the database. After DataBind has been called, you can check out the number of items currently being displayed by the DataList, by looking at the Count property of its Items collection. When Count is zero, the page shows the <asp:PlaceHolder> called plcNoRecords that holds a message telling the user no FAQs were found. To show the user what he searched for, the label lblSearchedFor1 is updated with the search term.
262
Customer Support Site
Instead of binding the control directly in the code-behind using dlFaqs.DataBind(), you could also use one of the data source controls, like an ObjectDataSource.
However, using such a control would have meant a lot more work implementing the required functionality on the page. First of all, you’d need to create an <asp: Parameter> to feed the search term to the control. You’d also need to find a way to stop the control from executing its Select operation when the page first loads. Finally, you’d need to write code in the event handler for its Selected event (which fires after the control has selected the data from the data source) to determine if any records have been returned from the data source so you can hide and display the appropriate panels. Using DataBind directly on the DataList control in the code-behind solves all these problems in one fell swoop. Chapter 12 shows you how to use an ObjectData Source control’s Selected event to get useful information about the data returned by the control after it has finished with its Select method.
When the DataList does contain records, the reverse action is performed. The plcNoRecords placeholder is hidden and plcRecord is shown instead.
As you might expect, GetFaqList of the Faq class simply calls the GetFaqList method in the FaqDB class to get the frequently asked questions from the database. This method is worth examining closely, because it is different from other data access code you have seen so far. All the stored procedures you have seen so far were self-contained. That is, they contained complete SQL statements that are optionally fed one or more parameters that control the WHERE clause. However, because a user can search the site with the aforementioned Boolean query syntax, a normal WHERE clause doesn’t work. Instead the code in the FaqDB class builds up the WHERE clause dynamically, and passes it as a parameter to the stored procedure. That procedure then uses SQL Server’s EXEC method to execute a dynamic SQL statement that includes the WHERE clause. To see how this works, the process is explained step by step. First, take a look at how the final SQL statement should end up. Assume a user searched for driver AND failure because she wanted to see all the FAQs that contain these terms. The SELECT statement for this search should end up like this:
SELECT
Id,
QuestionShort,
QuestionLong, Answer
FROM Faq WHERE
(
QuestionShort LIKE ‘%driver%’ OR QuestionLong LIKE ‘%driver%’ OR Answer LIKE ‘%driver%’
)
AND
(
QuestionShort LIKE ‘%failure%’ OR QuestionLong LIKE ‘%failure%’ OR Answer LIKE ‘%failure%’
)
Because of the dynamic nature of the search term, you cannot simply replace %driver% and %failure% with two parameters to the stored procedure to make the query dynamic. What if the user searched for driver AND failure AND Power Printer? Instead of two parameters, you now need three. The
263
Chapter 8
answer to this problem is not to make the search terms dynamic, but the entire WHERE clause. This is done with the BuildWhereClause method that is called in the code for the GetFaqList method:
Private Shared Function BuildWhereClause(ByVal searchTerm As String) As String Dim simpleSearch As Boolean = True
Dim whereClause As String = String.Empty
searchTerm = searchTerm.Trim()
searchTerm = searchTerm.Replace(“‘“, “‘’”) searchTerm = searchTerm.Replace(“”””, “”) searchTerm = searchTerm.Replace(“%”, “”) searchTerm = searchTerm.Replace(“--”, “”) searchTerm = searchTerm.Replace(“;”, “”) searchTerm = searchTerm.Replace(“(“, “”) searchTerm = searchTerm.Replace(“)”, “”) searchTerm = searchTerm.Replace(“_”, “”)
Dim testReplace As String = “”
testReplace = searchTerm.ToUpper().Replace(“ AND “, “”) If testReplace <> searchTerm.ToUpper() Then
simpleSearch = False End If
testReplace = searchTerm.ToUpper().Replace(“ OR “, “”) If testReplace <> searchTerm.ToUpper() Then
simpleSearch = False End If
If simpleSearch = True Then
searchTerm = searchTerm.Replace(“ “, “ AND “) End If
Dim myAndSplits() As String = Regex.Split(searchTerm, “ and “, _
RegexOptions.IgnoreCase)
For i As Integer = 0 To myAndSplits.Length - 1
Dim myOrSplits() As String = Regex.Split(myAndSplits(i), “ or “, _ RegexOptions.IgnoreCase)
whereClause += “(“
For j As Integer = 0 To myOrSplits.Length - 1
whereClause += “(F.QuestionShort LIKE ‘%” & myOrSplits(j) & “%’ OR _ F.QuestionLong LIKE ‘%” & myOrSplits(j) & “%’ OR F.Answer LIKE ‘%” & _ myOrSplits(j) & “%’)”
If (j + 1) < myOrSplits.Length Then whereClause += “ OR “
End If Next
whereClause += “) “
If (i + 1) < myAndSplits.Length Then
whereClause += “ AND “
264
