asp.net sql无限极分类实例程序

/**********************************************************************************
 * 程序说明：     生成静态页面类(复杂型.有参数)
 * 创建日期：     2009.6.13
 * 修改日期：     2010.12.18
 * 程序制作：     agui
 * 联系方式：     mailto:[email protected] 
 * ********************************************************************************/
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using PlugNT.Safe;
using PlugNT.Database.Common;
using PlugNT.Database.Common.Simple;
using PlugNT.Custom;
using PlugNT.Cms.Model;

namespace PlugNT.Cms.DAL
{
    /// <summary>
    /// 无限极分类（由于字段的长度只能嵌套25层，且只能用于少量重要型的数据存储）
    /// </summary>
    public class Category
    {

        private static string currTableName = WebConfig.TablePrefix + "category";

        #region 获取

        /// <summary>
        /// 获取clsno
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public string GetClsno(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 clsno from " + currTableName);
            strSql.Append(" where id=" + id.ToString());
            return Convert.ToString(DbHelper.GetSingle(strSql.ToString()));

        }

        /// <summary>
        /// 获取clslist
        /// </summary>
        /// <param name="clsname"></param>
        /// <returns></returns>
        public string GetClslistByName(string clsname)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 clslist from " + currTableName);
            strSql.Append(" where clsname=‘" + StringHelper.SqlFilter(clsname) + "‘");
            return Convert.ToString(DbHelper.GetSingle(strSql.ToString()));
        }

        /// <summary>
        /// 获取clslist
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public string GetClslistByNo(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 clslist from " + currTableName);
            strSql.Append(" where clsno=‘" + StringHelper.SqlFilter(clsno) + "‘");
            return Convert.ToString(DbHelper.GetSingle(strSql.ToString()));
        }

        /// <summary>
        /// 根据父级编号得到clsname
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public string GetClsnameByParentNo(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select top 1 clsname From " + currTableName);
            strSql.Append(" Where clsparentno=‘" + StringHelper.SqlFilter(clsno) + "‘");
            return DbHelper.GetSingle(strSql.ToString()).ToString();
        }

        /// <summary>
        /// 得到父级clsno
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public string GetParentClsno(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select top 1 clsparentno From " + currTableName);
            strSql.Append(" Where clsno=‘" + StringHelper.SqlFilter(clsno) + "‘");
            return DbHelper.GetSingle(strSql.ToString()).ToString();
        }

        /// <summary>
        /// 得到模型
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public CategoryInfo GetCategoryInfo(string clsno)
        {
            DataTable dt = GetCategoryTable(clsno);
            CategoryInfo model=null;
            if(dt.Rows.Count>0)
            {
                DataRow dr=dt.Rows[0];
                model=new CategoryInfo();
                model.id=(int)dr["id"];
                model.clsno=dr["clsno"].ToString();
                model.clsname=dr["clsname"].ToString();
                model.clslist=dr["clslist"].ToString();
                model.clsparentno=dr["clsparentno"].ToString();
                model.clslistlen=Int32.Parse(dr["clslistlen"].ToString());
            }
            return model;

        }

        /// <summary>
        /// 得到类别表
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public DataTable GetCategoryTable(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select top 1 * From " + currTableName);
            strSql.Append(" Where clsno=‘" + StringHelper.SqlFilter(clsno) + "‘");
            DataTable dt = DbHelper.TabQuery(strSql.ToString());
            return dt;
        }
        #endregion

        #region 添加，修改，删除操作

        /// <summary>
        /// 添加一个菜单项
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(CategoryInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into " + currTableName);
            strSql.Append(" (clsno,clsname,clslist,clsparentno,clslistlen)");

            strSql.Append(" values (");
            strSql.Append("‘" + StringHelper.SqlFilter(model.clsno) + "‘,");
            strSql.Append("‘" + StringHelper.SqlFilter(model.clsname) + "‘,");
            strSql.Append("‘" + StringHelper.SqlFilter(model.clslist) + "‘,");
            strSql.Append("‘" + StringHelper.SqlFilter(model.clsparentno) + "‘,");
            strSql.Append(" " +model.clslistlen );

            strSql.Append(")");

            return (DbHelper.ExecuteSql(strSql.ToString()) > 0) ? true : false;
        }

        /// <summary>
        /// 编辑一个菜单项
        /// </summary>
        /// <param name="model"></param>
        /// <param name="isSubModel">是否下级菜单</param>
        /// <returns></returns>
        public bool Update(CategoryInfo model, bool isSubModel)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + currTableName);
            strSql.Append(" set ");
            if (!isSubModel)
            {
                strSql.Append("clsname=‘" + StringHelper.SqlFilter(model.clsname) + "‘,");
                strSql.Append("clsparentno=‘" + StringHelper.SqlFilter(model.clsparentno) + "‘,");
            }
            strSql.Append("clslist=‘" + StringHelper.SqlFilter(model.clslist) + "‘,");
            strSql.Append("clslistlen=" + model.clslistlen );
            strSql.Append(" where clsno=‘" + StringHelper.SqlFilter(model.clsno) + "‘ ");

            //同步更新子菜单项
            DataTable  dt = GetOrderSubList(model.clsno);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    CategoryInfo imodel = new CategoryInfo();
                    string Subclslist = model.clslist + dr["clsno"].ToString().Trim() + ",";
                    imodel.clslist = Subclslist;
                    imodel.clslistlen = model.clslistlen + 1;
                    Update(imodel,true);
                }
            }
            return (DbHelper.ExecuteSql(strSql.ToString()) > 0) ? true : false;
        }

        /// <summary>
        /// 删除菜单项
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public bool Delete(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            DataTable dt = GetListRow(clsno);
            if (dt.Rows.Count > 0)
            {
                strSql.Append("Delete From " + currTableName);
                strSql.Append(" where clslist like ‘" + dt.Rows[0]["clslist"].ToString().Trim() + "%‘");
            }
            return (DbHelper.ExecuteSql(strSql.ToString()) > 0) ? true : false;
        }

        #endregion

        #region 获取列表

        /// <summary>
        /// 判断一个父类编号是否存在
        /// </summary>
        public bool ParentExists(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + currTableName);
            strSql.Append(" where clsparentno=‘" + StringHelper.SqlFilter(clsno) + "‘");
            return DbHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 获取全部菜单名及列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,clsno,clsname,clslist,clsparentno,clslistlen from " + currTableName);
            return DbHelper.TabQuery(strSql.ToString());
            //clsno,clsname,clslist,clslistlen,indexfile,listfile,showfile,createpath,ismap
        }

        /// <summary>
        /// 获取菜单列表(排序)
        /// </summary>
        /// <returns></returns>
        public DataTable GetOrderList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,clsno,clsname,clslist,clsparentno,clslistlen from " + currTableName);
            strSql.Append(" Order By clslist Asc"); // Order By clsorder Asc,clslist Asc
            return DbHelper.TabQuery(strSql.ToString());
        }

        /// <summary>
        /// 获取clsno的包含菜单列表
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public DataTable GetListRow(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 clsno,clslist,clslistlen from " + currTableName);
            strSql.Append(" where clsno=‘" + StringHelper.SqlFilter(clsno) + "‘ ");
            return DbHelper.TabQuery(strSql.ToString());
        }

        /// <summary>
        /// 获取该菜单项的所有子菜单项(因为clslist在表中都不同所以按clsorder排序无效果)
        /// </summary>
        /// <param name="clsno"></param>
        /// <returns></returns>
        public DataTable GetOrderSubList(string clsno)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,clsno,clsname,clslist,clsparentno,clslistlen from " + currTableName);
            strSql.Append(" where clsparentno=‘" + StringHelper.SqlFilter(clsno) + "‘ ");
            strSql.Append(" Order By clslist Asc");
            return DbHelper.TabQuery(strSql.ToString());
        }

        #endregion

        #region contact me
        public string Help()
        {
            return @"mailto:[email protected]";
        }
        #endregion
    }

}