Issue with Jeff's SqlSiteMapProvider

M

Mark B

I have been using http://msdn.microsoft.com/en-us/magazine/cc163657.aspx
(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">
<providers>
<add name="AspNetSqlSiteMapProvider"
type="SqlSiteMapProvider"
securityTrimmingEnabled="false"
connectionStringName="MyConnectionString"
sqlCacheDependency="CommandNotification"
/>
</providers>
</siteMap>
<caching>
<sqlCacheDependency enabled="false" />
</caching>




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

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.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("uspGeneralSiteMapGet",
connection)
command.CommandType = CommandType.StoredProcedure

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


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

' 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
Clear()
_nodes.Clear()
_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 =
UCase(fGetBrowserLanguage())
End If
Session("strLanguageSetting") =
Request.Cookies("SiteLanguage").Value
End If
DropDownList1.SelectedValue = Session("strLanguageSetting")
End If

End Function

Function fLanguageChange() As Boolean

Session("strLanguageSetting") = DropDownList1.SelectedValue
Response.Cookies("SiteLanguage").Value =
Session("strLanguageSetting")
Response.Cookies("SiteLanguage").Expires =
DateTime.Now.AddDays(1000)
Response.Redirect(Request.Url.ToString)

End Function


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

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
fLanguageChange()
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",
strWhere)
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)
fSetCountryFlag()

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)
AS

begin

SELECT [ID],
[dbo].[ufGeneralLanguageTextGet](@EnterLanguageCode,[Title]) as [Title],
[dbo].[ufGeneralLanguageTextGet](@EnterLanguageCode,[Description]) as
[Description],
,
[Roles],
[Parent]
FROM [tblSiteMap]
ORDER BY [ID]

end
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top