递归 删除层级记录

PrimaryKey    UpLevelCompCode   UpLevelCompName   CurrentLevelCompCode    CurrentLevelCompName    CurrentLevel
1                           0                             0                             NULL                                    NULL                        0
2                           0                             0                                 1                                      1                              1
3                           0                             0                                 2                                      2                              1
4                           1                             1                                 3                                      3                              2
5                           1                             1                                 4                                      4                              2
6                           3                             3                                 5                                      5                              3
7                           3                             3                                 6                                      6                              3

//递归 删除层级记录
public static void Delete(string code,int level,string sign)
{
    if(level>0)
    {
    System.Data.DataSet ds = bll.SelectDepartLevelMaintainByFilter(" and "+sign+" = '"+code+"' and CurrentLevel="+level);
    if(ds!=null)
    {
        if(ds.Tables[0]!=null)
        {
            if(ds.Tables[0].Rows.Count>0)
            {
                foreach(System.Data.DataRow dr in ds.Tables[0].Rows)
                {
                    string _code = dr["CurrentLevelCompCode"].ToString().Trim();
                    int _level = Convert.ToInt32(dr["CurrentLevel"].ToString().Trim());
                    long _pk = Convert.ToInt64(dr["PrimaryKey"].ToString().Trim());
                    Delete(_code,_level+1,"UpLevelCompCode");
                    bll.DeleteDepartLevelMaintain(_pk);
                }
            }
        }
    }
   }
}
DepartLevelMaintainBLL bll = new DepartLevelMaintainBLL ();
System.Data.DataRow dr = bll.SelectDepartLevelMaintainByFilter(" and PrimaryKey = "+2).Tables[0].Rows[0];
string _code = dr["CurrentLevelCompCode"].ToString().Trim();
int _level = Convert.ToInt32(dr["CurrentLevel"].ToString().Trim());
TestMethod.Delete(_code,_level,"CurrentLevelCompCode");

 

实际上可以将CurrentLevel可以设计成有层级的值，这样删除的时候根据条件删除就可以了。

如：

CurrentLevel
00
001
002
00101
00102
0010101
0010102

字段设计成这样删除时就不用使用层级查找了。直接SUBSTRING(.....)=.....就可以了。