C# 分类树操作

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

知识共享许可协议
《C# 分类树操作》常伟华 创作。
采用 知识共享 署名-相同方式共享 3.0 中国大陆 许可协议进行许可。
相邻依据:发表时间
  • 多说评论
  • 签名
  • 新浪微博
  • 默认评论
  • Tab Header 5

0 条评论 / 点击此处发表评论

Tab Content 5

开发技术


开发平台和工具

sitemap     181.56ms