This the VB.NET provided by Jeff Prosise
http://www.wintellect.com/cs/DasBlogContentBinary/SqlSiteMapProvider.zip
Here is the contents of the above link:
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
<SqlClientPermissionAttribute(SecurityAction.Demand, Unrestricted:=True)> _
Public Class SqlSiteMapProvider
Inherits StaticSiteMapProvider
Private _errmsg1 As String = "Missing node ID"
Private _errmsg2 As String = "Duplicate node ID"
Private _errmsg3 As String = "Missing parent ID"
Private _errmsg4 As String = "Invalid parent ID"
Private _errmsg5 As String = "Empty or missing connectionStringName"
Private _errmsg6 As String = "Missing connection string"
Private _errmsg7 As String = "Empty connection string"
Private _errmsg8 As String = "Invalid sqlCacheDependency"
Private _cacheDependencyName As String = "__SiteMapCacheDependency"
Private _connect As String ' Database connection string
Private _database, _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, _indexTitle, _indexUrl, _indexDesc, _indexRoles, _indexParent As Integer
Private _nodes As Dictionary(Of Integer, SiteMapNode) = New Dictionary(Of Integer, SiteMapNode)(16)
Private _lock As New Object()
Private _root As SiteMapNode
Public 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 Not (config("securityTrimmingEnabled") Is 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 'Initialize
Public Overrides Function BuildSiteMap() As SiteMapNode
SyncLock _lock
' Return immediately if this method has been called before
If Not (_root Is 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)
Else
If Not String.IsNullOrEmpty(_database) And Not String.IsNullOrEmpty(_table) Then
dependency = New SqlCacheDependency(_database, _table)
End If
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 Not (dependency Is 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 'BuildSiteMap
Protected Overrides Function GetRootNodeCore() As SiteMapNode
SyncLock _lock
BuildSiteMap()
Return _root
End SyncLock
End Function 'GetRootNodeCore
' 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 = Nothing
If Not reader.IsDBNull(_indexTitle) Then
title = reader.GetString(_indexTitle).Trim()
End If
Dim url As String = Nothing
If Not reader.IsDBNull(_indexUrl) Then
url = reader.GetString(_indexUrl).Trim()
End If
Dim description As String = Nothing
If Not reader.IsDBNull(_indexDesc) Then
description = reader.GetString(_indexDesc).Trim()
End If
Dim roles As String = Nothing
If Not reader.IsDBNull(_indexRoles) Then
roles = reader.GetString(_indexRoles).Trim()
End If
' 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 'CreateSiteMapNodeFromDataReader
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 'GetParentNodeFromDataReader
Sub OnSiteMapChanged(ByVal key As String, ByVal item As Object, ByVal reason As CacheItemRemovedReason)
SyncLock _lock
If key = _cacheDependencyName And reason = CacheItemRemovedReason.DependencyChanged Then
' Refresh the site map
Clear()
_nodes.Clear()
_root = Nothing
End If
End SyncLock
End Sub 'OnSiteMapChanged
End Class 'SqlSiteMapProvider