项目经常用到分类问题，我认为分类哪怕再简单也要存储为树结构，因为客户是上帝，不定那天他们要求的分类会成为树结构。树结构有两种方式，一种是ID ParentID，一种是编码方式例如 0001 0001 0001 标示共三级，每级编码共四位。下面给出总结的编码方式的操作类库：
View Code
#region 根据Code获得级别 /// <summary> /// 根据Code获得级别 /// </summary> /// <param name="strNodeCode">Code串</param> /// <param name="LevelLen">几位一级</param> /// <returns>返回级数</returns> 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 获取增加同级时的编码 /// <summary> /// 获取增加同级时的编码 /// </summary> /// <param name="strCode">编码</param> /// <param name="iLevelLen">一级几位</param> /// <param name="iLevel">总共几级</param> /// <param name="strTableName">数据库表明</param> /// <param name="strFieldName">编码字段名</param> /// <returns></returns> 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 获取增加下级时的编码 /// <summary> /// 获取增加下级时的编码 /// </summary> /// <param name="strCode">编码</param> /// <param name="iLevelLen">一级几位</param> /// <param name="iLevel">总共几级</param> /// <param name="strTableName">数据库表明</param> /// <param name="strFieldName">编码字段名</param> /// <returns></returns> 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用符号隔开 /// <summary> /// 根据Code获取所有下级Code用符号隔开，含有本身 /// </summary> /// <param name="strCode">编码</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">Code字段名</param> /// <param name="strSqlCon">sql条件（没有为空）</param> /// <returns>所有下级Code用符号隔开</returns> 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用符号隔开 /// <summary> /// 根据Code获取所有下级ID用符号隔开，含有本身 /// </summary> /// <param name="strCode">编码</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">Code字段名</param> /// <param name="strIdFileName">Id字段名</param> /// <param name="strSqlCon">sql条件（没有为空）</param> /// <returns>所有下级ID用符号隔开</returns> 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用符号隔开 /// <summary> /// 根据Code获取所有下级Name用符号隔开 /// </summary> /// <param name="strCode">编码</param> /// <param name="strSplit">分割字符</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">Code字段名</param> /// <param name="strNameFileName">Name字段名</param> /// <param name="strSqlCon">sql条件（没有为空）</param> /// <returns></returns> 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用符号隔开 /// <summary> /// 根据id获取所有下级Code用符号隔开（含有本身） /// </summary> /// <param name="strId">id</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">code字段名</param> /// <param name="strIdFileName">id字段名</param> /// <param name="strSqlCon">sql条件</param> /// <returns>所有下级Code用符号隔开（含有本身）</returns> 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用符号隔开 /// <summary> /// 根据id获取所有下级id用符号隔开（含有本身） /// </summary> /// <param name="strId">id</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">code字段名</param> /// <param name="strIdFileName">id字段名</param> /// <param name="strSqlCon">sql条件</param> /// <returns>所有下级Code用符号隔开（含有本身）</returns> 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用符号隔开 /// <summary> /// 根据id获取所有下级name用符号隔开（含有本身） /// </summary> /// <param name="strId">id</param> /// <param name="strSplit">分割字符</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">code字段名</param> /// <param name="strIdFileName">id字段名</param> /// <param name="strNameFileName">name字段名</param> /// <param name="strSqlCon">sql条件</param> /// <returns>所有下级Code用符号隔开（含有本身）</returns> 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用符号隔开 /// <summary> /// 根据Code获取所有上级Code用符号隔开，含有本身，未过滤 /// </summary> /// <param name="strCode">code</param> /// <param name="iLevelLen">几位一级</param> /// <returns>所有上级Code用符号隔开</returns> 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用符号隔开 /// <summary> /// 根据Code获取所有上级id用符号隔开，含有本身 /// </summary> /// <param name="strCode">code</param> /// <param name="iLevelLen">几位一级</param> /// <returns>所有上级Code用符号隔开</returns> 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用符号隔开 /// <summary> /// 根据Code获取所有上级name用符号隔开，含有本身 /// </summary> /// <param name="strCode">code</param> /// <param name="strSplit">分隔符</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">code字段名</param> /// <param name="strNameFileName">name字段名</param> /// <param name="strSqlCon">sql条件</param> /// <returns>所有上级name用符号隔开，含有本身</returns> 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用符号隔开 /// <summary> /// 根据id获取所有上级Code用符号隔开，含有本身 /// </summary> /// <param name="strId">id</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">code字段名</param> /// <param name="strIdFileName">id字段名</param> /// <returns>所有上级Code用符号隔开，含有本身</returns> 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用符号隔开 /// <summary> /// 根据id获取所有上级id用符号隔开，含有本身 /// </summary> /// <param name="strId">id</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">code字段名</param> /// <param name="strIdFileName">id字段名</param> /// <param name="strIdFileName">sql条件</param> /// <returns>所有上级Code用符号隔开，含有本身</returns> 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用符号隔开 /// <summary> /// 根据id获取所有上级name用符号隔，含有本身 /// </summary> /// <param name="strId">id</param> /// <param name="strSplit">分割字符串</param> /// <param name="iLevelLen">几位一级</param> /// <param name="strTableName">表名</param> /// <param name="strCodeFileName">code字段名</param> /// <param name="strIdFileName">id字段名</param> /// <param name="strNameFileName">name字段名</param> /// <param name="strSqlCon">sql条件</param> /// <returns>所有上级name用符号隔，含有本身</returns> 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
