/// <summary>
///获取子节点列表,不包含父节点信息
/// </summary>
/// <param name="pid">父节点ID</param>
/// <param name="isall">true所有子节点信息,false:仅下一级</param>
public DataTable SelByPid(int pid, bool isall = false)
{
DataTable dt = new DataTable();
if (isall)
{
//string sql = "with Tree as(SELECT *,cast(1 as int) as [level] FROM ZL_Node WHERE ParentID=" + pid + " UNION ALL SELECT a.*,b.[level]+1 FROM ZL_Node a JOIN Tree b on a.ParentID=b.NodeID) SELECT *,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID) ChildCount FROM Tree AS A ORDER BY OrderID,NodeID ASC";
//DataTable dt = Sel();
//string nids = "";
//RecursionByPid(dt, pid, ref nids);
//nids = StrHelper.PureIDSForDB(nids);
//dt.DefaultView.RowFilter = "NodeID IN(" + nids + ")";
//return dt.DefaultView.ToTable();
//WITH Tree AS(
//SELECT * FROM ZL_Node WHERE ParentID=0 UNION ALL SELECT A.* FROM ZL_Node A JOIN Tree B on A.ParentID=B.NodeID)
//SELECT * FROM Tree ORDER BY OrderID,NodeID ASC
string sql = "with Tree as(SELECT * FROM ZL_Node WHERE ParentID=" + pid + " UNION ALL SELECT a.* FROM ZL_Node a JOIN Tree b on a.ParentID=b.NodeID) SELECT *,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID AND ZStatus=99) ChildCount FROM Tree AS A ORDER BY OrderID,NodeID ASC";
//获取父节点下子级后,再去除父级
string oracle = "SELECT * FROM(SELECT * FROM ZL_Node START WITH NODEID IN(" + pid + ") CONNECT BY nocycle prior NodeID=ParentID )WHERE NodeID!=" + pid;
dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
}
else
{
string fields = "A.*,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID) ChildCount ";
//string sql = "SELECT " + fields + " FROM " + TbName + " A WHERE ParentID=" + pid+" ORDER BY OrderID,NodeID ASC";
dt = DBCenter.SelWithField(TbName, fields, "ParentID=" + pid, defOrder);
}
dt.DefaultView.RowFilter = "ZStatus IS NULL OR ZStatus NOT IN ('" + (int)ZLEnum.ConStatus.Recycle + "')";
dt = dt.DefaultView.ToTable();
return(dt);
}
public static int SelFirstNodeID(string TbName, string PK, int nodeid, ref string nodeTree)
{
int firstNodeID = 0;
if (nodeid < 1)
{
return(firstNodeID);
}
string sql = "with f as(SELECT * FROM " + TbName + " WHERE " + PK + "=" + nodeid + " UNION ALL SELECT A.* FROM " + TbName + " A, f WHERE a." + PK + "=f.ParentID) SELECT * FROM " + TbName + " WHERE " + PK + " IN(SELECT " + PK + " FROM f)";
string oracle = "SELECT * FROM " + TbName + " Where " + PK + "=" + nodeid;//[need deal]
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
if (dt.Rows.Count < 1)
{
return(firstNodeID);
}
//顺序无法确定,首位可能是第一,也可能是最后???
foreach (DataRow dr in dt.Rows)
{
if (DataConvert.CLng(dr["ParentID"]) == 0)
{
firstNodeID = Convert.ToInt32(dr[PK]);
}
nodeTree += dr[PK] + ",";
}
nodeTree = nodeTree.Trim(',');
return(firstNodeID);
}
public DataTable SelNodeByModel(string ids)
{
SafeSC.CheckIDSEx(ids);
string where = DBCenter.GetSqlByDB("','+ContentModel+','", "','||ContentModel||','");
string[] idarr = ids.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < idarr.Length; i++)
{
where += " LIKE '%," + idarr[i] + ",%' OR";
}
where = where.TrimEnd("OR".ToCharArray());
return(DBCenter.Sel(TbName, where));
}
public static DataTable SelByPid(string TbName, string PK, int pid)
{
string sql = "with Tree as(SELECT * FROM {0} WHERE ParentID=" + pid + " UNION ALL SELECT a.* FROM {0} a JOIN Tree b on a.ParentID=b.{1}) SELECT * FROM Tree AS A ORDER {1} ASC";
//获取父节点下子级后,再去除父级
string oracle = "SELECT * FROM(SELECT * FROM {0} START WITH {1} IN(" + pid + ") CONNECT BY nocycle prior {1}=ParentID )WHERE {1}!=" + pid;
sql = string.Format(sql, TbName, PK);
oracle = string.Format(oracle, TbName, PK);
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
return(dt);
}
/// <summary>
/// 返回父级链,包含起始ID
/// </summary>
/// <param name="tbname">ZL_Node</param>
/// <param name="pk">NodeID</param>
/// <param name="pfield">示例:ParentID</param>
/// <param name="startid">起始的ID值,如UserID的值</param>
/// <param name="ids">返回的层级IDS</param>
/// <returns></returns>
private string SelParentTree(string tbname, string pk, string pfield, int startID)
{
string ids = "";
if (startID < 1)
{
return(ids);
}
string sql = "WITH f AS(SELECT * FROM {ZL_Node} WHERE {NodeID}=" + startID + " UNION ALL SELECT A.* FROM {ZL_Node} A, f WHERE a.{NodeID}=f.{ParentID}) SELECT * FROM {ZL_Node} WHERE {NodeID} IN(SELECT {NodeID} FROM f)";
string oracle = "SELECT * FROM {tbname} START WITH {NodeID} =" + startID + " CONNECT BY PRIOR {ParentID} = {NodeID}";
sql = sql.Replace("{ZL_Node}", tbname).Replace("{NodeID}", pk).Replace("{ParentID}", pfield);
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
foreach (DataRow dr in dt.Rows)
{
ids += dr[pk] + ",";
}
return(ids.Trim(','));
}
/// <summary>
/// 获取指定节点的起始父节点,并生成节点树,不包含0
/// 注意节点不要存在循环(如NodeID=ParentID,或圆形)
/// </summary>
public int SelFirstNodeID(int nodeid, ref string nodeTree)
{
if (nodeid < 1)
{
return(0);
}
string sql = "with f as(SELECT * FROM ZL_Node WHERE NodeID=" + nodeid + " UNION ALL SELECT A.* FROM ZL_Node A, f WHERE a.NodeID=f.ParentID) SELECT * FROM ZL_Node WHERE NodeID IN(SELECT NodeID FROM f)";
//string oracle = "SELECT * FROM ZL_Node START WITH NODEID =" + nodeid + " CONNECT BY PRIOR ParentID = NodeID";
string oracle = "SELECT * FROM ZL_Node Where NodeID=" + nodeid;//[need deal]
DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
if (dt.Rows.Count < 1)
{
return(0);
}
foreach (DataRow dr in dt.Rows)
{
nodeTree += dr["NodeID"] + ",";
}
nodeTree = nodeTree.Trim(',');
return(DataConvert.CLng(dt.Rows[0]["NodeID"]));
}
/// <summary>
/// 为贴子增加标记
/// </summary>
/// <param name="flag">置顶等标记</param>
private void AddPostFlag(string ids, string flag, bool isadd)
{
DBCenter.UpdateSQL(TbName, "PostFlag=''", "PostFlag IS NULL");
SafeSC.CheckIDSEx(ids);
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter("flag", "," + flag + ",")
};
string set = "", where = "";
if (isadd)
{
sp.Add(new SqlParameter("flag2", "%," + flag + ",%"));
set = DBCenter.GetSqlByDB("PostFlag=PostFlag+@flag", "PostFlag=PostFlag||:flag");
where = "ID IN (" + ids + ") And (PostFlag NOT Like @flag2 OR POSTFLAG IS NULL) AND Pid=0";//兼容Oracle,增加is null
}
else
{
set = DBCenter.GetSqlByDB("PostFlag=REPLACE(REPLACE(PostFlag,@flag,','),',,',',')", "PostFlag=REPLACE(REPLACE(PostFlag,@flag,''),',,',',')");
where = "ID IN (" + ids + ")";
}
DBCenter.UpdateSQL(TbName, set, where, sp);
}