VB.NET SQL支持的站点地图
嗨，大家好，
因此，一直存在的问题一直存在到现在-如何根据数据库中存储的信息创建动态站点地图。
微软的MSDN杂志介绍了如何使用C＃中的自定义类，特定的表体系结构和几个数据库触发器（以及如何处理SQL Server 7和2000上的缓存-MSSQL 2K5自动处理处理新内容的缓存依赖项）来执行此操作。网站地图的布局和/或内容，这很时髦）。 可以找到该文章
在这里 。但是，我不使用C＃，而是在VB和VB.NET中工作-所以我不知道这段代码读的是什么，不仅如此，而且我的所有站点和自定义应用程序（以及将来的收缩包装产品）行）在VB / VB.NET中运行-我的理解是，每个项目只能使用一种语言。 那么，如何利用这个出色的新套件呢？
Sanjay Uttam将整个代码段从C＃转换为VB.NET-它的功能恰如其名-创建了一个ASP.NET的SiteMap功能可以使用的站点地图，因此您可以将其链接到TreeView，Menu和SiteMapPath通过将信息存储在任何SQL关系数据库结构中来控制和动态更新SiteMap内容。
可以找到Sanjay的原始文章
在这里 ，非常感谢他花了时间和精力制作了一套精美的工具包，使之对全世界无数的开发人员来说更加易用和有用。我所做的唯一编辑是在第242行的ReplaceNullRefs函数中-在Sanjay的文章中，该函数正在检查字段索引整数本身是否为空值，并返回整数本身，而不是返回整数所指向的字段中的数据。
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
_
Public Class SqlSiteMapProviderInherits 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 IntegerPrivate _nodes As New Dictionary(Of Integer, SiteMapNode)(16)Private ReadOnly _lock As New Object()Private _root As SiteMapNode 'Added...Declare an arraylist to hold all the roles this menu item applies toPublic roles As New ArrayList Public Overloads Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)' Verify that config isn't nullIf config Is Nothing ThenThrow New ArgumentNullException("config")End If ' Assign the provider a default name if it doesn't have oneIf [String].IsNullOrEmpty(Name) ThenName = "SqlSiteMapProvider"End If ' Add a default "description" attribute to config if the' attribute doesnt exist or is emptyIf String.IsNullOrEmpty(config("description")) Thenconfig.Remove("description")config.Add("description", "SQL site map provider")End If ' Call the base class's Initialize methodMyBase.Initialize(Name, config) ' Initialize _connectDim connect As String = config("connectionStringName") If [String].IsNullOrEmpty(connect) ThenThrow New ProviderException(_errmsg5)End Ifconfig.Remove("connectionStringName") If WebConfigurationManager.ConnectionStrings(connect) Is Nothing ThenThrow New ProviderException(_errmsg6)End If _connect = WebConfigurationManager.ConnectionStrings(connect).ConnectionString If [String].IsNullOrEmpty(_connect) ThenThrow New ProviderException(_errmsg7)End If ' Initialize SQL cache dependency infoDim dependency As String = config("sqlCacheDependency") If Not [String].IsNullOrEmpty(dependency) ThenIf [String].Equals(dependency, "CommandNotification", StringComparison.InvariantCultureIgnoreCase) ThenSqlDependency.Start(_connect)_2005dependency = TrueElse' If not "CommandNotification", then extract database and table namesDim info As String() = dependency.Split(New Char() {":"c})If info.Length <> 2 ThenThrow 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 Thenconfig.Remove("securityTrimmingEnabled")End If ' Throw an exception if unrecognized attributes remainIf config.Count > 0 ThenDim attr As String = config.GetKey(0)If Not [String].IsNullOrEmpty(attr) ThenThrow New ProviderException("Unrecognized attribute: " + attr)End IfEnd IfEnd Sub Public Overloads Overrides Function BuildSiteMap() As SiteMapNodeSyncLock _lock' Return immediately if this method has been called beforeIf _root IsNot Nothing ThenReturn _rootEnd If ' Query the database for site map nodesDim connection As New SqlConnection(_connect) TryDim command As New SqlCommand("proc_GetSiteMap", connection)command.CommandType = CommandType.StoredProcedure ' Create a SQL cache dependency if requestedDim dependency As SqlCacheDependency = Nothing If _2005dependency Thendependency = New SqlCacheDependency(command)ElseIf Not [String].IsNullOrEmpty(_database) AndAlso Not String.IsNullOrEmpty(_table) Thendependency = 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 nodeWhile reader.Read()' Create another site map node and add it to the site mapDim node As SiteMapNode = CreateSiteMapNodeFromDataReader(reader)AddNode(node, GetParentNodeFromDataReader(reader))End While ' Use the SQL cache dependencyIf dependency IsNot Nothing ThenHttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, _New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))End IfEnd IfFinallyconnection.Close()End Try ' Return the root SiteMapNodeReturn _rootEnd SyncLockEnd Function Protected Overloads Overrides Function GetRootNodeCore() As SiteMapNodeSyncLock _lockBuildSiteMap()Return _rootEnd SyncLockEnd Function ' Helper methodsPrivate Function CreateSiteMapNodeFromDataReader(ByVal reader As DbDataReader) As SiteMapNode ' Make sure the node ID is presentIf reader.IsDBNull(_indexID) ThenThrow New ProviderException(_errmsg1)End If ' Get the node ID from the DataReaderDim id As Integer = reader.GetInt32(_indexID) ' Make sure the node ID is uniqueIf _nodes.ContainsKey(id) ThenThrow New ProviderException(_errmsg2)End If ' Get title, URL, description, and roles from the DataReaderDim title As String = ReplaceNullRefs(reader, _indexTitle)Dim url As String = ReplaceNullRefs(reader, _indexUrl) 'Eliminated...see http://weblogs.asp.net/psteele/archive/2003/10/09/31250.aspxDim description As String = ReplaceNullRefs(reader, _indexDesc) 'Changed variable name from 'roles' to 'rolesN' and added line 230 to dump all roles into an arrayListDim rolesN As StringSelect Case reader.IsDBNull(_indexRoles)Case TruerolesN = NothingCase ElserolesN = reader(_indexRoles).ToString.Trim()End Select Dim rolelist As String()rolelist = NothingIf String.IsNullOrEmpty(rolesN) ThenElserolelist = rolesN.Split(New Char() {","c, ";"c}, 512)End If If IsArray(rolelist) Thenroles = ArrayList.Adapter(rolelist)End If ' Create a SiteMapNodeDim 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 nodeReturn nodeEnd Function Private Function ReplaceNullRefs(ByVal rdr As SqlDataReader, ByVal rdrVal As Integer) As StringIf Not (rdr.IsDBNull(rdrVal)) ThenReturn rdr(rdrVal)ElseReturn String.EmptyEnd IfEnd Function Private Function GetParentNodeFromDataReader(ByVal reader As DbDataReader) As SiteMapNode' Make sure the parent ID is presentIf reader.IsDBNull(_indexParent) Then'**** Commented out throw, added exit function ****'Throw New ProviderException(_errmsg3)Exit FunctionEnd If' Get the parent ID from the DataReaderDim pid As Integer = reader.GetInt32(_indexParent) ' Make sure the parent ID is validIf Not _nodes.ContainsKey(pid) ThenThrow New ProviderException(_errmsg4)End If ' Return the parent SiteMapNodeReturn _nodes(pid)End Function Private Sub OnSiteMapChanged(ByVal key As String, ByVal item As Object, ByVal reason As CacheItemRemovedReason)SyncLock _lockIf key = _cacheDependencyName AndAlso reason = CacheItemRemovedReason.DependencyChanged Then' Refresh the site mapClear()_nodes.Clear()_root = NothingEnd IfEnd SyncLockEnd Sub
End Class 医务工作者
From: https://bytes.com/topic/asp-net/insights/695173-vb-net-sql-powered-sitemap
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！