Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Issue with Jeff's SqlSiteMapProvider

Thread Tools

Issue with Jeff's SqlSiteMapProvider

Mark B
Posts: n/a
I have been using
(albeit a converted VB version) to have an SqlSiteMapProvider for the main
horizontal menu.

Instead of using a static table though I have used a stored procedure with a
parameter: @LanguageCode (e.g. FR-FR)

So each time a web user changes the Language drop-down on a web page
(actually on the top right of the Master page) there is a posts back and the
stored procedure returns different values for the Node descriptions.

It works most of the time though at seemingly random times, the whole
horizontal menu bar disappears.

I am wondering if this has something to do with his SQLCache code. I
followed his instructions on the above page link to set it to false but now
the menu bar doesn't change at all to reflect a new language.

Any ideas? (SQL 2005)

<siteMap enabled="true" defaultProvider="AspNetSqlSiteMapProvider">
<add name="AspNetSqlSiteMapProvider"
<sqlCacheDependency enabled="false" />

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>
''' Summary description for SqlSiteMapProvider
''' </summary>
<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
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 =

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
Private _nodes As New Dictionary(Of Integer, SiteMapNode)(16)
Private ReadOnly _lock As New Object()
Private _root As SiteMapNode

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 doesn't 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
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(_errmsg
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: " +
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("uspGeneralSiteMapGet",
command.CommandType = CommandType.StoredProcedure

Dim strLanguageSetting As String =
System.Web.HttpContext.Current.Session("strLanguag eSetting")
If strLanguageSetting = "" Then
strLanguageSetting = "FR-FR"
End If
command.Parameters.AddWithValue("EnterLanguageCode ",

' 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 =
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
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 = IIf(reader.IsDBNull(_indexTitle), Nothing,
Dim url As String = IIf(reader.IsDBNull(_indexUrl), Nothing,
Dim description As String = IIf(reader.IsDBNull(_indexDesc),
Nothing, reader.GetString(_indexDesc).Trim())
Dim roles As String = IIf(reader.IsDBNull(_indexRoles), Nothing,

' If roles were specified, turn the list into a string array
Dim rolelist As String() = Nothing
If Not [String].IsNullOrEmpty(roles) Then
rolelist = roles.Split(New Char() {","c, ";"c}, 512)
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 GetParentNodeFromDataReader(ByVal reader As
DbDataReader) As SiteMapNode

' Make sure the parent ID is present
If reader.IsDBNull(_indexParent) Then
Throw New ProviderException(_errmsg3)
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

Partial Class pages_master_page_MasterPage
Inherits System.Web.UI.MasterPage

Function fSetLanguage() As Boolean

'Check if session language exists
'If not, get cookie, if none, get browser language
'and set cookie and session language

If IsPostBack = False Then
If Session("strLanguageSetting") = Nothing Then
'Get cookie
If Response.Cookies("SiteLanguage").Value = Nothing Then
Response.Cookies("SiteLanguage").Value =
End If
Session("strLanguageSetting") =
End If
DropDownList1.SelectedValue = Session("strLanguageSetting")
End If

End Function

Function fLanguageChange() As Boolean

Session("strLanguageSetting") = DropDownList1.SelectedValue
Response.Cookies("SiteLanguage").Value =
Response.Cookies("SiteLanguage").Expires =

End Function

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
End Sub

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
End Sub

Function fGetBrowserLanguage() As String

Dim objUserInfo() As String
Dim strBrowserLanguage As String
Dim strWhere As String

objUserInfo = Request.UserLanguages
strBrowserLanguage = objUserInfo(0)

'If an active language then set
strWhere = "LanguageCode='" + strBrowserLanguage + "'"
If sfGeneral.fGetSingleValueFromTable("Active", "tblLanguageCodes",
strWhere) = True Then
fGetBrowserLanguage = strBrowserLanguage
Exit Function
End If

'If not, regress to root active language, e.g. fr-CA would be fr-FR
strWhere = "LanguageCodeGeneric='" + Left(strBrowserLanguage,
InStr(strBrowserLanguage, "-") - 1) + "' AND [Active]= 'True'"
Dim strFoundLanguageCode As String
strFoundLanguageCode =
sfGeneral.fGetSingleValueFromTable("LanguageCode", "tblLanguageCodes",
If strFoundLanguageCode <> "" Then
fGetBrowserLanguage = strFoundLanguageCode
Exit Function
End If

'If not active, regress to en-US
fGetBrowserLanguage = "EN-US"

End Function

Function fSetLanguageValues() As Boolean

Label1.Text = sfLanguage.fText(30)
Label2.Text = sfLanguage.fText(31)
Label3.Text = sfLanguage.fText(32)
HyperLink1.Text = sfLanguage.fText(33)

End Function

Function fSetCountryFlag() As Boolean
Dim strCountryCode As String

strCountryCode = LCase(Mid(Session("strLanguageSetting"),
InStr(Session("strLanguageSetting"), "-") + 1))
Image1.ImageUrl = "~/pages/master_page/images/flags/" +
strCountryCode + ".png"

End Function

End Class

ALTER PROCEDURE [dbo].[uspGeneralSiteMapGet]

@EnterLanguageCode varchar(20)


[dbo].[ufGeneralLanguageTextGet](@EnterLanguageCode,[Title]) as [Title],
[dbo].[ufGeneralLanguageTextGet](@EnterLanguageCode,[Description]) as
FROM [tblSiteMap]


Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
TreeView / SQLSiteMapProvider Question Dave ASP .Net 0 06-02-2010 11:40 AM
how to enable security trimmimg with SqlSiteMapProvider rote ASP .Net 0 10-16-2008 07:12 AM
SqlSiteMapProvider question for menu control =?Utf-8?B?V2ViQnVpbGRlcjQ1MQ==?= ASP .Net 2 08-17-2007 07:10 PM
SQLsiteMapProvider Cache Problem Rahul ASP .Net 1 04-19-2006 05:23 PM
SqlSiteMapProvider SteveL ASP .Net 2 03-06-2006 07:47 AM