using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Web;
using System.Data;
using System.Web.Caching;
using System.Data.SqlClient;
using System.Configuration.Provider;
using System.Data.Common;
using System.Configuration;
using System.Web.Security;
using System.Web.Configuration;
using System.Security.Permissions;
namespace FStudio.Provider
{
[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
private const string _errmsg1 = "Missing node code";
private const string _errmsg2 = "Duplicate node code";
private const string _errmsg3 = "Missing parent code";
private const string _errmsg4 = "Invalid parent code";
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 = "Invalid Parent Code Length";
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 _indexCode, _indexClassName, _indexUrl, _indexInfo, _indexRoles, _indexTarget;
private Dictionary<string, SiteMapNode> _nodes = new Dictionary<string, SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;
private int _codeLength = 4;
public override void Initialize(string name, NameValueCollection config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");
// Assign the provider a default name if it doesn't have one
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");
}
// 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
if (_root != null)
return _root;
// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);
try
{
SqlCommand command = new SqlCommand("SqlSiteMap_Query", 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();
_indexCode = reader.GetOrdinal("Code");
_indexUrl = reader.GetOrdinal("Url");
_indexClassName = reader.GetOrdinal("ClassName");
_indexInfo = reader.GetOrdinal("Info");
_indexRoles = reader.GetOrdinal("Roles");
_indexTarget = reader.GetOrdinal("Target");
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);
AddNode(node, 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));
}
}
}
finally
{
connection.Close();
}
// Return the root SiteMapNode
return _root;
}
}
protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}
public override bool IsAccessibleToUser(HttpContext context, SiteMapNode node)
{
bool isUserInRole = false;
System.Collections.IList test = node.Roles;
if (node.Roles != null && (string)(node.Roles[0]) != "*")
{
// Create a SiteMapNode
foreach (string role in node.Roles)
{
if (Roles.IsUserInRole(role))
{
isUserInRole = true;
break;
}
}
}
else
{
isUserInRole = true;
}
return isUserInRole;
//return base.IsAccessibleToUser(context, node);
}
// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader reader)
{
// Make sure the node Code is present
if (reader.IsDBNull(_indexCode))
throw new ProviderException(_errmsg1);
// Get the node Code from the DataReader
string code = reader.GetString(_indexCode);
// Make sure the node Code is unique
if (_nodes.ContainsKey(code))
throw new ProviderException(_errmsg2);
// Get title, URL, description, and roles from the DataReader
string className = reader.IsDBNull(_indexClassName) ? null : reader.GetString(_indexClassName).Trim();
string url = reader.IsDBNull(_indexUrl) ? null : reader.GetString(_indexUrl).Trim();
string info = reader.IsDBNull(_indexInfo) ? null : reader.GetString(_indexInfo).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null : reader.GetString(_indexRoles).Trim();
string target = reader.IsDBNull(_indexTarget) ? null : reader.GetString(_indexTarget).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
NameValueCollection targetAttribute = new NameValueCollection();
targetAttribute.Add("target", target);
SiteMapNode node = new SiteMapNode(this, code, url, className, info, rolelist, targetAttribute, null, null);
// Record the node in the _nodes dictionary
_nodes.Add(code, node);
// Return the node
return node;
}
private SiteMapNode GetParentNodeFromDataReader(DbDataReader reader)
{
// Make sure the parent Code is present
if (reader.IsDBNull(_indexCode))
throw new ProviderException(_errmsg3);
// Get the parent code from the DataReader
string code = reader.GetString(_indexCode);
if (code.Length <= _codeLength)
throw new ProviderException(_errmsg9);
string parentCode = code.Substring(0, code.Length - 4);
// Make sure the parent code is valid
if (!_nodes.ContainsKey(parentCode))
throw new ProviderException(_errmsg4);
// Return the parent SiteMapNode
return _nodes[parentCode];
}
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;
}
}
}
/// <summary>
/// Clear Cached Site map tree.
/// </summary>
public void ClearSiteMap()
{
lock (_lock)
{
Clear();
_nodes.Clear();
_root = null;
}
}
}
}
以下是数据库脚本(MSSQL2000)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_Add]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_Del]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_Del]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_Move]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_Move]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_Query]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_Query]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_QueryChildren]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_QueryChildren]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_QueryParent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_QueryParent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_QueryRoot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_QueryRoot]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_Reset]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_Reset]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_ResetDataNum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_ResetDataNum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_Select]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_SelectClassName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_SelectClassName]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_Update]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_UpdateDataNum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_UpdateDataNum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap_UpdateDataNumList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SqlSiteMap_UpdateDataNumList]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SqlSiteMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SqlSiteMap]
GO
CREATE TABLE [dbo].[SqlSiteMap] (
[ClassID] [int] NOT NULL ,
[ClassName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Code] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DataNum] [int] NULL ,
[Url] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Roles] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Target] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Info] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SqlSiteMap] ADD
CONSTRAINT [DF_SqlSiteMap_DataNum] DEFAULT (0) FOR [DataNum],
CONSTRAINT [PK_SqlSiteMap] PRIMARY KEY CLUSTERED
(
[ClassID]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----添加分类存储过程
Create Proc SqlSiteMap_Add
@ClassName nvarchar(50),
@DataNum int ,
@Url nvarchar(200),@Roles nvarchar(200),@Target nvarchar(50),
@Info nvarchar(1000),
@ParentID int -- 0表示根类别
As
Declare @EditCode int
Declare @StepLen int
Declare @matchStr nvarchar(50)
Declare @typeCode nvarchar(50)
Declare @Code nvarchar(200)
Declare @MyCode nvarchar(200)
Declare @ParentCode nvarchar(200)
Declare @selfCode int
Set @editCode=1
Set @StepLen=4
Set @matchStr=REPLICATE('_',@StepLen) --4个_
set @typeCode=''
Set @Code=''
Set @MyCode=''
Set @selfCode=0
Set @ParentCode=''
Select @ParentCode=Code From [SqlSiteMap] Where ClassID=@ParentID
--//*
--判断当前分类编号是否有对应数据，当不允许在父类下添加数据时做下面的检测
--If Len(@ParentCode)>=@StepLen
--Begin
--If (Select Count(ArticleID) From [Article] Where ClassID=(Select ClassID From [ArticleClass] Where Code=@ParentCode) ) >0
--Begin
--RaisError ('父类下有数据,请将其数据转移后再添加',16,1)
--return
--End
--End
--*//
If(@editCode=1)
Begin
--获取子类中编号最大的Code,column.ParentCode + matchStr中
Select Top 1 @MyCode= Code From [SqlSiteMap] Where Code Like @ParentCode + @matchStr Order By Code DESC
If @@ROWCOUNT >0
Begin
Set @selfCode=Cast(Right(@MyCode,@StepLen) As Int ) +1
Set @typeCode=Replicate('0',@StepLen-1) + Cast(@selfCode As nvarchar)
Set @typeCode=Right(@typeCode,@StepLen)
Set @typeCode=@ParentCode + @TypeCode
End
Else
Begin
Set @typeCode=@ParentCode +Replicate('0',@StepLen-1)+'1'
End
End
Declare @ClassID int
Set @ClassID=0
--获取最大ClassID
Select @ClassId=Max(ClassID) From [SqlSiteMap]
If Not @ClassID Is Null
Begin
Set @ClassId=@ClassID +1
End
Else
Begin
Set @ClassID=1
End
Insert into [SqlSiteMap]
(ClassID,ClassName,Code,DataNum,[Url],[Roles],[Target], Info)
values
(@ClassID,@ClassName,@typeCode,@DataNum,@Url,@Roles,@Target, @Info)
Select @ClassID As ClassID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----删除一个分类，只允许删除没有字类的分类
Create Proc SqlSiteMap_Del
@ClassID int
As
If (Select Count(ClassID) From[SqlSiteMap] Where Code Like(Select Code From [SqlSiteMap] Where ClassID=@ClassID)+'%' And ClassId <> @ClassId ) >0
Begin
RaisError ('不能删除带有子类的分类',16,1)
Return
End
----当前分类下面是否有数据,因为前面判断了分类是最终叶分类,故这里直接用ClassID=@ClassID
--If (Select Count(ArticleID) From [Article] Where ClassId=@ClassID) >0
--Begin
--RaisError ('所删除的类别下有文章',16,1)
--Return
--End
Declare @Code nvarchar(200)
Declare @Value int
Set @Value=0
Select @Code=[Code],@Value=[DataNum] From [SqlSiteMap] Where [ClassID]=@ClassID
Update [SqlSiteMap] Set [DataNum]=[DataNum] - @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<=Len(@Code) And Code=Left(@Code,Len(Code)))
Delete From SqlSiteMap Where ClassID=@ClassID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
---移动分类的排序
Create Proc SqlSiteMap_Move
@ClassID int,
@IsUp bit=1
As
Declare @maskStr nvarchar(200)
Declare @tempStr nvarchar(200)
Declare @Code nvarchar(200)
Set @Code=''
Set @tempStr=''
Select @Code=Code From [SqlSiteMap] Where ClassID=@ClassID
Set @maskStr=REPLICATE(N'-',Len(@Code))
If @Code !='' And ( (Len(@Code) % 4) =0 )
Begin
If(@isUp=1)
Begin
If(Len(@Code) > 4)
Begin
Select Top 1 @tempStr=Code From [SqlSiteMap] Where Len(Code)=Len(@Code) And Code < @Code And Left(Code,Len(Code)-4)=Left(@Code,Len(@Code)-4) Order By Code DESC
End
Else
Begin
Select Top 1 @tempStr=Code From [SqlSiteMap] Where Len(Code)=Len(@Code) And Code < @Code Order By Code DESC
End
End
Else
Begin
If(Len(@Code) >4)
Begin
Select Top 1 @tempStr=Code From [SqlSiteMap] Where Len(Code)=Len(@Code) And Code > @Code And Left(Code,Len(Code)-4)=Left(@Code,Len(@Code)-4) Order By Code ASC
End
Else
Begin
Select Top 1 @tempStr=Code From [SqlSiteMap] Where Len(Code)=Len(@Code) And Code >@Code Order By Code ASC
End
End
End
-- //已经是最前(最后)
If @tempStr Is Null Or RTrim(LTrim(@tempStr))=''
Begin
return
End
Declare @CodeLen int
Declare @MAXLEN int
Set @CodeLen=Len(@Code)
Set @MAXLEN=200
--//设置目标类，以及目标类的子类为----0001(目标类)或----00010002(子类)为形式
Update [SqlSiteMap] Set Code=@maskStr +Substring(code,@CodeLen +1,@MAXLEN) Where Left(code,@CodeLen)=@tempStr
--//更新当前交换类(包括子类)Code为目标类Code
Update [SqlSiteMap] Set Code=@tempStr +Substring(Code,@CodeLen+1,@MAXLEN) Where Left(code,@CodeLen)=@Code
--//更新目标类（包括子类)Code为当前交换类Code
Update [SqlSiteMap] Set Code=@Code +Substring(code,@CodeLen +1,@MAXLEN) Where Left(code,@CodeLen)=@maskStr
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----获取整个分类信息
CREATE Proc SqlSiteMap_Query
As
Select [ClassID],[ClassName],[Code],[Url],[Roles],[Target], [DataNum],[Info] From [SqlSiteMap] Order By [Code]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----获取指定类的子类，并包括自身
Create Proc SqlSiteMap_QueryChildren
@ClassID int
As
Declare @Code nvarchar(200)
Select @Code=[Code] From [SqlSiteMap] Where [ClassID]=@ClassID
Select [ClassID],[ClassName],[Code],[Url],[Roles],[Target], [DataNum]
From [SqlSiteMap] Where Code Like @Code +'%' Order By Code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-----获取指定分类的父分类信息
Create Proc SqlSiteMap_QueryParent
@ClassID int
As
Declare @ClassCode nvarchar(200)
Select @ClassCode=Code From [SqlSiteMap] Where ClassId=@ClassID
Select ClassID,ClassName,Code,[Url],[Roles],[Target], DataNum
From [SqlSiteMap]
Where Len(Code)<=Len(@ClassCode)
And Code = Left(@ClassCode,Len(Code))
Order By Code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-----获取顶级分类列表
Create Proc SqlSiteMap_QueryRoot
AS
Select [ClassID],[ClassName],[Code],[Url],[Roles],[Target], [DataNum] From [SqlSiteMap] Where Len(Code)=4 Order By Code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-------重置所有分类为根分类
Create Proc SqlSiteMap_Reset
As
Declare @code nvarchar(200)
Declare @i int
Set @Code=''
Set @i=1
----修改DataNum列
Declare @Total int
Declare SqlSiteMap_Cursor_Order Cursor For
Select Code From [SqlSiteMap] Order By Code
Open SqlSiteMap_Cursor_Order
Fetch Next From SqlSiteMap_Cursor_Order
Into @Code
WHile @@FETCH_STATUS=0
Begin
Select @Total= Sum(DataNum) From [SqlSiteMap] Where Code Like @Code + Replicate('_',4)
If Not( @Total Is Null)
Begin
Update [SqlSiteMap] Set DataNum=DataNum - @Total WHERE Current Of SqlSiteMap_Cursor_Order
End
Set @Total=0
Fetch Next From SqlSiteMap_Cursor_Order Into @Code
End
Close SqlSiteMap_Cursor_Order
DEALLOCATE SqlSiteMap_Cursor_Order
Declare SqlSiteMap_Cursor CURSOR For
Select CODE From [SqlSiteMap]
Open SqlSiteMap_Cursor
Fetch Next From SqlSiteMap_Cursor
WHILE @@FETCH_STATUS=0
Begin
Set @Code=Replicate(N'0',4) + Cast(@i as nvarchar)
Set @Code=Right(@Code,4)
Update [SqlSiteMap] Set Code= @Code WHERE Current Of SqlSiteMap_Cursor
Set @i=@i+1
Fetch Next From SqlSiteMap_Cursor
End
Close SqlSiteMap_Cursor
DEALLOCATE SqlSiteMap_Cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Proc SqlSiteMap_ResetDataNum
@ClassID int
As
Declare @Code nvarchar(200)
Declare @Value int
Set @Value=1
Select @Code=[Code],@Value=[DataNum] From [SqlSiteMap] Where ClassID=@ClassID
Update [SqlSiteMap] Set [DataNum]=[DataNum] - @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<=Len(@Code) And Code=Left(@Code,Len(Code)))
Update [SqlSiteMap] Set [DataNum]=0 Where [Code] Like @Code + '%'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----选择存储过程
Create PROCEDURE SqlSiteMap_Select
@ClassID int
AS
SELECT [ClassID],[ClassName],[Code],[DataNum],[Url],[Roles],[Target], [Info]
FROM [SqlSiteMap]
WHERE
[ClassID]=@ClassID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----获取指定分类的分类名称
Create Proc SqlSiteMap_SelectClassName
@ClassID int
AS
Select [ClassName] From [SqlSiteMap] Where [ClassID]=@ClassID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----修改分类存储过程
Create Proc SqlSiteMap_Update
@ClassID int , --需要修改的ClassID
@ClassName nvarchar(50),
@Url nvarchar(200),@Roles nvarchar(200),@Target nvarchar(50),
@Info nvarchar(1000),
@ParentID int
As
Declare @EditCode int
Declare @StepLen int
Declare @matchStr nvarchar(50)
Declare @typeCode nvarchar(50)
Declare @Code nvarchar(200)
Declare @MyCode nvarchar(200)
Declare @ParentCode nvarchar(200)
Declare @selfCode int
Set @editCode=0
Set @StepLen=4
Set @matchStr=REPLICATE('_',@StepLen) --4个_
set @typeCode=''
Set @Code=''
Set @MyCode=''
Set @selfCode=0
Set @ParentCode=''
Select @ParentCode=Code From [SqlSiteMap] Where ClassID=@ParentID
Select @Code=Code From [SqlSiteMap] Where ClassID=@ClassID
--//*
--判断当前分类编号是否有对应数据，当不允许在父类下添加数据时做下面的检测
--If Len(@ParentCode)>=@StepLen
--Begin
--If (Select Count(ArticleID) From [Article] Where ClassID=(Select ClassID From [ArticleClass] Where Code=@ParentCode) ) >0
--Begin
--RaisError ('父类下有数据,请将其数据转移后再添加',16,1)
--return
--End
--End
--*//
--修改原有类别
--确定是否要修改Code字段
--查看是否改变了直接父类别(上一级)
If @ParentCode != Left(@code,len(@code)-@StepLen)
Begin
--过滤选择自己做为父类
If(@ParentCode !=@Code)
Begin
--过滤选择自己的子类为父类
If Len(@ParentCode) > Len(@Code)
Begin
--因为 Len(@ParentCode) > Len(@Code) 所以可以Left(@ParentCode,Len(@Code))
If Left(@ParentCode,Len(@Code)) != @Code --如果相等则为选择自己的子类为父类
Begin
Set @EditCode=1
End
End
Else
Begin
Set @EditCode=1
End
End
End
If(@editCode=1)
Begin
--获取子类中编号最大的Code,column.ParentCode + matchStr中
Select Top 1 @MyCode= Code From [SqlSiteMap] Where Code Like @ParentCode + @matchStr Order By Code DESC
--是否有子类
If @@ROWCOUNT >0
Begin
Set @selfCode=Cast(Right(@MyCode,@StepLen) As Int ) +1
Set @typeCode=Replicate('0',@StepLen-1) + Cast(@selfCode As nvarchar)
Set @typeCode=Right(@typeCode,@StepLen)
Set @typeCode=@ParentCode + @TypeCode
End
Else --没有子类那么编号从1开始
Begin
Set @typeCode=@ParentCode +Replicate('0',@StepLen-1)+'1'
End
End
If (@editCode=1)
Begin
Update [SqlSiteMap] Set
ClassName=@ClassName,Code=@typeCode,[Url]=@Url,[Roles]=@Roles,[Target]=@Target, Info=@Info
where ClassID=@ClassID
End
Else
Begin
Update [SqlSiteMap] Set
ClassName=@ClassName,[Url]=@Url,[Roles]=@Roles,[Target]=@Target, Info=@Info
where ClassID=@ClassID
End
---修改子类编号(Code)
If(@editCode=1)
Begin
Update [SqlSiteMap] Set
Code=@typeCode + Right(Code,Len(Code)-Len(@Code))
Where Code Like @Code + '%'
End
----修改DataNum列
Declare @Value int
Set @Value=0
If(@editCode=1)
Begin
Select @Value=[DataNum] From [SqlSiteMap] Where ClassID =@ClassID
--原有的父类递归减少指定数目
Update [SqlSiteMap] Set [DataNum]=[DataNum] - @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<Len(@Code) And Code=Left(@Code,Len(Code)))
--现在的父类递归增加指定数目
Update [SqlSiteMap] Set [DataNum]=[DataNum] + @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<Len(@TypeCode) And Code=Left(@TypeCode,Len(Code)))
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-------更新DataNum列
Create Proc SqlSiteMap_UpdateDataNum
@ClassID int,
@IsIncrease bit=1, --增加或减少
@Value int =1
As
Declare @Code nvarchar(200)
Select @Code=[Code] From [SqlSiteMap] Where [ClassID]=@ClassID
If @IsIncrease=1
Begin
Update [SqlSiteMap] Set [DataNum]=[DataNum] + @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<=Len(@Code) And Code=Left(@Code,Len(Code)))
End
Else
Begin
Update [SqlSiteMap] Set [DataNum]=[DataNum] - @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<=Len(@Code) And Code=Left(@Code,Len(Code)))
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----批量更新列的DataNum值,@IDs输入的形式为: '1,2,234,2345,22'
Create Proc SqlSiteMap_UpdateDataNumList
@IDs nvarchar(2000),
@IsIncrease bit=1, --增加或减少
@Value int=1
As
If @IDs=''
Begin
return
End
Declare @ClassID int
Declare @Code nvarchar(200)
Declare @s int
Declare @Len int
Declare @DataLen int
Set @Len=0
Set @s=1
Set @IDs=','+@IDs+',' --变成 ',1,334,23,'这样的形式
Set @DataLen=Len(@IDs)
While @s<@DataLen
Begin
Set @s=@s+1
Set @Len=CharIndex(',',@IDs,@s)-@s
Set @ClassID=Cast(Substring(@IDs,@s,@Len) as int)
---更新操作
Select @Code=[Code] From [SqlSiteMap] Where [ClassID]=@ClassID
If @IsIncrease=1
Begin
Update [SqlSiteMap] Set [DataNum]=[DataNum] + @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<=Len(@Code) And Code=Left(@Code,Len(Code)))
End
Else
Begin
Update [SqlSiteMap] Set [DataNum]=[DataNum] - @Value Where [ClassID] In( Select ClassID From [SqlSiteMap] Where Len(Code)<=Len(@Code) And Code=Left(@Code,Len(Code)))
End
----更新操作结束
Set @s=@s+@Len
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
以下是web.config配置,需同时配置角色,用户等提供程序
<siteMap enabled="true" defaultProvider="AspNetSqlSiteMapProvider">
<providers>
<add name="AspNetSqlSiteMapProvider" type="FStudio.Provider.SqlSiteMapProvider,FStudio.Provider" securityTrimmingEnabled="true" connectionStringName="MainDB"/>
</providers>
</siteMap>
<authentication mode="Forms">
<forms loginUrl="/log.aspx" defaultUrl="~/user/UserTrade.aspx" timeout="120"/>
</authentication>
<roleManager defaultProvider="SqlProvider" enabled="true" cacheRolesInCookie="true" cookieName=".WOW52.COOKIENAME" cookieTimeout="120" cookieRequireSSL="false" cookieSlidingExpiration="true" createPersistentCookie="true" cookieProtection="All">
<providers>
<add name="SqlProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="MainDB" applicationName="WOW52.CN"/>
</providers>
</roleManager>
<membership defaultProvider="SqlProvider">
<providers>
<add name="SqlProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="MainDB" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" passwordFormat="Hashed" minRequiredNonalphanumericCharacters="0" minRequiredPasswordLength="6" requiresUniqueEmail="false" applicationName="WOW52.CN"/>
</providers>
</membership>