In this article we will cover the new feature of SQL Server 2000 and the ability to create user defined functions. We will focus on creating recursive queries in this article to extend upon previous articles.
Being the developer for this directory, I had a need to perform several recursive methods. Any time you are creating a tree like data structure like directories, org charts, etc. You will need to perform some kind of recursion. In this example, I wanted to be able to provide a count of all descendents sites under a particular category as you see on WWWCoder.com when navigating the site. Each category contains a count of descendent sites of all categories under that specific category.
In the past there were several ways to perform this, one simple method was to create a field in the categories table that would get incremented each time a site was added. This was accomplished using a recursive method in the ASP.Net code that would create a new connection to the database each time a new record was added. It did eliminate the need to call a recursive function for each time a person requested a page to display the category navigation. Basically the method performed would accept a category id and a string value to increment or decrement the count contained in the site count field:
Public Sub UpdateParentCount(ByVal ParentID As Integer, ByVal AddSubtract As String)
Dim SQLQ As String = "UPDATE Categories Set SiteCount = SiteCount " & AddSubtract & " 1"
If AddSubtract = "+" Then 'add a new icon.
SQLQ = SQLQ & ", DateSiteAdded = '" & Date.Today & "'"
End If
SQLQ = SQLQ & " WHERE CategoryID=" & ParentID
Dim secondConnection As New SqlConnection(GetDBConnectionString)
Dim secondCommand As New SqlCommand(SQLQ, secondConnection)
secondCommand.CommandType = CommandType.Text
secondConnection.Open()
secondCommand.ExecuteNonQuery()
secondConnection.Close()
secondConnection = Nothing
Dim SQLQ2 As String = "SELECT ParentID FROM Categories Where CategoryID = " & ParentID
Dim myConnection As New SqlConnection(GetDBConnectionString)
Dim myCommand As New SqlCommand(SQLQ2, myConnection)
myConnection.Open()
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
If result.Read Then
UpdateParentCount(result("ParentID"), AddSubtract)End If
myConnection.Close()
myConnection = Nothing
End Sub
This wasn't too bad of solution, since I was able to eliminate the hits on the database for the count. The problem here is a count can become incorrect if any changes are performed outside of the application.
The solution for me had to meet the following requirements: reduce traffic between the Web server and the database server, and make sure an accurate count is always available in the database regardless of what modifies the records that it contains. In order to accomplish this, the count method was moved out of the ASP.Net code and into the database. SQL Server 2000 supports User Defined Functions that can be called from a stored procedure, in addition, the function can be recursive up to 32 levels. Since I can't see a need to go beyond 32 levels deep of categories, I opted to use the functions for creating the count. Here is an example of using a function from within a stored procedure:
ALTER procedure GetCategories
@ParentID int
as
BEGIN
SELECT
CategoryID, CategoryName, Path, SiteCount, DateSiteAdded,
ParentID, SortColumn, dbo.CountChildren(CategoryID, 0)
As CulCount
FROM
Categories
WHERE
ParentID = @ParentID
ORDER BY
SortColumn, CategoryName
END
You'll notice in the SQL stored procedure's select statement there is a call to a function called CountChildren, in this function we pass the category id of the current category and the current cumulative count of the sites within the category.
ALTER FUNCTION dbo.CountChildren
(@id int, @cChildren int)
RETURNS bigint
AS
BEGIN
IF EXISTS (SELECT
Sites.SiteCatID
FROM
dbo.Categories
INNER JOIN
dbo.Sites
ON
dbo.Categories.CategoryID = dbo.Sites.SiteCatID
WHERE
dbo.Categories.ParentID = @id OR dbo.Sites.SiteCatID = @id)
BEGIN
SET @cChildren = @cChildren + (
SELECT
Count(SiteCatID)
FROM
Sites
WHERE
SiteCatID = @id AND SiteActive = 1)
SELECT
@cChildren = dbo.CountChildren(CategoryID, @cChildren)
FROM
Categories
WHERE
ParentID = @id
END
RETURN @cChildren
END
As you can see the function calls itself just as a recursive function in VB.Net would do, each time incrementing the cumulative count of all descendents of a particular category. In the end we have all the information generated on the SQL machine, and then it returns what we need without having to call a recursive method in the ASP.Net page and generate all the additional database calls over the network.
By: Patrick Santry, Microsoft MVP (ASP/ASP.NET), developer of this site, author of books on Web technologies, and member of the DotNetNuke core development team. If you're interested in the services provided by Patrick, visit his company Website at Santry.com.
Source: http://www.wwwcoder.com/
