Asp.net Create Custom Sql Sitemapprovider Sample

 

MSDN

Custom Site Map Providers in ASP.NET 2.0

Site Map Providers

 

Code

Web.config

<configuration>
	<connectionStrings>
		<add name="SiteMapConnectionString" connectionString="server=.;database=TestDatabase;uid=sa;pwd=P@ssw0rd"/>
	</connectionStrings>
	<system.web>
		<siteMap defaultProvider="SqlSiteMapProvider" enabled="true">
			<providers>
				<clear />
				<add name="SqlSiteMapProvider" type="ClassLibrary1.SqlSiteMapProvider, ClassLibrary1" securityTrimmingEnabled="false" connectionStringName="SiteMapConnectionString" />
				<add name="web" type="System.Web.XmlSiteMapProvider, System.Web, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" siteMapFile="Web.sitemap" />
			</providers>
		</siteMap>
		<caching>
			<sqlCacheDependency enabled="true" pollTime="500">
				<databases>
					<add name="DocumentMaster" connectionStringName="SiteMapConnectionString" pollTime="500" />
				</databases>
			</sqlCacheDependency>
		</caching>
	......
	</system.web>
<configuration>

 

 

aspx

<asp:TreeView runat="server" ID="trSiteMap"
        DataSourceID="smdsAdministration"
        ImageSet="Arrows" 
        NodeStyle-HorizontalPadding="4px" 
        SelectedNodeStyle-Font-Bold="true"
        SelectedNodeStyle-CssClass="ui-menu-selected"
        ShowLines="true">
</asp:TreeView>
<asp:SiteMapDataSource runat="server" ID="smdsAdministration" SiteMapProvider="SqlSiteMapProvider" StartingNodeUrl="naver.com" />

 

MSSQL Table

USE [TestDatabase]
GO

/****** Object:  Table [dbo].[tb_Sitemap]    Script Date: 04/04/2011 19:27:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tb_Sitemap](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](32) NULL,
	[Description] [varchar](512) NULL,
	[Url] [varchar](512) NULL,
	[Roles] [varchar](512) NULL,
	[Parent] [int] NULL,
 CONSTRAINT [PK_tb_Sitemap] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

 

MSSQL Stored Procedure

USE [TestDatabase]
GO
/****** Object:  StoredProcedure [dbo].[proc_GetSiteMap]    Script Date: 04/04/2011 19:28:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Angeleyes
-- Create date: 2011-04-01
-- Description:	Sitemap List
-- =============================================
ALTER PROCEDURE [dbo].[proc_GetSiteMap]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT	*
	  FROM	[dbo].[tb_Sitemap]
END

 

 

MSSQL Table-Data

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[tb_Sitemap]    Script Date: 04/04/2011 19:29:33 ******/
SET IDENTITY_INSERT [dbo].[tb_Sitemap] ON
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (1, N'Home', NULL, N'~/Default.aspx', NULL, NULL)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (2, N'News', N'News', N'~/News.aspx', N'*', 1)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (3, N'Naver', N'Naver Page', N'http://naver.com', N'*', 1)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (4, N'new.gimsdev.com', N'new.gimsdev.com', N'http://new.gimsdev.com', N'*', 1)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (5, N'Admin', N'Administrator Menu', N'~/Admin.aspx', N'*', 1)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (6, N'Daum', N'Daum Page', N'http://daum.net/', N'*', 1)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (7, N'경향', N'경향', N'http://www.khan.co.kr/', NULL, 2)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (8, N'한겨례', N'한겨례', N'http://www.hani.co.kr/', NULL, 2)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (9, N'노컷뉴스', N'노컷뉴스', N'http://www.nocutnews.co.kr/', NULL, 2)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (10, N'SE', N'Naver SE', N'http://se.naver.com/', NULL, 3)
INSERT [dbo].[tb_Sitemap] ([ID], [Title], [Description], [Url], [Roles], [Parent]) VALUES (11, N'ME', N'Naver ME', N'http://me.naver.com/', NULL, 3)
SET IDENTITY_INSERT [dbo].[tb_Sitemap] OFF

 

 

SqlSiteMapProvider.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using System.Runtime.CompilerServices;
using System.Security.Permissions;

using System.Web;
using System.Web.Configuration;

namespace HPW.Portal.Common.Navigation
{
	[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
	public class SqlSiteMapProvider : StaticSiteMapProvider
	{
		#region Contructor

		#endregion

		#region Instance Variables

		private const string _errmsg1 = "Missing node ID";
		private const string _errmsg2 = "Duplicate node ID";
		private const string _errmsg3 = "Missing parent ID";
		private const string _errmsg4 = "Invalid parent ID";
		private const string _errmsg5 = "Empty or missing connectionStringName";
		private const string _errmsg6 = "Missing connection string";
		private const string _errmsg7 = "Empty connection string";

		private string _connect, strProcedureName;
		private int _indexID, _indexTitle, _indexUrl, _indexDesc, _indexRoles, _indexParent;
		private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int, SiteMapNode>(16);
		private Dictionary<string, object> Parameters = new Dictionary<string, object>();
		private bool IsParameter = false;
		private SiteMapNode _root;

		#endregion

		#region Properties

		#region // string ProcedureName //
		/// <summary>
		/// Properties Procedure Name
		/// </summary>
		protected string ProcedureName
		{
			get
			{
				if (String.IsNullOrEmpty(strProcedureName))
					return "proc_GetSiteMap";
				else
					return strProcedureName;
			}

			set
			{
				strProcedureName = value;
			}
		}
		#endregion

		#endregion

		#region Initialize and Shutdown methods

		#region // override void Initialize(string name, NameValueCollection config) //
		/// <summary>
		/// Initialize
		/// </summary>
		/// <param name="name">config Name</param>
		/// <param name="config">config attribute</param>
		public override void Initialize(string name, NameValueCollection config)
		{
			if (config == null)
				throw new ArgumentNullException("config");

			if (String.IsNullOrEmpty(name))
				name = "SqlSiteMapProvider";

			base.Initialize(name, config);

			string connect = config["connectionStringName"];

			if (String.IsNullOrEmpty(connect))
				throw new ProviderException(_errmsg5);

			config.Remove("connectionStringName");

			if (WebConfigurationManager.ConnectionStrings[connect] == null)
				throw new ProviderException(_errmsg6);

			_connect = WebConfigurationManager.ConnectionStrings
				[connect].ConnectionString;

			if (String.IsNullOrEmpty(_connect))
				throw new ProviderException(_errmsg7);

			if (config["securityTrimmingEnabled"] != null)
				config.Remove("securityTrimmingEnabled");

			if (config["ProcedureName"] != null)
			{
				ProcedureName = config["ProcedureName"];
				config.Remove("ProcedureName");
			}

			if (config["Parameters"] != null)
			{
				foreach (string parameter in config["Parameters"].Split('|'))
				{
					string[] data = parameter.Split(';');
					Parameters.Add(data[0], data[1]);
				}

				config.Remove("Parameters");
			}

			if (config.Count > 0)
			{
				string attr = config.GetKey(0);
				if (!String.IsNullOrEmpty(attr))
					throw new ProviderException("Unrecognized attribute: " + attr);
			}
		}
		#endregion

		#endregion

		#region Private Methods

		#region // SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader reader) //
		/// <summary>
		/// Create SiteMapNode From DataReader
		/// </summary>
		/// <param name="reader">Data Source</param>
		/// <returns>create node</returns>
		private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader reader)
		{
			if (reader.IsDBNull(_indexID))
				throw new ProviderException(_errmsg1);

			int id = reader.GetInt32(_indexID);

			if (_nodes.ContainsKey(id))
				throw new ProviderException(_errmsg2);

			string title = reader.IsDBNull(_indexTitle) ? null : reader.GetString(_indexTitle).Trim();
			string url = reader.IsDBNull(_indexUrl) ? null : reader.GetString(_indexUrl).Trim();
			string description = reader.IsDBNull(_indexDesc) ? null : reader.GetString(_indexDesc).Trim();
			string roles = reader.IsDBNull(_indexRoles) ? null : reader.GetString(_indexRoles).Trim();

			string[] rolelist = null;
			if (!String.IsNullOrEmpty(roles))
				rolelist = roles.Split(new char[] { ',', ';' }, 512);

			SiteMapNode node = new SiteMapNode(this, id.ToString(), url, title, description, rolelist, null, null, null);

			_nodes.Add(id, node);

			return node;
		}
		#endregion

		#region // SiteMapNode GetParentNodeFromDataReader(DbDataReader reader) //
		/// <summary>
		/// Get Parent Node From DataReader
		/// </summary>
		/// <param name="reader">Data Source</param>
		/// <returns>Selected node</returns>
		private SiteMapNode GetParentNodeFromDataReader(DbDataReader reader)
		{
			if (reader.IsDBNull(_indexParent))
				throw new ProviderException(_errmsg3);

			int pid = reader.GetInt32(_indexParent);

			if (!_nodes.ContainsKey(pid))
				throw new ProviderException(_errmsg4);

			return _nodes[pid];
		}
		#endregion

		#region // SiteMapNode FindChildNodeByID(SiteMapNode ParentNode, string strId) //
		/// <summary>
		/// 현재 노드의 하위 노드 중 아이디와 동일한 노드를 찾아 반환한다.
		/// </summary>
		/// <param name="ParentNode">부모 노드</param>
		/// <param name="strId">찾을 아이디</param>
		/// <returns>찾은 노드</returns>
		private SiteMapNode FindChildNodeByID(SiteMapNode ParentNode, string strId)
		{
			SiteMapNode smnFindNode = null;
			if (ParentNode != null && ParentNode.ChildNodes.Count > 0)
				foreach (SiteMapNode node in ParentNode.ChildNodes)
				{
					if (node.Key.Equals(strId))
					{
						smnFindNode = node;
						break;
					}
					else if (node.ChildNodes.Count > 0)
						smnFindNode = FindChildNodeByID(node, strId);

					if (smnFindNode != null) break;
				}

			return smnFindNode;
		}
		#endregion

		#endregion

		#region Public Methods

		#endregion

		#region override Methods

		#region // override SiteMapNode BuildSiteMap() //
		/// <summary>
		/// Build SiteMap
		/// </summary>
		/// <returns>Root SiteMapNode</returns>
		public override SiteMapNode BuildSiteMap()
		{
			lock (this)
			{
				if (_root != null)
					return _root;

				using (SqlConnection connection = new SqlConnection(_connect))
				{
					try
					{
						connection.Open();
						SqlCommand command = new SqlCommand(ProcedureName, connection);
						command.CommandType = CommandType.StoredProcedure;

						if (IsParameter)
						{
							foreach (KeyValuePair<string, object> data in Parameters)
								command.Parameters.Add(new SqlParameter(data.Key, data.Value));
						}

						SqlDataReader reader = 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())
						{
							_root = CreateSiteMapNodeFromDataReader(reader);
							AddNode(_root, null);

							while (reader.Read())
							{
								SiteMapNode node = CreateSiteMapNodeFromDataReader(reader);
								AddNode(node, GetParentNodeFromDataReader(reader));
							}
						}
					}
					finally
					{
						connection.Close();
					}
				}

				return _root;
			}
		}
		#endregion

		#region // override void AddNode(SiteMapNode node, SiteMapNode parentNode) //
		/// <summary>
		/// Add Node
		/// </summary>
		/// <param name="node">add node</param>
		/// <param name="parentNode">target parent node</param>
		protected override void AddNode(SiteMapNode node, SiteMapNode parentNode)
		{
			bool isVirture = node.Url.ToLower().StartsWith("http://");
			string strId = node.Key;
			string strUrl = node.Url;

			node.Url = isVirture ? node.Url.Remove(0, 7) : node.Url;
			base.AddNode(node, parentNode);

			if (isVirture && base.RootNode != null && base.RootNode.ChildNodes.Count > 0)
			{
				SiteMapNode childNode = FindChildNodeByID(base.RootNode, strId);

				if (childNode != null)
					childNode.Url = strUrl;
			}
		}
		#endregion

		#region // override SiteMapNode GetRootNodeCore() //
		/// <summary>
		/// Get RootNode Core
		/// </summary>
		/// <returns>Root SiteMapNode</returns>
		protected override SiteMapNode GetRootNodeCore()
		{
			BuildSiteMap();
			return _root;
		}
		#endregion

		#endregion
	}
}

 

 

Output