File: App_Code\SqlSiteMapProvider.cs
using System;
using System.Collections.Specialized;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Caching;
namespace MyNamespace
{
public class SqlSiteMapProvider : StaticSiteMapProvider
{
private bool _isInitialized = false;
private string _connectionString;
private SiteMapNode _rootNode;
public override void Initialize(string name, NameValueCollection attributes)
{
if (_isInitialized) return;
base.Initialize(name, attributes);
string connectionStringName = attributes["connectionStringName"];
if (String.IsNullOrEmpty(connectionStringName))
throw new Exception("You must provide a connectionStringName attribute");
_connectionString = WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
if (String.IsNullOrEmpty(_connectionString))
throw new Exception("Could not find connection string " + connectionStringName);
_isInitialized = true;
}
protected override SiteMapNode GetRootNodeCore()
{
return BuildSiteMap();
}
public override SiteMapNode BuildSiteMap()
{
lock (this)
{
HttpContext context = HttpContext.Current;
_rootNode = (SiteMapNode)context.Cache["RootNode"];
if (_rootNode == null)
{
HttpContext.Current.Trace.Warn("Loading from database");
Clear();
DataTable tblSiteMap = GetSiteMapFromDB();
_rootNode = GetRootNode(tblSiteMap);
AddNode(_rootNode);
BuildSiteMapRecurse(tblSiteMap, _rootNode);
SqlCacheDependency sqlDepend = new SqlCacheDependency("SiteMapDB", "SiteMap");
context.Cache.Insert("RootNode", _rootNode, sqlDepend);
}
return _rootNode;
}
}
private DataTable GetSiteMapFromDB()
{
string selectCommand = "SELECT Id,ParentId,Url,Title,Description FROM SiteMap";
SqlDataAdapter dad = new SqlDataAdapter(selectCommand, _connectionString);
DataTable tblSiteMap = new DataTable();
dad.Fill(tblSiteMap);
return tblSiteMap;
}
private SiteMapNode GetRootNode(DataTable siteMapTable)
{
DataRow[] results = siteMapTable.Select("ParentId IS NULL");
if (results.Length == 0)
throw new Exception("No root node in database");
DataRow rootRow = results[0];
return new SiteMapNode(this, rootRow["Id"].ToString(), rootRow["url"].ToString(), rootRow["title"].ToString(), rootRow["description"].ToString());
}
private void BuildSiteMapRecurse(DataTable siteMapTable, SiteMapNode parentNode)
{
DataRow[] results = siteMapTable.Select("ParentId=" + parentNode.Key);
foreach (DataRow row in results)
{
SiteMapNode node = new SiteMapNode(this, row["Id"].ToString(), row["url"].ToString(), row["title"].ToString(), row["description"].ToString());
AddNode(node, parentNode);
BuildSiteMapRecurse(siteMapTable, node);
}
}
}
}
File: Web.Config
connectionString="Data Source=.\SQLExpress;Integrated
Security=True;AttachDbFileName=|DataDirectory|SiteMapDB.mdf;User Instance=True"/>
type="MyNamespace.SqlSiteMapProvider"
connectionStringName="conSiteMap" />
/>