VB.NET SQL-powered Sitemap

[JamieHowarth0]

Hi guys,

So the ever-lasting problem has remained until now - how to create a dynamic sitemap from information stored in a database.

Microsoft's MSDN magazine covered how to do this using a custom-made class in C#, specific table architecture and a couple of database triggers (plus how to deal with caching on SQL Server 7 and 2000 - MSSQL 2K5 automatically deals with cache dependencies which handles new sitemap layout and/or content, which is funky). The article can be found here.

However, I don't work with C#, I work in VB and VB.NET - so I have no idea what any of this code reads, and not only that, but all my sites and custom apps (and future shrink-wrap product line) run in VB/VB.NET - and my understanding is that each project can only work in one language. So how do I take advantage of this great new piece of kit?

Sanjay Uttam translated the entire piece of code from C# to VB.NET - and it does exactly what it says on the tin - creates a sitemap that ASP.NET's SiteMap functionality can work with, so you can link it to TreeView, Menu and SiteMapPath controls, and dynamically update the SiteMap content by storing the information in any SQL relational database structure.

Sanjay's original article can be found here, many thanks to him for taking the time and effort to make a wonderful piece of kit even more accessible and useful to countless numbers of developers worldwide.

The only edit I have made is in the ReplaceNullRefs function at line 242 - in Sanjay's article the function is checking the field index integer itself for null value, and returning the integer itself, not the data in the field that the integer points to.

Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Collections.Specialized
Imports System.Configuration
Imports System.Web.Configuration
Imports System.Collections.Generic
Imports System.Configuration.Provider
Imports System.Security.Permissions
Imports System.Data.Common
Imports System.Data
Imports System.Web.Caching
 
<SqlClientPermission(SecurityAction.Demand, Unrestricted:=True)> _
Public Class SqlSiteMapProvider
    Inherits StaticSiteMapProvider
 
    Private Const _errmsg1 As String = "Missing node ID"
    Private Const _errmsg2 As String = "Duplicate node ID"
    Private Const _errmsg3 As String = "Missing parent ID"
    Private Const _errmsg4 As String = "Invalid parent ID"
    Private Const _errmsg5 As String = "Empty or missing connectionStringName"
    Private Const _errmsg6 As String = "Missing connection string"
    Private Const _errmsg7 As String = "Empty connection string"
    Private Const _errmsg8 As String = "Invalid sqlCacheDependency"
    Private Const _cacheDependencyName As String = "__SiteMapCacheDependency"
 
    Private _connect As String
    ' Database connection string
 
    Private _database As String, _table As String
    ' Database info for SQL Server 7/2000 cache dependency
 
    Private _2005dependency As Boolean = False
    ' Database info for SQL Server 2005 cache dependency
 
    Private _indexID As Integer, _indexTitle As Integer, _indexUrl As Integer, _indexDesc As Integer, _indexRoles As Integer, _indexParent As Integer
    Private _nodes As New Dictionary(Of Integer, SiteMapNode)(16)
    Private ReadOnly _lock As New Object()
    Private _root As SiteMapNode
 
    'Added...Declare an arraylist to hold all the roles this menu item applies to
    Public roles As New ArrayList
 
    Public Overloads Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        ' Verify that config isn't null
        If config Is Nothing Then
            Throw New ArgumentNullException("config")
        End If
 
        ' Assign the provider a default name if it doesn't have one
        If [String].IsNullOrEmpty(Name) Then
            Name = "SqlSiteMapProvider"
        End If
 
        ' Add a default "description" attribute to config if the
        ' attribute doesnt exist or is empty
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "SQL site map provider")
        End If
 
        ' Call the base class's Initialize method
        MyBase.Initialize(Name, config)
 
        ' Initialize _connect
        Dim connect As String = config("connectionStringName")
 
        If [String].IsNullOrEmpty(connect) Then
            Throw New ProviderException(_errmsg5)
        End If
        config.Remove("connectionStringName")
 
        If WebConfigurationManager.ConnectionStrings(connect) Is Nothing Then
            Throw New ProviderException(_errmsg6)
        End If
 
        _connect = WebConfigurationManager.ConnectionStrings(connect).ConnectionString
 
        If [String].IsNullOrEmpty(_connect) Then
            Throw New ProviderException(_errmsg7)
        End If
 
        ' Initialize SQL cache dependency info
        Dim dependency As String = config("sqlCacheDependency")
 
        If Not [String].IsNullOrEmpty(dependency) Then
            If [String].Equals(dependency, "CommandNotification", StringComparison.InvariantCultureIgnoreCase) Then
                SqlDependency.Start(_connect)
                _2005dependency = True
            Else
                ' If not "CommandNotification", then extract database and table names
                Dim info As String() = dependency.Split(New Char() {":"c})
                If info.Length <> 2 Then
                    Throw New ProviderException(_errmsg8)
                End If
 
                _database = info(0)
                _table = info(1)
            End If
 
            config.Remove("sqlCacheDependency")
        End If
 
        ' SiteMapProvider processes the securityTrimmingEnabled
        ' attribute but fails to remove it. Remove it now so we can
        ' check for unrecognized configuration attributes.
 
        If config("securityTrimmingEnabled") IsNot Nothing Then
            config.Remove("securityTrimmingEnabled")
        End If
 
        ' Throw an exception if unrecognized attributes remain
        If config.Count > 0 Then
            Dim attr As String = config.GetKey(0)
            If Not [String].IsNullOrEmpty(attr) Then
                Throw New ProviderException("Unrecognized attribute: " + attr)
            End If
        End If
    End Sub
 
    Public Overloads Overrides Function BuildSiteMap() As SiteMapNode
        SyncLock _lock
            ' Return immediately if this method has been called before
            If _root IsNot Nothing Then
                Return _root
            End If
 
            ' Query the database for site map nodes
            Dim connection As New SqlConnection(_connect)
 
            Try
                Dim command As New SqlCommand("proc_GetSiteMap", connection)
                command.CommandType = CommandType.StoredProcedure
 
                ' Create a SQL cache dependency if requested
                Dim dependency As SqlCacheDependency = Nothing
 
                If _2005dependency Then
                    dependency = New SqlCacheDependency(command)
                ElseIf Not [String].IsNullOrEmpty(_database) AndAlso Not String.IsNullOrEmpty(_table) Then
                    dependency = New SqlCacheDependency(_database, _table)
                End If
 
                connection.Open()
                Dim reader As SqlDataReader = command.ExecuteReader()
                _indexID = reader.GetOrdinal("ID")
                _indexUrl = reader.GetOrdinal("Url")
                _indexTitle = reader.GetOrdinal("Title")
                _indexDesc = reader.GetOrdinal("Description")
                _indexRoles = reader.GetOrdinal("Roles")
                _indexParent = reader.GetOrdinal("Parent")
 
                If reader.Read() Then
                    ' Create the root SiteMapNode and add it to the site map
 
                    _root = CreateSiteMapNodeFromDataReader(reader)
                    AddNode(_root, Nothing)
 
                    ' Build a tree of SiteMapNodes underneath the root node
                    While reader.Read()
                        ' Create another site map node and add it to the site map
                        Dim node As SiteMapNode = CreateSiteMapNodeFromDataReader(reader)
                        AddNode(node, GetParentNodeFromDataReader(reader))
                    End While
 
                    ' Use the SQL cache dependency
                    If dependency IsNot Nothing Then
                        HttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, _
                         New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))
                    End If
                End If
            Finally
                connection.Close()
            End Try
 
            ' Return the root SiteMapNode
            Return _root
        End SyncLock
    End Function
 
    Protected Overloads Overrides Function GetRootNodeCore() As SiteMapNode
        SyncLock _lock
            BuildSiteMap()
            Return _root
        End SyncLock
    End Function
 
    ' Helper methods
    Private Function CreateSiteMapNodeFromDataReader(ByVal reader As DbDataReader) As SiteMapNode
 
        ' Make sure the node ID is present
        If reader.IsDBNull(_indexID) Then
            Throw New ProviderException(_errmsg1)
        End If
 
        ' Get the node ID from the DataReader
        Dim id As Integer = reader.GetInt32(_indexID)
 
        ' Make sure the node ID is unique
        If _nodes.ContainsKey(id) Then
            Throw New ProviderException(_errmsg2)
        End If
 
        ' Get title, URL, description, and roles from the DataReader
        Dim title As String = ReplaceNullRefs(reader, _indexTitle)
        Dim url As String = ReplaceNullRefs(reader, _indexUrl)
 
        'Eliminated...see http://weblogs.asp.net/psteele/archive/2003/10/09/31250.aspx
        Dim description As String = ReplaceNullRefs(reader, _indexDesc)
 
        'Changed variable name from 'roles' to 'rolesN' and added line 230 to dump all roles into an arrayList
        Dim rolesN As String
        Select Case reader.IsDBNull(_indexRoles)
            Case True
                rolesN = Nothing
            Case Else
                rolesN = reader(_indexRoles).ToString.Trim()
        End Select
 
        Dim rolelist As String()
        rolelist = Nothing
        If String.IsNullOrEmpty(rolesN) Then
        Else
            rolelist = rolesN.Split(New Char() {","c, ";"c}, 512)
        End If
 
        If IsArray(rolelist) Then
            roles = ArrayList.Adapter(rolelist)
        End If
 
        ' Create a SiteMapNode
        Dim node As New SiteMapNode(Me, id.ToString(), url, title, description, rolelist, Nothing, Nothing, Nothing)
 
        ' Record the node in the _nodes dictionary
        _nodes.Add(id, node)
 
        ' Return the node
        Return node
    End Function
 
    Private Function ReplaceNullRefs(ByVal rdr As SqlDataReader, ByVal rdrVal As Integer) As String
        If Not (rdr.IsDBNull(rdrVal)) Then
            Return rdr(rdrVal)
        Else
            Return String.Empty
        End If
    End Function
 
    Private Function GetParentNodeFromDataReader(ByVal reader As DbDataReader) As SiteMapNode
        ' Make sure the parent ID is present
        If reader.IsDBNull(_indexParent) Then
            '**** Commented out throw, added exit function ****
            'Throw New ProviderException(_errmsg3)
            Exit Function
        End If
        ' Get the parent ID from the DataReader
        Dim pid As Integer = reader.GetInt32(_indexParent)
 
        ' Make sure the parent ID is valid
        If Not _nodes.ContainsKey(pid) Then
            Throw New ProviderException(_errmsg4)
        End If
 
        ' Return the parent SiteMapNode
        Return _nodes(pid)
    End Function
 
    Private Sub OnSiteMapChanged(ByVal key As String, ByVal item As Object, ByVal reason As CacheItemRemovedReason)
        SyncLock _lock
            If key = _cacheDependencyName AndAlso reason = CacheItemRemovedReason.DependencyChanged Then
                ' Refresh the site map
                Clear()
                _nodes.Clear()
                _root = Nothing
            End If
        End SyncLock
    End Sub
End Class
 

Hope you all find it as useful as I have!

medicineworker

[radionauto]

Hi there, I have implemented this code with a mysql database and it seems to work fine, i only have 2 problems, firstly how i do force a refresh of the sitemap to reflect changes made to the database, and secondly - since i changed from a web.sitemap based provider to a mysql one i seem to have lost the "asp-selected-link" css class on the current page, can anyone help me get this back?

thanks