Other Options
Before I get comments saying there are other ways of doing this, I know that.
In a previous method I provided a way of doing this using
recursive SQL user
defined functions. There are some cases where you may not be able to use
functions. I prefer functions since all the recursion is done on the SQL server
and this reduces the amount of calls to the SQL database over the network. Given
this however, you may be running Access or an older version of SQL that doesn't
support functions. Another reason is the ability to generate automated
installation scripts may be an issue when using functions.
Building SQL
This method basically creates a SQL string and then adds to the count of the
descendents. Your table structure basically consists of a category name, an id
which is your primary key, a parent id field which contains the id of the parent
category, and then a count field. In this method we are going to populate the
value of the count field anytime a site is added or removed. If you've read the
SQL function article, there is no need for a count field since this is
calculated each time a request is made for the records.
The Method
Below we have a method that is called whenever a site is deleted or added to
the category. We need to be able to update the descendent count since things
have changed.
Public Sub UpdateSiteCount(ByVal ParentID As Integer, ByVal intLevel As Integer)
Dim SQLQ, SQLQ1, SQLQ2 As String
Dim tmpLevel As Integer = intLevel
SQLQ1 = "Select CategoryID From Categories where (ParentID = " & ParentID & ")"
Dim dr As IDataReader = ExecuteSQL(SQLQ1)
While dr.Read
'build our category ids for count updating.
If tmpLevel = 0 Then
_ParentPaths = dr.GetInt32(0).ToString & ","
Else
_ParentPaths += dr.GetInt32(0).ToString & ","
End If
SQLQ = "Select Count(*) From Sites Where SiteActive = 1 AND SiteCatID = " & _
dr.GetInt32(0).ToString
'execute our SQL string using a helper method.
Dim dr2 As IDataReader = ExecuteSQL(SQLQ)
While dr2.Read
If (dr2.GetInt32(0) <> 0) Then
SQLQ2 = "Update Categories Set SiteCount = (SiteCount + " & _
dr2.GetInt32(0).ToString & ") WHERE CategoryID IN (" & _
Left(_ParentPaths, Len(_ParentPaths) - 1) & ")"
ExecuteSQL(SQLQ2)
End If
End While
dr2.Close()
dr2.Dispose()
'now call the method unto itself in order to do the recursion.
UpdateSiteCount(dr.GetInt32(0), tmpLevel + 1)
End While
_ParentPaths = Trim(Replace(_ParentPaths, ("," & ParentID.ToString & ","), ","))
dr.Close()
dr.Dispose()
End Sub
Looking at the method above you can see it accepts a parent id, and the
current level. Current level is for letting us know at what point in the
recursion we are at, meaning is the first call to the method or is it higher
than that. Parent id provides us with the current category id, each time we loop
though and get a category, we pass the id of the current category to the method
in order to get any direct child categories of it.
You can see the first thing we do is build a SQL string to get any category
ids of children of the parent id passed to the method. We then loop through
these records, as we're looping through the records we build another SQL string,
this time it is an Update which is going to update the cumulative count of
descendents for our categories. If you look at the string being generated here
you can see how this count is going to be cumulative, basically we created a
variable that is global to the class and for each recursive call we append the
category id to it. This variable is the _parentPaths variable that is being
concatenated at the very beginning of the loop. Since we have this string of ids
we then update the count of all records that are in that pool of ids contained
in the _parentPaths variable. Each time a new category id is being added for the
child, sine the previous category ids are already in the string they get their
count updated as well for each iteration of the method.
We then use a helper method to execute our SQL string in order to perform our
update. This method we won't discuss here, but it basically executes a SQL
command. The main point of this article is building the SQL string required in
order to update our count.
Finally the we run out of records to update and the method will eventually
end.
There are some drawbacks to this method as described earlier and you may want
to consider using
SQL functions or whatever your infrastructure permits. Performance can
seriously be degraded for a method like this, you have to make several calls to
the database over the network in order to perform this operation. You may want
to consider running a method like this as a batch job rather than on every
update.
