Geeks With Blogs


Locations of visitors to this page

View ► Sanjay Uttam []'s profile on LinkedIn

Add to Google Reader or Homepage

Sanjay Uttam Predominantly .NET

UPDATE 6.18.2008: You can download the file from here

Alright, so I wanted to build a dynamically/DB driven role-based menu system utilizing framework 2.  I started with this article…


And then converted to VB.NET…Not to come across some items that I needed to change…


I came across the following errors:

“'System.Web.Caching.CacheItemRemovedCallback' is a delegate type and requires a single 'addressof' expression as the only argument to the constructor”


Which required this line “New CacheItemRemovedCallback(AOnSiteMapChanged))” in the BuildSiteMap() function to be changed to “New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))”  No biggy.


Then there was some funky stuff going on with NULL values (mainly in the “description” and “parentID” columns”  Turns out there was a bunch of this going on..


Dim title As String = IIf(reader.IsDBNull(_indexTitle), Nothing, reader.GetString(_indexTitle).Trim())


Which was freaking out because of the NULL values (see


So I added a simple function “CheckNullRefs” to account for that. 


Lastly, it looks like the “Roles” property from SqlSiteMapProvider was actually giving me the _number_ of roles assigned to a siteMapNode rather than a collection of the actual roles themselves.  So for example roles=”2” instead of




I added a public array list, and then added the line below to the “CreateSiteMapNodeFromDataReader” function. 

roles = ArrayList.Adapter(rolelist)


I’m sure there is a more elegant way to do all of this…but figured this would be helpful since I found little to no info on it by googling. 


Update! I had a, uhm, "feature" in my code..the signature of the ReplaceNullRefs was wrong (was accepting ByVal rdrVal As String instead of Integer.  Thanks to Benjamin Howarth for pointing this out!

Update 9/20:  I've updated the ReplaceNullRefs function below, thanks Rob!

This allows me to dynamically build my menu by doing the following in the MenuItemDataBound sub

    Protected Sub PublicMenu_MenuItemDataBound(ByVal sender As Object, ByVal e As MenuEventArgs)

        'Users can be in multiple groups so...if the roles attribute of a siteMapNode contains one of the roles that a user belongs to

        'do not remove it.  Remove all others.

        If (UserRoleName.Length) < 1 Then

            UserRoleName = Roles.GetRolesForUser()

        End If

        Dim blmRemoveRole As Boolean = True

For i As Integer = 0 To UserRoleName.Length - 1

            If CType(e.Item.DataItem, System.Web.SiteMapNode).Roles.Contains(UserRoleName(i)) Then


                blmRemoveRole = False

                Exit For

            End If



        If blmRemoveRole Then

            If e.Item.Parent Is Nothing Then




            End If

        End If


    End Sub


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



''' Summary description for SqlSiteMapProvider



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.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




        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


                _2005dependency = True


                ' 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



        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


        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)



                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



                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



            End Try


            ' Return the root SiteMapNode

            Return _root

        End SyncLock

    End Function


    Protected Overloads Overrides Function GetRootNodeCore() As SiteMapNode

        SyncLock _lock


            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)



        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 = IIf(reader.IsDBNull(_indexRoles), Nothing, reader.GetString(_indexRoles).Trim())



        Dim rolelist As String() = Nothing

        If Not [String].IsNullOrEmpty(rolesN) Then

            rolelist = rolesN.Split(New Char() {","c, ";"c}, 512)

        End If

        roles = ArrayList.Adapter(rolelist)



        ' 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 DbDataReader, ByVal rdrVal As Integer) As String

        If Not (rdr.IsDBNull(rdrVal)) Then

            ' Thanks Rob Johnston

            Return rdr.GetString(rdrVal)


            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



                _root = Nothing

            End If

        End SyncLock

    End Sub

End Class
You can download the file from here kick it on Posted on Tuesday, August 7, 2007 11:10 AM VS.NET 2005 , VB.NET | Back to top

Copyright © Sanjay Uttam | Powered by: