数据库结构
代码如下
复制代码
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
}
}