By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,913 Members | 1,356 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

VB.NET SQL-powered Sitemap

codegecko
Expert 100+
P: 533
Hi guys,

So the ever-lasting problem has remained until now - how to create a dynamic sitemap from information stored in a database.

Microsoft's MSDN magazine covered how to do this using a custom-made class in C#, specific table architecture and a couple of database triggers (plus how to deal with caching on SQL Server 7 and 2000 - MSSQL 2K5 automatically deals with cache dependencies which handles new sitemap layout and/or content, which is funky). The article can be found here.

However, I don't work with C#, I work in VB and VB.NET - so I have no idea what any of this code reads, and not only that, but all my sites and custom apps (and future shrink-wrap product line) run in VB/VB.NET - and my understanding is that each project can only work in one language. So how do I take advantage of this great new piece of kit?

Sanjay Uttam translated the entire piece of code from C# to VB.NET - and it does exactly what it says on the tin - creates a sitemap that ASP.NET's SiteMap functionality can work with, so you can link it to TreeView, Menu and SiteMapPath controls, and dynamically update the SiteMap content by storing the information in any SQL relational database structure.

Sanjay's original article can be found here, many thanks to him for taking the time and effort to make a wonderful piece of kit even more accessible and useful to countless numbers of developers worldwide.

The only edit I have made is in the ReplaceNullRefs function at line 242 - in Sanjay's article the function is checking the field index integer itself for null value, and returning the integer itself, not the data in the field that the integer points to.

Expand|Select|Wrap|Line Numbers
  1. Imports System
  2. Imports System.Web
  3. Imports System.Data.SqlClient
  4. Imports System.Collections.Specialized
  5. Imports System.Configuration
  6. Imports System.Web.Configuration
  7. Imports System.Collections.Generic
  8. Imports System.Configuration.Provider
  9. Imports System.Security.Permissions
  10. Imports System.Data.Common
  11. Imports System.Data
  12. Imports System.Web.Caching
  13.  
  14. <SqlClientPermission(SecurityAction.Demand, Unrestricted:=True)> _
  15. Public Class SqlSiteMapProvider
  16.     Inherits StaticSiteMapProvider
  17.  
  18.     Private Const _errmsg1 As String = "Missing node ID"
  19.     Private Const _errmsg2 As String = "Duplicate node ID"
  20.     Private Const _errmsg3 As String = "Missing parent ID"
  21.     Private Const _errmsg4 As String = "Invalid parent ID"
  22.     Private Const _errmsg5 As String = "Empty or missing connectionStringName"
  23.     Private Const _errmsg6 As String = "Missing connection string"
  24.     Private Const _errmsg7 As String = "Empty connection string"
  25.     Private Const _errmsg8 As String = "Invalid sqlCacheDependency"
  26.     Private Const _cacheDependencyName As String = "__SiteMapCacheDependency"
  27.  
  28.     Private _connect As String
  29.     ' Database connection string
  30.  
  31.     Private _database As String, _table As String
  32.     ' Database info for SQL Server 7/2000 cache dependency
  33.  
  34.     Private _2005dependency As Boolean = False
  35.     ' Database info for SQL Server 2005 cache dependency
  36.  
  37.     Private _indexID As Integer, _indexTitle As Integer, _indexUrl As Integer, _indexDesc As Integer, _indexRoles As Integer, _indexParent As Integer
  38.     Private _nodes As New Dictionary(Of Integer, SiteMapNode)(16)
  39.     Private ReadOnly _lock As New Object()
  40.     Private _root As SiteMapNode
  41.  
  42.     'Added...Declare an arraylist to hold all the roles this menu item applies to
  43.     Public roles As New ArrayList
  44.  
  45.     Public Overloads Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
  46.         ' Verify that config isn't null
  47.         If config Is Nothing Then
  48.             Throw New ArgumentNullException("config")
  49.         End If
  50.  
  51.         ' Assign the provider a default name if it doesn't have one
  52.         If [String].IsNullOrEmpty(Name) Then
  53.             Name = "SqlSiteMapProvider"
  54.         End If
  55.  
  56.         ' Add a default "description" attribute to config if the
  57.         ' attribute doesnt exist or is empty
  58.         If String.IsNullOrEmpty(config("description")) Then
  59.             config.Remove("description")
  60.             config.Add("description", "SQL site map provider")
  61.         End If
  62.  
  63.         ' Call the base class's Initialize method
  64.         MyBase.Initialize(Name, config)
  65.  
  66.         ' Initialize _connect
  67.         Dim connect As String = config("connectionStringName")
  68.  
  69.         If [String].IsNullOrEmpty(connect) Then
  70.             Throw New ProviderException(_errmsg5)
  71.         End If
  72.         config.Remove("connectionStringName")
  73.  
  74.         If WebConfigurationManager.ConnectionStrings(connect) Is Nothing Then
  75.             Throw New ProviderException(_errmsg6)
  76.         End If
  77.  
  78.         _connect = WebConfigurationManager.ConnectionStrings(connect).ConnectionString
  79.  
  80.         If [String].IsNullOrEmpty(_connect) Then
  81.             Throw New ProviderException(_errmsg7)
  82.         End If
  83.  
  84.         ' Initialize SQL cache dependency info
  85.         Dim dependency As String = config("sqlCacheDependency")
  86.  
  87.         If Not [String].IsNullOrEmpty(dependency) Then
  88.             If [String].Equals(dependency, "CommandNotification", StringComparison.InvariantCultureIgnoreCase) Then
  89.                 SqlDependency.Start(_connect)
  90.                 _2005dependency = True
  91.             Else
  92.                 ' If not "CommandNotification", then extract database and table names
  93.                 Dim info As String() = dependency.Split(New Char() {":"c})
  94.                 If info.Length <> 2 Then
  95.                     Throw New ProviderException(_errmsg8)
  96.                 End If
  97.  
  98.                 _database = info(0)
  99.                 _table = info(1)
  100.             End If
  101.  
  102.             config.Remove("sqlCacheDependency")
  103.         End If
  104.  
  105.         ' SiteMapProvider processes the securityTrimmingEnabled
  106.         ' attribute but fails to remove it. Remove it now so we can
  107.         ' check for unrecognized configuration attributes.
  108.  
  109.         If config("securityTrimmingEnabled") IsNot Nothing Then
  110.             config.Remove("securityTrimmingEnabled")
  111.         End If
  112.  
  113.         ' Throw an exception if unrecognized attributes remain
  114.         If config.Count > 0 Then
  115.             Dim attr As String = config.GetKey(0)
  116.             If Not [String].IsNullOrEmpty(attr) Then
  117.                 Throw New ProviderException("Unrecognized attribute: " + attr)
  118.             End If
  119.         End If
  120.     End Sub
  121.  
  122.     Public Overloads Overrides Function BuildSiteMap() As SiteMapNode
  123.         SyncLock _lock
  124.             ' Return immediately if this method has been called before
  125.             If _root IsNot Nothing Then
  126.                 Return _root
  127.             End If
  128.  
  129.             ' Query the database for site map nodes
  130.             Dim connection As New SqlConnection(_connect)
  131.  
  132.             Try
  133.                 Dim command As New SqlCommand("proc_GetSiteMap", connection)
  134.                 command.CommandType = CommandType.StoredProcedure
  135.  
  136.                 ' Create a SQL cache dependency if requested
  137.                 Dim dependency As SqlCacheDependency = Nothing
  138.  
  139.                 If _2005dependency Then
  140.                     dependency = New SqlCacheDependency(command)
  141.                 ElseIf Not [String].IsNullOrEmpty(_database) AndAlso Not String.IsNullOrEmpty(_table) Then
  142.                     dependency = New SqlCacheDependency(_database, _table)
  143.                 End If
  144.  
  145.                 connection.Open()
  146.                 Dim reader As SqlDataReader = command.ExecuteReader()
  147.                 _indexID = reader.GetOrdinal("ID")
  148.                 _indexUrl = reader.GetOrdinal("Url")
  149.                 _indexTitle = reader.GetOrdinal("Title")
  150.                 _indexDesc = reader.GetOrdinal("Description")
  151.                 _indexRoles = reader.GetOrdinal("Roles")
  152.                 _indexParent = reader.GetOrdinal("Parent")
  153.  
  154.                 If reader.Read() Then
  155.                     ' Create the root SiteMapNode and add it to the site map
  156.  
  157.                     _root = CreateSiteMapNodeFromDataReader(reader)
  158.                     AddNode(_root, Nothing)
  159.  
  160.                     ' Build a tree of SiteMapNodes underneath the root node
  161.                     While reader.Read()
  162.                         ' Create another site map node and add it to the site map
  163.                         Dim node As SiteMapNode = CreateSiteMapNodeFromDataReader(reader)
  164.                         AddNode(node, GetParentNodeFromDataReader(reader))
  165.                     End While
  166.  
  167.                     ' Use the SQL cache dependency
  168.                     If dependency IsNot Nothing Then
  169.                         HttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, _
  170.                          New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))
  171.                     End If
  172.                 End If
  173.             Finally
  174.                 connection.Close()
  175.             End Try
  176.  
  177.             ' Return the root SiteMapNode
  178.             Return _root
  179.         End SyncLock
  180.     End Function
  181.  
  182.     Protected Overloads Overrides Function GetRootNodeCore() As SiteMapNode
  183.         SyncLock _lock
  184.             BuildSiteMap()
  185.             Return _root
  186.         End SyncLock
  187.     End Function
  188.  
  189.     ' Helper methods
  190.     Private Function CreateSiteMapNodeFromDataReader(ByVal reader As DbDataReader) As SiteMapNode
  191.  
  192.         ' Make sure the node ID is present
  193.         If reader.IsDBNull(_indexID) Then
  194.             Throw New ProviderException(_errmsg1)
  195.         End If
  196.  
  197.         ' Get the node ID from the DataReader
  198.         Dim id As Integer = reader.GetInt32(_indexID)
  199.  
  200.         ' Make sure the node ID is unique
  201.         If _nodes.ContainsKey(id) Then
  202.             Throw New ProviderException(_errmsg2)
  203.         End If
  204.  
  205.         ' Get title, URL, description, and roles from the DataReader
  206.         Dim title As String = ReplaceNullRefs(reader, _indexTitle)
  207.         Dim url As String = ReplaceNullRefs(reader, _indexUrl)
  208.  
  209.         'Eliminated...see http://weblogs.asp.net/psteele/archive/2003/10/09/31250.aspx
  210.         Dim description As String = ReplaceNullRefs(reader, _indexDesc)
  211.  
  212.         'Changed variable name from 'roles' to 'rolesN' and added line 230 to dump all roles into an arrayList
  213.         Dim rolesN As String
  214.         Select Case reader.IsDBNull(_indexRoles)
  215.             Case True
  216.                 rolesN = Nothing
  217.             Case Else
  218.                 rolesN = reader(_indexRoles).ToString.Trim()
  219.         End Select
  220.  
  221.         Dim rolelist As String()
  222.         rolelist = Nothing
  223.         If String.IsNullOrEmpty(rolesN) Then
  224.         Else
  225.             rolelist = rolesN.Split(New Char() {","c, ";"c}, 512)
  226.         End If
  227.  
  228.         If IsArray(rolelist) Then
  229.             roles = ArrayList.Adapter(rolelist)
  230.         End If
  231.  
  232.         ' Create a SiteMapNode
  233.         Dim node As New SiteMapNode(Me, id.ToString(), url, title, description, rolelist, Nothing, Nothing, Nothing)
  234.  
  235.         ' Record the node in the _nodes dictionary
  236.         _nodes.Add(id, node)
  237.  
  238.         ' Return the node
  239.         Return node
  240.     End Function
  241.  
  242.     Private Function ReplaceNullRefs(ByVal rdr As SqlDataReader, ByVal rdrVal As Integer) As String
  243.         If Not (rdr.IsDBNull(rdrVal)) Then
  244.             Return rdr(rdrVal)
  245.         Else
  246.             Return String.Empty
  247.         End If
  248.     End Function
  249.  
  250.     Private Function GetParentNodeFromDataReader(ByVal reader As DbDataReader) As SiteMapNode
  251.         ' Make sure the parent ID is present
  252.         If reader.IsDBNull(_indexParent) Then
  253.             '**** Commented out throw, added exit function ****
  254.             'Throw New ProviderException(_errmsg3)
  255.             Exit Function
  256.         End If
  257.         ' Get the parent ID from the DataReader
  258.         Dim pid As Integer = reader.GetInt32(_indexParent)
  259.  
  260.         ' Make sure the parent ID is valid
  261.         If Not _nodes.ContainsKey(pid) Then
  262.             Throw New ProviderException(_errmsg4)
  263.         End If
  264.  
  265.         ' Return the parent SiteMapNode
  266.         Return _nodes(pid)
  267.     End Function
  268.  
  269.     Private Sub OnSiteMapChanged(ByVal key As String, ByVal item As Object, ByVal reason As CacheItemRemovedReason)
  270.         SyncLock _lock
  271.             If key = _cacheDependencyName AndAlso reason = CacheItemRemovedReason.DependencyChanged Then
  272.                 ' Refresh the site map
  273.                 Clear()
  274.                 _nodes.Clear()
  275.                 _root = Nothing
  276.             End If
  277.         End SyncLock
  278.     End Sub
  279. End Class
  280.  
Hope you all find it as useful as I have!

medicineworker
Aug 19 '07 #1
Share this Article
Share on Google+
1 Comment


P: 1
Hi there, I have implemented this code with a mysql database and it seems to work fine, i only have 2 problems, firstly how i do force a refresh of the sitemap to reflect changes made to the database, and secondly - since i changed from a web.sitemap based provider to a mysql one i seem to have lost the "asp-selected-link" css class on the current page, can anyone help me get this back?

thanks
Oct 27 '08 #2