无限级分类树(数据库存储)

//实体类，此类由工具自动生成
//-------------------------------------------
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;

namespace My.Entities
{
    public class CategoryInfo : IEnumerable,IEntity
    {
      #region 构造器
        //1
        public CategoryInfo(){}
        //2
        public CategoryInfo( NameValueCollection nvc )
        {
            if( !string.IsNullOrEmpty(nvc["Id"]) ){ int r; int.TryParse( nvc["Id"],out r ); this.Id = r;  }
            if( !string.IsNullOrEmpty(nvc["DateCreated"]) ){ DateTime r; DateTime.TryParse( nvc["DateCreated"],out r ); this.DateCreated = r;  }
            if( !string.IsNullOrEmpty(nvc["DateModified"]) ){ DateTime r; DateTime.TryParse( nvc["DateModified"],out r ); this.DateModified = r;  }
            if( !string.IsNullOrEmpty(nvc["UserCreated"]) ){ int r; int.TryParse( nvc["UserCreated"],out r ); this.UserCreated = r;  }
            if( !string.IsNullOrEmpty(nvc["UserModified"]) ){ int r; int.TryParse( nvc["UserModified"],out r ); this.UserModified = r;  }
            if( !string.IsNullOrEmpty(nvc["Title"]) ){ this.Title = nvc["Title"];  }
            if( !string.IsNullOrEmpty(nvc["Description"]) ){ this.Description = nvc["Description"];  }
            if( !string.IsNullOrEmpty(nvc["Icon"]) ){ this.Icon = nvc["Icon"];  }
            if( !string.IsNullOrEmpty(nvc["ParentId"]) ){ int r; int.TryParse( nvc["ParentId"],out r ); this.ParentId = r;  }
            if( !string.IsNullOrEmpty(nvc["Path"]) ){ this.Path = nvc["Path"];  }
            if( !string.IsNullOrEmpty(nvc["Depth"]) ){ int r; int.TryParse( nvc["Depth"],out r ); this.Depth = r;  }
            if( !string.IsNullOrEmpty(nvc["Width"]) ){ int r; int.TryParse( nvc["Width"],out r ); this.Width = r;  }
        }
        //3
        public CategoryInfo( DataRow row )
        {
            DataColumnCollection dcc = row.Table.Columns;

            if( dcc.Contains("Id") && DBNull.Value!=row["Id"] ){ this.Id = (int)row["Id"];  }
            if( dcc.Contains("DateCreated") && DBNull.Value!=row["DateCreated"] ){ this.DateCreated = (DateTime)row["DateCreated"];  }
            if( dcc.Contains("DateModified") && DBNull.Value!=row["DateModified"] ){ this.DateModified = (DateTime)row["DateModified"];  }
            if( dcc.Contains("UserCreated") && DBNull.Value!=row["UserCreated"] ){ this.UserCreated = (int)row["UserCreated"];  }
            if( dcc.Contains("UserModified") && DBNull.Value!=row["UserModified"] ){ this.UserModified = (int)row["UserModified"];  }
            if( dcc.Contains("Title") && DBNull.Value!=row["Title"] ){ this.Title = (string)row["Title"];  }
            if( dcc.Contains("Description") && DBNull.Value!=row["Description"] ){ this.Description = (string)row["Description"];  }
            if( dcc.Contains("Icon") && DBNull.Value!=row["Icon"] ){ this.Icon = (string)row["Icon"];  }
            if( dcc.Contains("ParentId") && DBNull.Value!=row["ParentId"] ){ this.ParentId = (int)row["ParentId"];  }
            if( dcc.Contains("Path") && DBNull.Value!=row["Path"] ){ this.Path = (string)row["Path"];  }
            if( dcc.Contains("Depth") && DBNull.Value!=row["Depth"] ){ this.Depth = (int)row["Depth"];  }
            if( dcc.Contains("Width") && DBNull.Value!=row["Width"] ){ this.Width = (int)row["Width"];  }
        }
      #endregion 构造器

      #region Public Properties
        public virtual int Id
        {
            get{ return _Id;  }
            set{ _Id = value; }
        }
        public virtual DateTime DateCreated
        {
            get{ return _DateCreated;  }
            set{ _DateCreated = value; }
        }
        public virtual DateTime DateModified
        {
            get{ return _DateModified;  }
            set{ _DateModified = value; }
        }
        public virtual int UserCreated
        {
            get{ return _UserCreated;  }
            set{ _UserCreated = value; }
        }
        public virtual int UserModified
        {
            get{ return _UserModified;  }
            set{ _UserModified = value; }
        }
        public virtual string Title
        {
            get{ return _Title;  }
            set{ _Title = value; }
        }
        public virtual string Description
        {
            get{ return _Description;  }
            set{ _Description = value; }
        }
        public virtual string Icon
        {
            get{ return _Icon;  }
            set{ _Icon = value; }
        }
        public virtual int ParentId
        {
            get{ return _ParentId;  }
            set{ _ParentId = value; }
        }
        public virtual string Path
        {
            get{ return _Path;  }
            set{ _Path = value; }
        }
        public virtual int Depth
        {
            get{ return _Depth;  }
            set{ _Depth = value; }
        }
        public virtual int Width
        {
            get{ return _Width;  }
            set{ _Width = value; }
        }
      #endregion Public Properties

      #region IEnumerable 接口
        public IEnumerator GetEnumerator()
        {
            yield return this.Id;
            yield return this.DateCreated;
            yield return this.DateModified;
            yield return this.UserCreated;
            yield return this.UserModified;
            yield return this.Title;
            yield return this.Description;
            yield return this.Icon;
            yield return this.ParentId;
            yield return this.Path;
            yield return this.Depth;
            yield return this.Width;
        }
      #endregion IEnumerable 接口

      #region ToString Method
        public override string ToString()
        {
            return ToText();
        }
        public virtual string ToString( string format)
        {
            format = format.ToLower();
            switch( format )
            {
                case "json" :
                    return ToJson();
                case "xml" :
                    return ToXml();
                case "html" :
                    return ToHtml();
                default :
                    return ToText();
            }
        }
        protected virtual string ToJson()
        {
            string s = string.Format
(
@"{{'Id':'{0}',
'DateCreated':'{1}',
'DateModified':'{2}',
'UserCreated':'{3}',
'UserModified':'{4}',
'Title':'{5}',
'Description':'{6}',
'Icon':'{7}',
'ParentId':'{8}',
'Path':'{9}',
'Depth':'{10}',
'Width':'{11}'}}",
My.Convert.JsonEncode(Id.ToString()),
My.Convert.JsonEncode(DateCreated.ToString()),
My.Convert.JsonEncode(DateModified.ToString()),
My.Convert.JsonEncode(UserCreated.ToString()),
My.Convert.JsonEncode(UserModified.ToString()),
My.Convert.JsonEncode(Title.ToString()),
My.Convert.JsonEncode(Description.ToString()),
My.Convert.JsonEncode(Icon.ToString()),
My.Convert.JsonEncode(ParentId.ToString()),
My.Convert.JsonEncode(Path.ToString()),
My.Convert.JsonEncode(Depth.ToString()),
My.Convert.JsonEncode(Width.ToString())
);
        return s;
        }
        protected virtual string ToXml()
        {
            throw new Exception( "不支持" );
        }
        protected virtual string ToHtml()
        {
            throw new Exception( "不支持" );
        }
        protected virtual string ToText()
        {
            List list = new List();
            foreach( object o in this ){
                list.Add( o.ToString() );
            }
            return string.Join( "/r/n",list.ToArray() );
        }
      #endregion ToString Method

      #region Private Member
        private int _Id = 0;
        private DateTime _DateCreated = DateTime.Now;
        private DateTime _DateModified = DateTime.Now;
        private int _UserCreated = 0;
        private int _UserModified = 0;
        private string _Title = "";
        private string _Description = string.Empty;
        private string _Icon = string.Empty;
        private int _ParentId = 0;
        private string _Path = "0";
        private int _Depth = 1;
        private int _Width = 1;
      #endregion Private Member
    }
}



//数据访问类，此类维护树节点之间的关系
//-------------------------------------------
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using My.Entities;
using My.Data;

namespace My.Data.Sqlserver
{
    // SqlServer适用的Dal类
    public class Category : ICategory
    {
      #region 构造器
        public Category(  string connectionString,string tbName )
        {
            _connectionString = connectionString;
            _tbName = tbName;
        }
      #endregion 构造器

      #region ICategoryDAL
        /**
         * 增加一
         */
        public int Add( CategoryInfo info )
        {
            string sql = GetInsertSql();
            SqlParameter[] parms = GetParameters();

            parms[0].Value = info.Id;
            parms[1].Value = info.DateCreated;
            parms[2].Value = DBNull.Value;
            parms[3].Value = info.UserCreated;
            parms[4].Value = DBNull.Value;
            parms[5].Value = info.Title;
            parms[6].Value = info.Description;
            parms[7].Value = info.Icon;
            parms[8].Value = info.ParentId;
            /// parms[9].Value = info.Path;
            /// parms[10].Value = info.Depth;
            /// parms[11].Value = info.Width;

            object o = SqlHelper.ExecuteScalar( _connectionString,CommandType.Text,sql,parms );
            return ( DBNull.Value!=o ) ? System.Convert.ToInt32(o) : 0;
        }

        /**
         * 修改一: 资料
         */
        public int Modify(CategoryInfo info)
        {
            string sql = GetUpdateSql();
            SqlParameter[] parms = GetParameters();

            parms[0].Value = info.Id;
            parms[1].Value = info.DateCreated;
            parms[2].Value = DBNull.Value;
            parms[3].Value = info.UserCreated;
            parms[4].Value = DBNull.Value;
            parms[5].Value = info.Title;
            parms[6].Value = info.Description;
            parms[7].Value = info.Icon;
            parms[8].Value = info.ParentId;
            /// parms[9].Value = info.Path;
            /// parms[10].Value = info.Depth;
            /// parms[11].Value = info.Width;

            return SqlHelper.ExecuteNonQuery( _connectionString,CommandType.Text,sql,parms );
        }

        /**
         * 删除一, 如果分类下存在分类, 则抛出错误(就是无法删除分类)
         */
        public int Remove( int id )
        {
            string sql = GetDeleteSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;

            return SqlHelper.ExecuteNonQuery( _connectionString,CommandType.Text,sql,parm );
        }

        // 移动到
        public int MoveTo( int id, int parentId, int width )
        {
            string sql = GetMoveToSql();
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter( "@Id",SqlDbType.Int ),
                new SqlParameter( "@NewParentId",SqlDbType.Int ),
                new SqlParameter( "@NewWidth",SqlDbType.Int )
            };
            parms[0].Value = id;
            parms[1].Value = parentId;
            parms[2].Value = width;
            
            return SqlHelper.ExecuteNonQuery( _connectionString,CommandType.Text,sql,parms );
        }
        //[重载]
        public int MoveTo( int id, int parentId )
        {
            return MoveTo( id, parentId, 0 );
        }


        /**
         * 查询一
         */
        public CategoryInfo GetDetail( string id )
        {
            string sql = GetDetailSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;
            
            DataTable table = SqlHelper.ExecuteDataset( _connectionString,CommandType.Text,sql,parm ).Tables[0];

            if ( 1 > table.Rows.Count )
                 return new CategoryInfo();
            else
                 return new CategoryInfo( table.Rows[0] );
        }
        //[重载]
        public CategoryInfo GetDetail( int id )
        {
            return GetDetail( id.ToString() );
        }

        /**
         * 查询父节点
         */
        public CategoryInfo GetParent( int id )
        {
            string sql = GetParentSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;
            
            DataTable table = SqlHelper.ExecuteDataset( _connectionString,CommandType.Text,sql,parm ).Tables[0];

            if ( 1 > table.Rows.Count )
                 return new CategoryInfo();
            else
                 return new CategoryInfo( table.Rows[0] );
        }

        /**
         * 查询所有祖先节点,包含自身
         *
         */
        public IList GetAncestors( int id )
        {
            string sql = GetAncestorsSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;
            
            DataTable table = SqlHelper.ExecuteDataset( _connectionString,CommandType.Text,sql,parm ).Tables[0];
            
            IList list = new List();
            foreach( DataRow row in table.Rows )
                list.Add( new CategoryInfo(row) );

            return list;
        }

        /**
         * 查询某分类的所有孩子节点
         */
        public IList GetChildren( int id )
        {
            string sql = GetChildrenSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;
            
            DataTable table = SqlHelper.ExecuteDataset( _connectionString,CommandType.Text,sql,parm ).Tables[0];

            IList list = new List();
            foreach( DataRow row in table.Rows )
                list.Add( new CategoryInfo(row) );

            return list;
        }

        /**
         * 查询树, 以某一节点作为根节点
         */
        public IList GetList( int id )
        {         
            string sql = GetListSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;
            
            DataTable table = SqlHelper.ExecuteDataset( _connectionString,CommandType.Text,sql,parm ).Tables[0];

            IList ret = new List();
            DataRow current = null;
            Stack stack = new Stack();

            if( 0 == id )  // 从虚拟的根节点开始
            {
                current = table.NewRow();

                current[PARM_Id] = 0;
                current[PARM_DateCreated] = DateTime.Now;
                current[PARM_DateModified] = DBNull.Value;
                current[PARM_UserCreated] = 0;
                current[PARM_UserModified] = DBNull.Value;
                current[PARM_Title] = "根";
                current[PARM_Description] = "虚拟根节点";
                current[PARM_Icon] = "";
                current[PARM_ParentId] =  -100000;  // 一个不可能存在的ID;
                current[PARM_Path] = "";
                current[PARM_Depth] = 0;
                current[PARM_Width] = 1;
            }
            else
            {
                current = GetNode( table, id );
            }

            int i = 0;
            while( null != current )
            {
                // first child
                while (null != current)
                {
                    ret.Add( new CategoryInfo(current) );
                    stack.Push(current);
                    current = GetFirstChild( table, current );
                    i = i + 1;
                }

                // next sibling
                while (null == current && 0 != stack.Count)
                {
                    current = stack.Pop();
                    current = GetNextSibling( table, current );
                }
                //if ( table.Rows.Count <= i )  break;
            }
            return ret;
        }
        public IList GetTree( int id )
        {
            return GetList(id);
        }
        /**
         * 某一节点为根节点的树的所有节点的id
         */
        public string GetIdList( int id )
        {
            string sql = GetIdListSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;
            
            DataTable table = SqlHelper.ExecuteDataset( _connectionString,CommandType.Text,sql ).Tables[0];
            
            string[] ss = new string[table.Rows.Count];
            int i = 0;
            foreach( DataRow row in table.Rows ){
                ss[i] = row["Id"].ToString(); ++i;     }
                
            return string.Join( ",",ss );
        }
        
        /**
         * 是否存在节点
         */
        public bool Exists( int id )
        {
            string sql = GetExistsSql();
            SqlParameter parm = new SqlParameter( "@Id",SqlDbType.Int );
            parm.Value = id;
            
            int i = (int)SqlHelper.ExecuteScalar( _connectionString,CommandType.Text,sql,parm );
            if( 0= rows.Length ) { return null;    }
            return rows[0];
        }

        /**
         * NextSibling
         */
        private DataRow GetNextSibling( DataTable table, DataRow current )
        {
            DataRow[] rows = table.Select( "ParentId="+current["ParentId"]
                                         + " And Width="+((int)current["Width"]+1) );
            if ( 0 >= rows.Length ) { return null;    }
            return rows[0];
        }
      #endregion

      #region Private Member
        private string _connectionString = "";
        private string _tbName = "";
      #endregion

      #region Protected
        protected const string PARM_Id = "Id";
        protected const string PARM_DateCreated = "DateCreated";
        protected const string PARM_DateModified = "DateModified";
        protected const string PARM_UserCreated = "UserCreated";
        protected const string PARM_UserModified = "UserModified";
        protected const string PARM_Title = "Title";
        protected const string PARM_Description = "Description";
        protected const string PARM_Icon = "Icon";
        protected const string PARM_ParentId = "ParentId";
        protected const string PARM_Path = "Path";
        protected const string PARM_Depth = "Depth";
        protected const string PARM_Width = "Width";

        protected virtual SqlParameter[] GetParameters()
        {
            /// string sql = GetInsertSql();
            /// SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet( _connectionString,sql );
            /// if( null==parms )
            /// {
                SqlParameter[] parms = new SqlParameter[]{
                    new SqlParameter( PARM_Id,SqlDbType.Int ),
                    new SqlParameter( PARM_DateCreated,SqlDbType.DateTime ),
                    new SqlParameter( PARM_DateModified,SqlDbType.DateTime ),
                    new SqlParameter( PARM_UserCreated,SqlDbType.Int ),
                    new SqlParameter( PARM_UserModified,SqlDbType.Int ),
                    new SqlParameter( PARM_Title,SqlDbType.NVarChar,50 ),
                    new SqlParameter( PARM_Description,SqlDbType.NVarChar,256 ),
                    new SqlParameter( PARM_Icon,SqlDbType.NVarChar,256 ),
                    new SqlParameter( PARM_ParentId,SqlDbType.Int )
                    /// new SqlParameter( PARM_Path,SqlDbType.NVarChar,512 ),
                    /// new SqlParameter( PARM_Depth,SqlDbType.Int ),
                    /// new SqlParameter( PARM_Width,SqlDbType.Int )
                };
            ///     SqlHelperParameterCache.CacheParameterSet( _connectionString,sql,parms );
            /// }
            return parms;
        }
        
        /**
         * 1.根节点的 Id=0,Path='',Width=1,Depth=0
         * 2.确保父结点存在
         * 3.同一层次中不能存在同名结点
         */
        protected virtual string GetInsertSql()
        {
            string[] columns = new string[]{
                PARM_DateCreated,
                PARM_DateModified,
                PARM_UserCreated,
                PARM_UserModified,
                PARM_Title,
                PARM_Description,
                PARM_Icon,
                PARM_ParentId,
                PARM_Path,
                PARM_Depth,
                PARM_Width
            };
            string[] values = new string[columns.Length];
            int i = 0;
            foreach( string s in columns ){
                values[i] = "@"+s; ++i;    }

            return string.Format(
@"Declare @Path VarChar(512),@Width Int,@Depth Int;
If @ParentId=0 Begin
    Set @Path='0';
    Set @Depth=1; End
Else
    Select @Path=Path+','+Cast(@ParentId As VarChar),@Depth=Depth+1 From {0} Where Id=@ParentId 
If @Path Is Null Return;
If ( Select Count(*) From {0} Where ParentId=@ParentId And Title=@Title )>0 Return;
Select @Width=Count(*)+1 From {0} Where ParentId=@ParentId;
Insert Into {0}({1})Values({2});
Select @@Identity",_tbName,string.Join(",",columns),string.Join(",",values) );
        }
        protected virtual string GetUpdateSql()
        {
            string[] columns = new string[]{
                PARM_DateModified,
                PARM_UserModified,
                PARM_Title,
                PARM_Description,
                PARM_Icon,
            };
            string[] values = new string[columns.Length];
            int i = 0;
            foreach( string s in columns ){
                values[i] = string.Concat( s,"=@",s ); ++i;  }

            return string.Concat( "Update ",_tbName,@" Set ",string.Join(",",values)," Where Id=@Id;" );
        }
        protected virtual string GetDeleteSql()
        {
            return string.Format(
@"Declare @Width Int,@ParentId Int;
Select @Width=Width,@ParentId=ParentId From {0} Where Id=@Id And Not Exists(Select Id From {0} Where ParentId=@Id);
If @Width Is Null Return;
Update {0} Set Width=Width-1 Where ParentId=@ParentId And Width>@Width
Delete From {0} Where Id=@Id And @Width Is Not Null;",_tbName );
        }
        /**
         * 移动涉及的变量有 Name,OldParentId,NewParentId(用户参数),OldPath,NewPath,OldWidth,NewWidth(用户参数,提供默认值),NewDepth
         * 处理流程:
         * 1. 查找出指定移动的节点,检查节点是否存在
         * 2. 检查指定要移动到的父节点是否存在
         * 3. 检查该父节点下是否已经存在同名的节点,不能把父分类移动到子孙分类
         * 4. 在旧层次中删除节点顺序位置
         * 5. 在新层次中腾出节点顺序位置
         * 6. 更新子树节点的path和depth为新的值(不包括子树的根节点)
         * 7. 更新指定节点为新父节点的数据
         *
         * 接口变量:@Id, 当前要移动的id
         *          @NewParentId,新的父id
         *          @NewWidth,指定放在新层次中的顺序,输入小于等于0的数表示插入最末尾
         */
        protected virtual string GetMoveToSql()
        {
            return string.Format(
@"Declare @Title NVarChar(50),@OldParentId Int,@OldPath VarChar(512),@OldWidth Int,@OldDepth Int;
Declare @NewPath VarChar(512),@NewDepth Int,@DefaultNewWidth Int,@DifferenceDepth Int;
Select @Title=Title,@OldParentId=ParentId,@OldPath=Path,@OldWidth=Width,@OldDepth=Depth From {0} Where Id=@Id;
If @OldPath Is Null Return;
If @NewParentId=0 Begin
    Set @NewPath='0';
    Set @NewDepth=1; End
Else
    Select @NewPath=Path+','+Cast(@NewParentId As VarChar),@NewDepth=Depth+1 From {0} Where Id=@NewParentId;
If @NewPath Is Null Return;
If( Select Count(*) From {0} Where ParentId=@NewParentId And (Title=@Title Or ','+Path+',' Like('%,'+Cast(@Id as VarChar)+',%') ) )>0 Return;
Update {0} Set Width=Width-1 Where ParentId=@OldParentId And Width>@OldWidth;
Select @DefaultNewWidth=Count(*)+1 From {0} Where ParentId=@NewParentId;
If @NewWidth Is Null Or @NewWidth<1 Or @NewWidth>@DefaultNewWidth Set @NewWidth = @DefaultNewWidth;
Update {0} Set Width=Width+1 Where ParentId=@NewParentId And Width>=@NewWidth;
Set @DifferenceDepth=@NewDepth-@OldDepth;
Update {0} Set Path=Replace(Path,@OldPath+',',@NewPath+','),Depth=Depth+@DifferenceDepth Where Path+',' Like(@OldPath+','+Cast(@Id As VarChar)+',%');
Update {0} Set ParentId=@NewParentId,Path=@NewPath,Width=@NewWidth,Depth=@NewDepth Where Id=@Id",_tbName );
        }
        
        // 接口变量:@Id
        protected virtual string GetDetailSql()
        {
            string[] columns = new string[]{
                PARM_Id,
                PARM_DateCreated,
                PARM_DateModified,
                PARM_UserCreated,
                PARM_UserModified,
                PARM_Title,
                PARM_Description,
                PARM_Icon,
                PARM_ParentId,
                PARM_Path,
                PARM_Depth,
                PARM_Width
            };
            
            return string.Format( "Select {1} From {0} Where Id=@Id",_tbName,string.Join(",",columns) );
        }
        // 接口变量:@Id
        protected virtual string GetParentSql()
        {
            string sql = GetDetailSql();  //这里调用了Detail的Sql
            return Regex.Replace( sql,"@Id",string.Format("(Select ParentId From {0} Where Id=@Id)",_tbName) );
        }
        // 接口变量:@Id
        protected virtual string GetAncestorsSql()
        {
            string[] columns = new string[]{
                PARM_Id,
                PARM_DateCreated,
                PARM_DateModified,
                PARM_UserCreated,
                PARM_UserModified,
                PARM_Title,
                PARM_Description,
                PARM_Icon,
                PARM_ParentId,
                PARM_Path,
                PARM_Depth,
                PARM_Width
            };
            
            return string.Format( 
@"Select {1} From {0} Where CharIndex(
    ','+Cast(Id as VarChar)+',', ','+(Select (Path + ',' + Cast(Id as VarChar)) From {0} Where Id=@Id)+','
    )>0 Order By Depth ASC",_tbName,string.Join(",",columns) );
        }
        // 接口变量:@Id
        protected virtual string GetChildrenSql()
        {
            string[] columns = new string[]{
                PARM_Id,
                PARM_DateCreated,
                PARM_DateModified,
                PARM_UserCreated,
                PARM_UserModified,
                PARM_Title,
                PARM_Description,
                PARM_Icon,
                PARM_ParentId,
                PARM_Path,
                PARM_Depth,
                PARM_Width
            };
            
            return string.Format( @"Select {1} From {0} Where ParentId=@Id", _tbName,string.Join(",",columns) );
        }
        // 接口变量:@Id
        protected virtual string GetListSql()
        {
            string[] columns = new string[]{
                PARM_Id,
                PARM_DateCreated,
                PARM_DateModified,
                PARM_UserCreated,
                PARM_UserModified,
                PARM_Title,
                PARM_Description,
                PARM_Icon,
                PARM_ParentId,
                PARM_Path,
                PARM_Depth,
                PARM_Width
            };
            
            return string.Format(
@"Declare @Path VarChar(512);
If @Id Is Null Or @Id=0
    Set @Path='%';
Else
    Select @Path=Path+','+Cast(Id As VarChar)+',%' From {0} Where Id=@Id;
Select {1} From {0} Where Path+','+Cast(Id As VarChar)+',' Like(@Path)",_tbName,string.Join(",",columns) );
        }
        // 接口变量:@Id,Sql与GetListSql是一样的
        protected virtual string GetIdListSql()
        {
            return string.Format(
@"Declare @Path VarChar(512);
If @Id=0
    Set @Path='%';
Else
    Select @Path=Path+','+Cast(Id As VarChar)+',%' From {0} Where Id=@Id;
If @Path Is Null Return;
Select Id From {0} Where Path+','+Cast(Id As VarChar)+',' Like(@Path)",_tbName );
        }
        /**
         * 修复节点关系,
         * 节点关系有时后可能会被程序的Bug破坏,
         * 本修复是假设ParentId还正常的情况下,修复Path,Depth,Width关系
         *
         * 接口变量:@Id
         */
        protected virtual string GetRepairNodeSql()
        {
            return string.Format( 
@"Declare @ParentId Int,@Path VarChar(512),@Depth Int;
Select @ParentId=ParentId From {0} Where Id=@Id;
If @ParentId Is Null Return;
If @ParentId=0 Begin
    Set @Path='0';
    Set @Depth=1; End
Else
    Select @Path=Path+','+Cast(Id As VarChar),@Depth=Depth+1 From {0} Where Id=@ParentId;
Update {0} Set Path=@Path,Depth=@Depth Where Id=@Id;
Declare @Width Int;
Set @Width=0;
Update {0} Set Width=@Width,@Width=@Width+1 Where ParentId=@ParentId",_tbName );
        }
        
        // 接口变量:@Id 或 @Title
        protected virtual string GetExistsSql()
        {
            return string.Format( @"Select Count(*) From {0} Where Id=@Id",_tbName );
        }
      #endregion Protected
    }
}