项目经常用到分类问题，我认为分类哪怕再简单也要存储为树结构，因为客户是上帝，不定那天他们要求的分类会成为树结构。树结构有两种方式，一种是ID ParentID，一种是编码方式例如 0001 0001 0001 标示共三级，每级编码共四位。下面给出总结的编码方式的操作类库：
View Code #region 根据Code获得级别 ////// 根据Code获得级别 /// /// Code串 /// 几位一级 ///返回级数 public static int GetDeptLevel(string strNodeCode, int LevelLen) { int level = 0; while (level * LevelLen < strNodeCode.Length && strNodeCode.Substring(level * LevelLen, LevelLen) != Convert.ToString("0").PadLeft(LevelLen, '0')) level++; return level; } #endregion #region 获取增加同级时的编码 ////// 获取增加同级时的编码 /// /// 编码 /// 一级几位 /// 总共几级 /// 数据库表明 /// 编码字段名 ///public static string GetNextSameLevel(string strCode, int iLevelLen, int iLevel, string strTableName, string strFieldName) { int level = GetDeptLevel(strCode, iLevelLen); string strLikeCode = ""; string strPre = ""; string strEnd = ""; if (level != 1) { strPre = strCode.Substring(0, (level - 1) * iLevelLen); } if (level != iLevel) { strEnd = Convert.ToString("0").PadLeft((iLevel - level) * iLevelLen, '0'); } strLikeCode = strPre + "%" + strEnd; string strSql = "SELECT " + strFieldName + " FROM " + strTableName + " WHERE " + strFieldName + " LIKE'" + strLikeCode + "' ORDER BY " + strFieldName; DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(strSql,null); return strPre + Convert.ToString(Convert.ToInt32(dt.Rows[dt.Rows.Count - 1][strFieldName].ToString().Substring((level - 1) * iLevelLen, iLevelLen)) + 1).PadLeft(iLevelLen, '0') + strEnd; } #endregion #region 获取增加下级时的编码 /// /// 获取增加下级时的编码 /// /// 编码 /// 一级几位 /// 总共几级 /// 数据库表明 /// 编码字段名 ///public static string GetNextSubLevel(string strCode, int iLevelLen, int iLevel, string strTableName, string strFieldName) { int level = GetDeptLevel(strCode, iLevelLen) + 1; string strLikeCode = ""; string strPre = ""; string strEnd = ""; strPre = strCode.Substring(0, (level - 1) * iLevelLen); if (level != iLevel) { strEnd = Convert.ToString("0").PadLeft((iLevel - level) * iLevelLen, '0'); } strLikeCode = strPre + "%" + strEnd; string strSql = "SELECT " + strFieldName + " FROM " + strTableName + " WHERE " + strFieldName + " LIKE'" + strLikeCode + "' ORDER BY " + strFieldName; DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(strSql,null); return strPre + Convert.ToString(Convert.ToInt32(dt.Rows[dt.Rows.Count - 1][strFieldName].ToString().Substring((level - 1) * iLevelLen, iLevelLen)) + 1).PadLeft(iLevelLen, '0') + strEnd; } #endregion #region 根据编码原则获取下级 #region 根据Code获取所有下级Code用符号隔开 /// /// 根据Code获取所有下级Code用符号隔开，含有本身 /// /// 编码 /// 几位一级 /// 表名 /// Code字段名 /// sql条件（没有为空） ///所有下级Code用符号隔开 public static string GetSubCodeSplitByCode(string strCode, int iLevelLen, string strTableName, string strCodeFileName, string strSqlCon) { int iDept = GetDeptLevel(strCode, iLevelLen); StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " LIKE '" + strCode.Substring(0, iLevelLen * iDept) + "%'"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null), ","); } #endregion #region 根据Code获取所有下级ID用符号隔开 ////// 根据Code获取所有下级ID用符号隔开，含有本身 /// /// 编码 /// 几位一级 /// 表名 /// Code字段名 /// Id字段名 /// sql条件（没有为空） ///所有下级ID用符号隔开 public static string GetSubIdSplitByCode(string strCode, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName, string strSqlCon) { int iDept = GetDeptLevel(strCode, iLevelLen); StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strIdFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " LIKE '" + strCode.Substring(0, iLevelLen * iDept) + "%'"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null), ","); } #endregion #region 根据Code获取所有下级Name用符号隔开 ////// 根据Code获取所有下级Name用符号隔开 /// /// 编码 /// 分割字符 /// 几位一级 /// 表名 /// Code字段名 /// Name字段名 /// sql条件（没有为空） ///public static string GetSubNameSplitByCode(string strCode, string strSplit, int iLevelLen, string strTableName, string strCodeFileName, string strNameFileName, string strSqlCon) { int iDept = GetDeptLevel(strCode, iLevelLen); StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strNameFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " LIKE '" + strCode.Substring(0, iLevelLen * iDept) + "%'"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null), strSplit); } #endregion #region 根据id获取所有下级Code用符号隔开 /// /// 根据id获取所有下级Code用符号隔开（含有本身） /// /// id /// 几位一级 /// 表名 /// code字段名 /// id字段名 /// sql条件 ///所有下级Code用符号隔开（含有本身） public static string GetSubCodeSplitById(string strId, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName, string strSqlCon) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName + " FROM " + strTableName + " WHERE " + strIdFileName + "='" + strId + "'"); DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null); if (dt.Rows.Count == 0) { return ""; } string strCode = dt.Rows[0][0].ToString().Trim(); int iDept = GetDeptLevel(strCode, iLevelLen); sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " LIKE '" + strCode.Substring(0, iLevelLen * iDept) + "%'"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null), ","); } #endregion #region 根据id获取所有下级id用符号隔开 ////// 根据id获取所有下级id用符号隔开（含有本身） /// /// id /// 几位一级 /// 表名 /// code字段名 /// id字段名 /// sql条件 ///所有下级Code用符号隔开（含有本身） public static string GetSubIdSplitById(string strId, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName, string strSqlCon) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName + " FROM " + strTableName + " WHERE " + strIdFileName + "='" + strId + "'"); DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null); if (dt.Rows.Count == 0) { return ""; } string strCode = dt.Rows[0][0].ToString().Trim(); int iDept = GetDeptLevel(strCode, iLevelLen); sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strIdFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " LIKE '" + strCode.Substring(0, iLevelLen * iDept) + "%'"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null), ","); } #endregion #region 根据id获取所有下级name用符号隔开 ////// 根据id获取所有下级name用符号隔开（含有本身） /// /// id /// 分割字符 /// 几位一级 /// 表名 /// code字段名 /// id字段名 /// name字段名 /// sql条件 ///所有下级Code用符号隔开（含有本身） public static string GetSubNameSplitById(string strId, string strSplit, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName, string strNameFileName, string strSqlCon) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName + " FROM " + strTableName + " WHERE " + strIdFileName + "='" + strId + "'"); DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null); if (dt.Rows.Count == 0) { return ""; } string strCode = dt.Rows[0][0].ToString().Trim(); int iDept = GetDeptLevel(strCode, iLevelLen); sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strNameFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " LIKE '" + strCode.Substring(0, iLevelLen * iDept) + "%'"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(),null), strSplit); } #endregion #endregion #region 根据编码原则获取上级 #region 根据Code获取所有上级Code用符号隔开 ////// 根据Code获取所有上级Code用符号隔开，含有本身，未过滤 /// /// code /// 几位一级 ///所有上级Code用符号隔开 public static string GetSupCodeSplitByCode(string strCode, int iLevelLen) { int iDept = GetDeptLevel(strCode, iLevelLen); string strTemp = ""; StringBuilder sbTemp = new StringBuilder(); int i = 0; while (i < iDept) { strTemp = strCode.Substring(0, (i + 1) * iLevelLen).PadRight(strCode.Length, '0'); if (sbTemp.ToString().Trim() == "") { sbTemp.Append(strTemp); } else { sbTemp.Append("," + strTemp); } i++; } return sbTemp.ToString(); } #endregion #region 根据Code获取所有上级id用符号隔开 ////// 根据Code获取所有上级id用符号隔开，含有本身 /// /// code /// 几位一级 ///所有上级Code用符号隔开 public static string GetSupIdSplitByCode(string strCode, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName, string strSqlCon) { string[] strSp = GetSupCodeSplitByCode(strCode, iLevelLen).Split(','); string strCon = ""; for (int i = 0; i < strSp.Length; i++) { if (strCon.Trim() == "") { strCon += ("'" + strSp[i] + "'"); } else { strCon += (",'" + strSp[i] + "'"); } } StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strIdFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " IN (" + strCon + ")"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(), null), ","); } #endregion #region 根据Code获取所有上级name用符号隔开 ////// 根据Code获取所有上级name用符号隔开，含有本身 /// /// code /// 分隔符 /// 几位一级 /// 表名 /// code字段名 /// name字段名 /// sql条件 ///所有上级name用符号隔开，含有本身 public static string GetSupNameSplitByCode(string strCode, string strSplit, int iLevelLen, string strTableName, string strCodeFileName, string strNameFileName, string strSqlCon) { string[] strSp = GetSupCodeSplitByCode(strCode, iLevelLen).Split(','); string strCon = ""; for (int i = 0; i < strSp.Length; i++) { if (strCon.Trim() == "") { strCon += ("'" + strSp[i] + "'"); } else { strCon += (",'" + strSp[i] + "'"); } } StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strNameFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strCodeFileName + " IN (" + strCon + ")"); sbSql.Append(strSqlCon.Trim()); sbSql.Append(" ORDER BY " + strCodeFileName); return ConvertColToRow(Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(), null), strSplit); } #endregion #region 根据id获取所有上级Code用符号隔开 ////// 根据id获取所有上级Code用符号隔开，含有本身 /// /// id /// 几位一级 /// 表名 /// code字段名 /// id字段名 ///所有上级Code用符号隔开，含有本身 public static string GetSupCodeSplitById(string strId, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strIdFileName + " = '" + strId + "'"); DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(), null); if (dt.Rows.Count == 0) { return ""; } return GetSupCodeSplitByCode(dt.Rows[0][0].ToString(), iLevelLen); } #endregion #region 根据id获取所有上级id用符号隔开 ////// 根据id获取所有上级id用符号隔开，含有本身 /// /// id /// 几位一级 /// 表名 /// code字段名 /// id字段名 /// sql条件 ///所有上级Code用符号隔开，含有本身 public static string GetSupIdSplitById(string strId, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName, string strSqlCon) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strIdFileName + " = '" + strId + "'"); DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(), null); if (dt.Rows.Count == 0) { return ""; } return GetSupIdSplitByCode(dt.Rows[0][0].ToString(), iLevelLen, strTableName, strCodeFileName, strIdFileName, strSqlCon); } #endregion #region 根据id获取所有上级name用符号隔开 ////// 根据id获取所有上级name用符号隔，含有本身 /// /// id /// 分割字符串 /// 几位一级 /// 表名 /// code字段名 /// id字段名 /// name字段名 /// sql条件 ///所有上级name用符号隔，含有本身 public static string GetSupNameSplitById(string strId, string strSplit, int iLevelLen, string strTableName, string strCodeFileName, string strIdFileName, string strNameFileName, string strSqlCon) { StringBuilder sbSql = new StringBuilder(); sbSql.Append(" SELECT " + strCodeFileName); sbSql.Append(" FROM " + strTableName); sbSql.Append(" WHERE " + strIdFileName + " = '" + strId + "'"); DataTable dt = Util.DbFactory.dbOperate.ExecuteDataTable(sbSql.ToString(), null); if (dt.Rows.Count == 0) { return ""; } return GetSupNameSplitByCode(dt.Rows[0][0].ToString(), strSplit, iLevelLen, strTableName, strCodeFileName, strNameFileName, strSqlCon); } #endregion #endregion