應用 StaticSiteMapProvider(SiteMapProvider) 與SQL Server 資料庫製作網站地圖

筆者因為工作需要將已建立於SQL Server上具有類似SiteMap資料表資料以Tree的方式呈現，而必須客製化SiteMapProvider來當作 SiteMapDataSoucre的來源提供者，筆者進一步的研究SiteMapProvider類別後發現，可以直接實作 StaticSiteMapProvide即可，因為他是繼承了SiteMapProvider類別且已經實作大部分的Method，我們也可以節省不少 開發的時間。後來在MSDN 發現了一篇文章Site Map Providers該作者實作了一個SQLSiteMapProvider也有詳細的安裝步驟正好可以拿來使用，不過經過筆者測試後發現 SiteMapProvider本身會有Cache功能真是個不錯的元件，因為原本還擔心使用者端在瀏覽網頁時，ASP.NET網頁會不斷的讀取資料庫產 生SiteMap結構，但現在根本不需要去煩惱這些了，但是問題又來了，使用類似讀取SQL Server來客製化SiteMapProvider時，並不像XmlSiteMapProvider能夠在對應的資料有更新時自動重新產生一份新的 SiteMap資料來源，於是又開始煩惱了。再仔細的閱讀Site Map Providers文章一次後發現筆者也提到若要達到類似的功能必須依賴ASP.NET 2.0的SqlCacheDependency類別，於是又開始了研究之路，當然MSDN是個寶庫筆者恰好是個採礦工，又被我發現了寶藏，The SQL Site Map Provider You've Been Waiting For此作者加強了SQLSiteMapProvider加入了SqlCacheDependency功能，真是讓我驚喜萬分，因為省去了我不少研究及實 作的時間，但是這離我的目標還差一步，因為我同一個頁面可能會有多個不同SiteMap且來源提供者都不同，但是上述兩篇文章內的範例均只能實作一個固定 的SiteMapProvider(讀取資表的Store Procedure被寫成固定)，因此筆者回想了SiteMapDateSource通常我們都使用預設(defaultProvider)的 SiteMapProvider而忽略了可以自行設定其屬性值，筆者利用了這一點的特性將範例程式做了些許的修改，讓我們能夠將由設定 Web.config組態即可讓ASP.NET自動切換來源提供者，另外因為儲存SiteMap對應的資料表僅能依照Node ID來做排序，但有時我們必須能夠去改變真正要顯示的順序，因此，筆者額外加了一個排序功能OrderID欄位來達到我需要的目標。你參考上述兩篇文章的 步驟來進行實作及安裝，關於筆者自行可製化的部分，可能需要參考本文章才能讓你的網頁執行正常。

範例檔案下載 : SQLSiteMapProvider (實作 StaticSiteMapProvider)

步驟說明
1.建立SiteMap database資料庫
  a.建立資料表。
    CREATE TABLE [dbo].[SiteMap] ([ID] [int] NOT NULL,[Title] [varchar](32),    [Description] [varchar](512),[Url] [varchar](512),[Roles] [varchar](512),[Parent] [int],[OrderID] [int] CONSTRAINT [DF_SiteMap_OrderID] DEFAULT (99999)) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[SiteMap] ADD
        CONSTRAINT [PK_SiteMap] PRIMARY KEY CLUSTERED
        ([ID]) ON [PRIMARY]
    GO

    INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent, OrderID)
    VALUES (1 , '首頁', NULL, '~/Default.aspx', NULL, NULL,-1)
    INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
    VALUES (10, '新聞', NULL, NULL, '*', 1)
    INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent, OrderID)
    VALUES (11, '本地', 'News from greater Seattle', '~/Summary.aspx?CategoryID=0', NULL, 10, 2)
    INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent, OrderID)
    VALUES (12, '全球', 'News from around the world', '~/Summary.aspx?CategoryID=2', NULL, 10, 99)
    INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent, OrderID)
    VALUES (20, '運動', NULL, NULL, '*', 1, 3)
    INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
    VALUES (21, '棒球', 'What''s happening in baseball', '~/Summary.aspx?CategoryID=3', NULL, 20)
    GO

  b.建立查詢SiteMap資料表的預存程序。
    CREATE PROCEDURE proc_GetSiteMap @TableName varchar(50)
    AS
 DECLARE @SqlStr as varchar(250)
 SELECT @SqlStr = 'SELECT [ID], [Title],[Description], [Url], [Roles], [Parent] FROM '
 SELECT @SqlStr = @SqlStr + @TableName + ' ORDER BY [ID]'
 EXEC (@SqlStr)

2.建立SiteMapProvider類別。(請方在專案的App_Code資料夾內)
using System;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Configuration;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Configuration.Provider;
using System.Security.Permissions;
using System.Data.Common;
using System.Data;
using System.Web.Caching;

///

/// Summary description for SqlSiteMapProvider
///

[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
    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 const string _errmsg8 = "Invalid sqlCacheDependency";
    private const string _errmsg9 = "Store Procedure Not Defined"; //Add by James
    private const string _cacheDependencyName = "__SiteMapCacheDependency";

    private string _connect;              // Database Connection String
    private string _database, _table;     // Database info for SQL Server 7/2000 cache dependency
    private bool _2005dependency = false; // Database info for SQL Server 2005 cache dependency  
    private int _indexID, _indexTitle, _indexUrl, _indexDesc, _indexRoles, _indexParent;
    private Dictionary

        // Assign the provider a default name if it doesn't have one
        // 若 Web.Config 未設定 SitMapProvider 則設定預設值
        if (String.IsNullOrEmpty(name))
            name = "SqlSiteMapProvider";

        // Add a default "description" attribute to config if the
        // attribute doesn't exist or is empty
        // 若 Web.Config 內的 description 未設定時，自動加上說明文字。
        if (string.IsNullOrEmpty(config["description"]))
        {
            config.Remove("description");
            config.Add("description", "SQL site map provider");
        }

        // Call the base class's Initialize method
        // 呼叫 基底類別 StaticSiteMapProvider 的 Initialize()
        base.Initialize(name, config);

        // Initialize _connect
        // 初始化連線字串
        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);

        _StoreProcedureName = config["storeProcedureName"]; //Add by James
        if (string.IsNullOrEmpty(_StoreProcedureName))//Add by James
            throw new ProviderException(_errmsg9);

        config.Remove("storeProcedureName");

        // Initialize SQL cache dependency info
        // 初始化資料庫的資料表或資料列上建立快取項目相依性
        // 當資料表或特定資料列發生變更時，具有相依性的項目便會失效並且自快取中移除。

        string dependency = config["sqlCacheDependency"];

        if (!String.IsNullOrEmpty(dependency))
        {
            if (String.Equals(dependency, "CommandNotification", StringComparison.InvariantCultureIgnoreCase))
            {
                // SQL Server 2005 僅需再啟動Service Boker即可啟動方法如下:
                // SELECT is_broker_enabled, name FROM sys.databases  
                // GO                  
                // ALTER DATABASE CHT 
                // SET ENABLE_BROKER 
                // GO
                SqlDependency.Start(_connect);
                _2005dependency = true;
            
            }
            else
            {
                // If not "CommandNotification", then extract database and table names
                // 須先執行下列指令 :
                // 1. aspnet_regsql -S localhost -E -d CHT -ed
                // 2. aspnet_regsql -S localhost -E -d CHT -t ServicesSectionSiteMap -et

                string[] info = dependency.Split(new char[] { ':' });
                if (info.Length != 2)
                    throw new ProviderException(_errmsg8);

                _database = info[0];
                _table = info[1];
            }

            config.Remove("sqlCacheDependency");
        }

        // SiteMapProvider processes the securityTrimmingEnabled
        // attribute but fails to remove it. Remove it now so we can
        // check for unrecognized configuration attributes.

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

        // Throw an exception if unrecognized attributes remain
        if (config.Count > 0)
        {
            string attr = config.GetKey(0);
            if (!String.IsNullOrEmpty(attr))
                throw new ProviderException("Unrecognized attribute: " + attr);
        }
    }

    public override SiteMapNode BuildSiteMap()
    {
        lock (_lock)
        {
            // Return immediately if this method has been called before
            // 確認原來的Root是否存在
            if (_root != null)
                return _root;

            // Query the database for site map nodes
            // 讀取資料表
            SqlConnection connection = new SqlConnection(_connect);

            try
            {
                SqlCommand command = new SqlCommand(_StoreProcedureName, connection);
                command.CommandType = CommandType.StoredProcedure;

                // Create a SQL cache dependency if requested
                // 建立快取項目相依性
                SqlCacheDependency dependency = null;

                if (_2005dependency)
                    dependency = new SqlCacheDependency(command); //使用SQL Server 2005 Cache Dependency
                else if (!String.IsNullOrEmpty(_database) && !string.IsNullOrEmpty(_table))
                    dependency = new SqlCacheDependency(_database, _table); //使用SQL Server 2000 or  SQL Server 7.0 Cache Dependency

                connection.Open();
                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())
                {
                    // Create the root SiteMapNode and add it to the site map
                    // 建立根目錄節點
                    _root = CreateSiteMapNodeFromDataReader(reader);
                    AddNode(_root, null);

                    // Build a tree of SiteMapNodes underneath the root node
                    // 開始建立整個目錄樹
                    while (reader.Read())
                    {
                        // Create another site map node and add it to the site map
                        // 建立每個節點
                        SiteMapNode node = CreateSiteMapNodeFromDataReader(reader);
                        // 找出此節點的父節點並加入SiteMap
                        AddNode(node, GetParentNodeFromDataReader(reader));
                    }

                    // Use the SQL cache dependency
                    // 啟動網頁的 SQL 快取項目相依性
                    if (dependency != null)
                    {
                        HttpRuntime.Cache.Insert(_cacheDependencyName, new object(), dependency,
                            Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
                            new CacheItemRemovedCallback(OnSiteMapChanged));
                    }
                }
            }
            finally
            {
                connection.Close();
            }

            // Return the root SiteMapNode
            return _root;
        }
    }

   
    protected override SiteMapNode GetRootNodeCore()
    {
        lock (_lock)
        {
            BuildSiteMap();
            return _root;
        }
    }

    // Helper methods
    private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader reader)
    {
        // Make sure the node ID is present
        // 確認 Node ID 是否為空值
        if (reader.IsDBNull(_indexID))
            throw new ProviderException(_errmsg1);

        // Get the node ID from the DataReader
        // 讀取 Node ID
        int id = reader.GetInt32(_indexID);

        // 判斷 SiteMapProvider 的 ID 是否是唯一
        if (_nodes.ContainsKey(id))
            throw new ProviderException(_errmsg2);

        // Get title, URL, description, and roles from the DataReader
        // 讀取此節點的相關資料
        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();

        // If roles were specified, turn the list into a string array
        // 判斷是否設定角色權限
        string[] rolelist = null;
        if (!String.IsNullOrEmpty(roles))
            rolelist = roles.Split(new char[] { ',', ';' }, 512);

        // Create a SiteMapNode
        // 建立此節點的實體物件
        SiteMapNode node = new SiteMapNode(this, id.ToString(), url, title, description, rolelist, null, null, null);

        // Record the node in the _nodes dictionary
        // 將此節點加入 Dictionary Collection 物件內
        _nodes.Add(id, node);

        // Return the node       
        return node;
    }

    private SiteMapNode GetParentNodeFromDataReader(DbDataReader reader)
    {
        // Make sure the parent ID is present
        // 確認是否有此父節點
        if (reader.IsDBNull(_indexParent))
            throw new ProviderException(_errmsg3);

        // Get the parent ID from the DataReader
        // 將父節點轉型別為整數變數 int
        int pid = reader.GetInt32(_indexParent);

        // Make sure the parent ID is valid
        // 確認是否只有一個父節點
        if (!_nodes.ContainsKey(pid))
            throw new ProviderException(_errmsg4);

        // Return the parent SiteMapNode
        return _nodes[pid];
    }

    void OnSiteMapChanged(string key, object item, CacheItemRemovedReason reason)
    {
        // 當資料庫快取項目的資料改變時，重新讀取整個目錄數資料。
        lock (_lock)
        {
            if (key == _cacheDependencyName && reason == CacheItemRemovedReason.DependencyChanged)
            {
                // Refresh the site map
                Clear();
                _nodes.Clear();
                _root = null;
            }
        }
    }
}

3.(選擇性)必要時啟動SQL Server 2005的Service Boker。
   SELECT is_broker_enabled, name FROM sys.databases  
   GO                  
   ALTER DATABASE TestDB   // TestDB 為欲開啟Service Boker的資料庫
   SET ENABLE_BROKER 
   GO

4.(選擇性)使用aspnet_regsql指令註冊快取相依性相關功能到資料庫，並啟動資料庫的快取相依功能。
  請到命令提示字元 C:WINDOWSMicrosoft.NETFrameworkv2.0.50727 路徑下:
     a. aspnet_regsql -S localhost -E -d TestDB –ed   // TestDB 為欲啟動資料庫的快取相依功能的資料庫
     b. aspnet_regsql -S localhost -E -d TestDB -t SiteMap –et  //SitMap 為先前我們所建立的資料表名稱

5.設定Web.config組態內容。
  <connectionStrings>
    <add name="TestDBConnectionString" connectionString="Data Source=.;Initial Catalog=testDB;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <siteMap enabled="true" >
      <providers>
        <add name="SqlSiteMapProvider1"
          type="SqlSiteMapProvider"
          securityTrimmingEnabled="false"
          connectionStringName="TestDBConnectionString" sqlCacheDependency="CommandNotification" SiteMapTableName="SitMap"/>     
      </providers><!-- sqlCacheDependency="CommandNotification" 為資料庫快取相依的設定-->
    </siteMap>
    <caching>
      <sqlCacheDependency enabled="true" /><!--為資料庫快取相依的設定-->
    </caching>

6.由工具箱加入SiteMapDataSource資料來源元件到ASPX或ASCX頁面等容器。
 
7.由工具箱加入Navigation巡覽元件(TreeView、SiteMapPath、Menu)到ASPX或ASCX頁面等容器，並設定SiteMapDataSource資料來源。