您一直期待的 SQL 站点映射提供程序

[SqlClientPermission(SecurityAction.Demand, Unrestricted=true)]

public class SqlSiteMapProvider : StaticSiteMapProvider

{

// static error messages omitted

const string _cacheDependencyName = "__SiteMapCacheDependency";

private string _connect;

private string _database, _table;

private bool _2005dependency = false;

private int _indexID, _indexTitle, _indexUrl, _indexDesc,

indexRoles, _indexParent;

private Dictionary<int, SiteMapNode> _nodes =

new Dictionary<int, SiteMapNode>(16);

private SiteMapNode _root;

private readonly object _lock = new object();

public override void Initialize (

string name, NameValueCollection config)

{

// Verify parameters

if (config == null) throw new ArgumentNullException("config");

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

// Add a default "description" attribute to config if the

// attribute doesn't exist or is empty

if (string.IsNullOrEmpty(config["description"]))

{

config.Remove("description");

config.Add("description", "SQL site map provider");

}

// Call the base class's Initialize method

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);

// Initialize SQL cache dependency info

string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))

{

if (String.Equals(dependency, "CommandNotification",

StringComparison.InvariantCultureIgnoreCase))

{

SqlDependency.Start(_connect);

_2005dependency = true;

}

else

{

// If not "CommandNotification", then extract

// database and table names

string[] info = dependency.Split(new char[] { ':' });

if (info.Length != 2)

throw new ProviderException(_errmsg8);

_database = info[0];

_table = info[1];

}

config.Remove("sqlCacheDependency");

}

// 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

if (_root != null) return _root;

// Query the database for site map nodes

using(SqlConnection connection = new SqlConnection(_connect))

{

SqlCommand command = new SqlCommand(

"proc_GetSiteMap", connection);

command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested

SqlCacheDependency dependency = null;

if (_2005dependency)

dependency = new SqlCacheDependency(command);

else if (!String.IsNullOrEmpty(_database) &&

!String.IsNullOrEmpty(_table))

dependency = new SqlCacheDependency(_database,

_table);

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 site map

_root = CreateSiteMapNodeFromDataReader(reader);

AddNode(_root, null);

// Build a tree of SiteMapNodes under the root node

while (reader.Read())

{

// Create another site map node and add it

AddNode(CreateSiteMapNodeFromDataReader(reader),

GetParentNodeFromDataReader(reader));

}

// Use the SQL cache dependency

if (dependency != null)

{

HttpRuntime.Cache.Insert(_cacheDependencyName,

new object(), dependency,

Cache.NoAbsoluteExpiration,

Cache.NoSlidingExpiration,

CacheItemPriority.NotRemovable,

new CacheItemRemovedCallback(

OnSiteMapChanged));

}

// Return the root SiteMapNode

return _root;

}

protected override SiteMapNode GetRootNodeCore ()

{

return BuildSiteMap();

}

// Helper methods CreateSiteMapNodeFromDataReader and

// GetParentNodeFromDataReader

}

Cache Dependencies (SQL Server 7.0 and 2000)

<add name="SiteMapConnectionString"

connectionString="

providerName="System.Data.SqlClient" />

</connectionStrings>

<system.web>

<add name="AspNetSqlSiteMapProvider"

type="SqlSiteMapProvider"

securityTrimmingEnabled="true"

connectionStringName="SiteMapConnectionString"

sqlCacheDependency="SiteMapDatabase:SiteMap" />

</providers>

</siteMap>

<add name="SiteMapDatabase"

connectionStringName="SiteMapConnectionString" />

</databases>

</sqlCacheDependency>

</caching>

</system.web>

</configuration>

Cache Dependencies (SQL Server 2005)

<add name="SiteMapConnectionString"

connectionString="

providerName="System.Data.SqlClient" />

</connectionStrings>

<system.web>

<add name="AspNetSqlSiteMapProvider"

type="SqlSiteMapProvider"

securityTrimmingEnabled="true"

connectionStringName="SiteMapConnectionString"

sqlCacheDependency="CommandNotification" />

</providers>

</siteMap>

</caching>

</system.web>

</configuration>

SQL Script for Creating a SiteMap Table

-- Create the site map node table

CREATE TABLE [dbo].[SiteMap] (

[ID] [int] NOT NULL,

[Title] [varchar] (32),

[Description] [varchar] (512),

[Url] [varchar] (512),

[Roles] [varchar] (512),

[Parent] [int]

) ON [PRIMARY]

ALTER TABLE [dbo].[SiteMap] ADD

CONSTRAINT [PK_SiteMap] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

-- Add site map nodes