delphi7 treeview + 数据库 实现动态节点维护
首先说下树节点对应的表的基本结构,必需要有的字段(节点编号,父节点编号,节点名称),其他字段根据你开发的需要添加
从添加节点开始,一开始就取出表中最大节点编号,每次添加节点的时候,该节点编号增加1;
添加节点的时候我们有2个步骤,首先是给treeview树种建立节点;其次是给数据库中添加相应的节点记录;
添加节点树的时候,需要注意的是要把该节点的节点编号加入到该节点的data属性中.
在数据库中加入节点记录的时候,节点编号和父节点编号都不能少,如果是第0层节点,那么该节点的父节点编号是\'0\'(这种情况你自己定义,我在这里设为\'0\')
显示节点我写了一个通用函数来实现的
删除节点由于要删除该节点及其所有后代节点在表中的记录,这个在sql server中好像没有什么好的语句,我写了一个存储过程来实现的
unit Unit1; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, ComCtrls, ShellAPI, DB, ADODB; type TForm1 = class(TForm) tv1: TTreeView; btn_addtj: TButton; btn_addzjd: TButton; btn_del: TButton; edt_jdmc: TEdit; Label1: TLabel; btn_xs: TButton; con1: TADOConnection; qry1: TADOQuery; qry2: TADOQuery; procedure btn_addtjClick(Sender: TObject); procedure btn_addzjdClick(Sender: TObject); procedure btn_delClick(Sender: TObject); procedure btn_xsClick(Sender: TObject); procedure FormCreate(Sender: TObject); private { Private declarations } public { Public declarations } end; var Form1: TForm1; maxdwbh: integer; procedure addChildNodes(tv: TTreeView; fnode: TTreeNode; qry: TADOQuery); overload; procedure addChildNodes(tv: TTreeView; fnode: TTreeNode; qry: TADOQuery; tab, jdbh, jdmc, fjdbh, nilfjdbh: string); overload; implementation {$R *.dfm} { 功能:在一个treeview中显示数据库中父子节点,并且把节点的编号存入自己的data属性中 参数:tv 展示树 fnode 表示tv中的节点,第一次调用通常这个节点取nil qry 一个ADOQuery控件,用来做查询 tab 我们的父子节点存放的数据库表名称 jdbh tab中节点编号代表的字段名称 jdmc tab中节点名称代表的字段名称 fjdbh tab中父节点编号代表的字段名称 nilfjdbh 树中第0级节点的父节点字段给分配的值 注意:我们的树在添加(子)节点的时候也要把它的节点的编号存入到该节点的data属性中 } procedure addChildNodes(tv: TTreeView; fnode: TTreeNode; qry: TADOQuery; tab, jdbh, jdmc, fjdbh, nilfjdbh: string); var sql, fdwbh: string; node, newnode: TTreeNode; pinteger: ^string; begin //判断表中有没有节点,如果连第0级节点都不存在,我们也就不需要显示树了 sql := \'select * from tab where fjdbh=\'\'\' + nilfjdbh + \'\'\'\'; qry.Close; qry.SQL.Clear; qry.SQL.Add(sql); qry.Open; if not qry.IsEmpty then begin if fnode = nil then fdwbh := nilfjdbh else fdwbh := string(fnode.data^); //取出节点fnode的全部子节点的记录 sql := \'select \' + jdbh + \',\' + jdmc + \',\' + fjdbh + \' from \' + tab + \' where \' + fjdbh + \'=\'\'\' + fdwbh + \'\'\' order by \' + jdbh; qry.Close; qry.SQL.Clear; qry.SQL.Add(sql); qry.Open; //树中加入子节点 if not qry.IsEmpty then //有子节点 begin //添加一个节点的所有子节点 qry.First; while not qry.Eof do begin newnode := tv.Items.AddChild(fnode, qry.fieldbyname(jdmc).AsString); New(pinteger); pinteger^ := qry.fieldbyname(jdbh).AsString; newnode.Data := pinteger; qry.Next; end; //定位下一个该操作的节点 if fnode = nil then node := tv.Items.GetFirstNode else node := fnode.getFirstChild; //递归调用对下一个子节点进行同样的操作 addChildNodes(tv, node, qry, tab, jdbh, jdmc, fjdbh, nilfjdbh); end else //没有子节点的情况,取它的下一个兄弟节点递归调用 begin node := fnode.GetNext; if node <> nil then addChildNodes(tv, node, qry, tab, jdbh, jdmc, fjdbh, nilfjdbh); end; end; end; procedure addChildNodes(tv: TTreeView; fnode: TTreeNode; qry: TADOQuery); var fdwbh: Integer; sql: string; node, newnode: TTreeNode; Pdata: ^Integer; begin if fnode = nil then fdwbh := 0 else fdwbh := Integer(fnode.data^); //取出节点fnode的全部子节点的记录 sql := \'select dwbh,dwmc,fdwbh from zzjgbmb where fdwbh=\' + inttostr(fdwbh) + \' order by dwbh\'; qry.Close; qry.SQL.Clear; qry.SQL.Add(sql); qry.Open; //树中加入子节点 if not qry.IsEmpty then //有子节点 begin //添加一个节点的所有子节点 qry.First; while not qry.Eof do begin newnode := tv.Items.AddChild(fnode, qry.fieldbyname(\'dwmc\').AsString); New(Pdata); Pdata^ := qry.fieldbyname(\'dwbh\').AsInteger; newnode.Data := Pdata; qry.Next; end; //定位下一个该操作的节点 if fnode = nil then node := tv.Items.GetFirstNode else node := fnode.getFirstChild; //递归调用对下一个节点进行同样的操作 addChildNodes(tv, node, qry); end else //没有子节点的情况 begin node := fnode.GetNext; if node <> nil then addChildNodes(tv, node, qry); end; end; procedure TForm1.btn_addtjClick(Sender: TObject); var jdmc, sql: string; fdwbh: integer; node, newnode: TTreeNode; pinteger: ^integer; begin try node := tv1.Selected; jdmc := edt_jdmc.text; newnode := tv1.Items.add(node, jdmc); maxdwbh := maxdwbh + 1; New(pinteger); pinteger^ := maxdwbh; newnode.data := pinteger; if node = nil then fdwbh := 0 else if node.Level = 0 then fdwbh := 0 else fdwbh := Integer(node.Parent.data^); sql := \'insert into zzjgbmb(dwbh,fdwbh,dwmc) values(\' + inttostr(maxdwbh) + \',\' + inttostr(fdwbh) + \',\'\'\' + jdmc + \'\'\')\'; qry1.Close; qry1.SQL.Clear; qry1.SQL.Add(sql); qry1.ExecSQL; ShowMessage(\'单位"\' + jdmc + \'"添加成功!\'); except on e: Exception do ShowMessage(e.Message); end; end; procedure TForm1.btn_addzjdClick(Sender: TObject); var jdmc, sql: string; fdwbh: integer; node, newnode: TTreeNode; pinteger: ^integer; begin try jdmc := edt_jdmc.text; node := tv1.Selected; newnode := tv1.Items.AddChild(node, jdmc); maxdwbh := maxdwbh + 1; New(pinteger); pinteger^ := maxdwbh; newnode.Data := pinteger; if node = nil then Exit else fdwbh := Integer(node.data^); sql := \'insert into zzjgbmb(dwbh,fdwbh,dwmc) values(\' + inttostr(maxdwbh) + \',\' + inttostr(fdwbh) + \',\'\'\' + jdmc + \'\'\')\'; qry1.Close; qry1.SQL.Clear; qry1.SQL.Add(sql); qry1.ExecSQL; ShowMessage(\'单位"\' + jdmc + \'"添加成功!\'); except on e: Exception do ShowMessage(e.Message); end; end; procedure TForm1.btn_delClick(Sender: TObject); var node: TTreeNode; dwbh: integer; sql: string; begin try node := tv1.Selected; node.Delete; dwbh := Integer(node.data^); sql := \'exec p_zzjgbmb_delnode \' + inttostr(dwbh); qry1.Close; qry1.SQL.Clear; qry1.SQL.Add(sql); qry1.ExecSQL; except on e: Exception do ShowMessage(e.Message); end; end; procedure TForm1.btn_xsClick(Sender: TObject); begin try tv1.Items.Clear; addChildNodes(tv1, nil, qry1, \'zzjgbmb\', \'dwbh\', \'dwmc\', \'fdwbh\', \'0\'); except on e: Exception do ShowMessage(e.Message); end; end; procedure TForm1.FormCreate(Sender: TObject); var sql: string; begin try maxdwbh := 0; sql := \'select max(dwbh) maxdwbh from zzjgbmb\'; qry1.Close; qry1.SQL.Clear; qry1.SQL.Add(sql); qry1.Open; if not qry1.isempty then begin qry1.First; maxdwbh := qry1.fieldbyname(\'maxdwbh\').AsInteger; end; except on e: Exception do showmessage(e.message); end; end; end.
删除节点及其后代节点的存储过程
create procedure [dbo].[p_zzjgbmb_delnode](@dwbh int) as begin declare @tmp1 table (dwbh int) declare @tmp2 table (dwbh int) declare @tmp3 table (dwbh int) insert into @tmp1 select dwbh from zzjgbmb where fdwbh=@dwbh insert into @tmp3 select dwbh from @tmp1 while exists(select dwbh from zzjgbmb where fdwbh in (select dwbh from @tmp1)) begin delete @tmp2 insert into @tmp2 select dwbh from zzjgbmb where fdwbh in (select dwbh from @tmp1) delete @tmp1 insert into @tmp1 select dwbh from @tmp2 insert into @tmp3 select dwbh from @tmp1 end delete from zzjgbmb where dwbh in (select dwbh from @tmp3); delete from zzjgbmb where dwbh = @dwbh end
简单的界面
请发表评论