Issue with Jeff's SqlSiteMapProvider

Discussion in 'ASP .Net' started by Mark B, Sep 5, 2008.

  1. Mark B

    Mark B Guest

    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
     
    Mark B, Sep 5, 2008
    #1
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dm1608
    Replies:
    0
    Views:
    419
    dm1608
    Jan 29, 2006
  2. SteveL

    SqlSiteMapProvider

    SteveL, Mar 5, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    5,488
    SteveL
    Mar 6, 2006
  3. Kenny McCormack
    Replies:
    0
    Views:
    319
    Kenny McCormack
    Mar 26, 2008
  4. Richard Lionheart

    Problem running a Jeff Prosise example

    Richard Lionheart, Oct 25, 2005, in forum: ASP .Net Building Controls
    Replies:
    1
    Views:
    138
    Richard Lionheart
    Oct 26, 2005
  5. Lynn McGuire
    Replies:
    27
    Views:
    712
    Rui Maciel
    Nov 8, 2012
Loading...

Share This Page