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

数据库结构

代码如下	复制代码
create table category (     id                  int,                    clsno            nvarchar(50),            clsname       nvarchar(50),                     clslist            nvarchar(250),              clsparentno  nvarchar(50),         clslistlen       int )

asp.net程序 http://www.111cn.net/net/net/51292.htm

代码如下

复制代码

/**********************************************************************************  * 程序说明：     生成静态页面类(复杂型.有参数)  * 创建日期：     2009.6.13  * 修改日期：     2010.12.18  * 程序制作：     agui  * 联系方式：     mailto:354990393@qq.com   * ********************************************************************************/ 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:354990393@qq.com";         }         #endregion     } }