使用存储过程找出所有的子节点(用于删除节点)
create PROCEDURE UserClassDeleteAllNodeUsedProcedure
@ClassID nchar(10)
AS
declare @id int
set @id=@ClassID;
with RootNodeCTE(id,ParentID)
as
(
Select ClassID,ParentID from Tb_UserClass
where ParentID in (@id)
Union all
Select Tb_UserClass.ClassID,Tb_UserClass.ParentID
From RootNodeCTE
inner join Tb_UserClass on RootNodeCTE.id=Tb_UserClass.ParentID
)
delete from Tb_UserClass
Where ClassID In
(
Select id from RootNodeCTE
union all
Select @classid
)
递归源码:
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
namespace Comm
{
public class TreeView
{
QueryLibrary.QuerySql query; //执行sql命令
SqlCommand cmd;
public TreeView()
{
query = new QueryLibrary.QuerySql();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
}
/// <summary>
/// 节点初始化
/// </summary>
/// <param name="TNode"></param>
public void UserClassGetNodeInit(TreeNode TNode)
{
DataSet ds = new DataSet();
cmd.CommandText = "UserClassGet";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ParentID", SqlDbType.NChar, 10).Value = TNode.Tag;
try
{
ds=query.GetDataSet(cmd);
}
catch{}
foreach (DataRow dr in ds.Tables[0].Rows)
{
TreeNode TN_Parent = new TreeNode();
TreeNode TN_child = new TreeNode();
TN_Parent = TNode;
TN_child.Tag = dr["ClassID"].ToString();
TN_child.Text = dr["ClassName"].ToString();
TNode.Nodes.Add(TN_child);
UserClassGetNodeInit(TN_child);
}
}
/// <summary>
/// 查找所选节点
/// </summary>
/// <param name="TNode"></param>
/// <returns></returns>
public DataSet UserClassGet(TreeNode TNode)
{
DataSet ds = new DataSet();
cmd.CommandText = "UserClassGet";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ParentID", SqlDbType.NChar, 10).Value = TNode.Tag;
try
{
ds = query.GetDataSet(cmd);
}
catch { }
return ds;
}
/// <summary>
/// 节点增加
/// </summary>
/// <param name="ClassID"></param>
/// <param name="ClassName"></param>
/// <param name="ParentID"></param>
/// <returns></returns>
public bool UserClassADD(string ClassID,string ClassName,string ParentID)
{
cmd.CommandText = "UserClassADD";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ClassID", SqlDbType.NChar, 10).Value = ClassID.Trim();
cmd.Parameters.Add("@ClassName", SqlDbType.NChar, 10).Value = ClassName.Trim();
cmd.Parameters.Add("@ParentID", SqlDbType.NChar, 10).Value = ParentID.Trim();
try
{
int i = query.IntExecuteNonQuery(cmd);
if (i > 0)
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
public int UserClassGetMaxClassID()
{
cmd.CommandText = "UserClassGetMaxClassID";
cmd.Parameters.Clear();
try
{
int i = Convert.ToInt32(query.GetScalar(cmd));
return i;
}
catch
{
return -1;
}
}
/// <summary>
/// 使用存储过程删除子节点
/// </summary>
/// <param name="ClassID"></param>
/// <returns></returns>
public bool UserClassDeleteAllNodeUsedProcedure(string ClassID)
{
cmd.CommandText = "UserClassDeleteAllNodeUsedProcedure";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ClassID", SqlDbType.NChar, 10).Value = ClassID.Trim();
int i = query.IntExecuteNonQuery(cmd);
if (i > 0)
{
return true;
}
else
{
return false;
}
}
#region 递归删除子节点
/// <summary>
/// 递归删除子节点
/// </summary>
/// <param name="TNode"></param>
/// <returns></returns>
public bool UserClassDeleteNode(TreeNode TNode)
{
DelNode(TNode);
DataSet ds = new DataSet();
cmd.CommandText = "UserClassGet";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ParentID", SqlDbType.NChar, 10).Value = TNode.Tag;
try
{
ds = query.GetDataSet(cmd);
}
catch { }
foreach (DataRow dr in ds.Tables[0].Rows)
{
TreeNode TN_Parent = new TreeNode();
TreeNode TN_child = new TreeNode();
TN_Parent = TNode;
TN_child.Tag = dr["ClassID"].ToString();
TN_child.Text = dr["ClassName"].ToString();
TNode.Nodes.Remove(TN_child);
UserClassDeleteNode(TN_child);
}
return true;
}
void DelNode(TreeNode TNode)
{
DataSet ds = new DataSet();
cmd.CommandText = "UserClassDeleteNode";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ClassID", SqlDbType.NChar, 10).Value = TNode.Tag;
query.IntExecuteNonQuery(cmd);
}
#endregion
/// <summary>
/// 递归查找节点
/// </summary>
/// <param name="current"></param>
/// <param name="NodeText"></param>
/// <returns></returns>
public TreeNode NodeFind(TreeNode current, string NodeText)
{
if (current == null)
{
return null;
}
if (current.Text.Trim() == NodeText.Trim())
{
return current;
}
TreeNode tn=null;
foreach (TreeNode TNode in current.Nodes)
{
tn=NodeFind(TNode,NodeText);
if (tn != null)
{
break;
}
}
return tn;
}
/// <summary>
/// 节点更新
/// </summary>
/// <param name="ClassID"></param>
/// <param name="ClassName"></param>
/// <returns></returns>
public bool UserClassUpadate(string ClassID,string ClassName)
{
cmd.CommandText = "UserClassUpadate";
cmd.Parameters.Clear();
cmd.Parameters.Add("@ClassID", SqlDbType.NChar, 10).Value = ClassID.Trim();
cmd.Parameters.Add("@ClassName", SqlDbType.NVarChar, 50).Value = ClassName.Trim();
int i = query.IntExecuteNonQuery(cmd);
if (i > 0)
{
return true;
}
else
{
return false;
}
}
}
}
增加节点:
private void btnAdd_Click(object sender, EventArgs e)
{
TreeNode tn_curent =treeView1.SelectedNode; //获取当前选择的节点
string nodeTag = logic.UserClassGetMaxClass().ToString();
if (txtNode.Text != String.Empty)
{
if (tn_curent !=null)
{
if (logic.UserClassADD(nodeTag,txtNode.Text.Trim(), tn_curent.Tag.ToString()))
{
TreeNode tn_child = new TreeNode();
tn_child.Tag = nodeTag;
tn_child.Text = txtNode.Text.Trim();
tn_curent.Nodes.Add(tn_child);
tn_curent.ExpandAll();
};
}
else
{
logic.UserClassADD(nodeTag,txtNode.Text.Trim(), "0");
}
}
}
删除节点:
private void btnDelete_Click(object sender, EventArgs e)
{
TreeNode tn_curent = treeView1.SelectedNode;
if (tn_curent != null)
{
//if (logic.UserClassDeleteAllNodeUsedProcedure(tn_curent.Tag.ToString())) //使用数据库存储过程删除
//{
// treeView1.Nodes.Remove(tn_curent);
//};
if(logic.UserClassDeleteNode(tn_curent)) //使用递归删除
{
treeView1.Nodes.Remove(tn_curent);
}
}
else
{
MessageBox.Show("请选择节点!");
}
}
节点初始化:
private void btnNodeInit_Click(object sender, EventArgs e)
{
TreeNode tnode = new TreeNode();
tnode.Tag = 0;
tnode.Text = "主节点(树)";
logic.UClassNodeInit(tnode);
treeView1.Nodes.Clear();
treeView1.Nodes.Add(tnode);
treeView1.ExpandAll();