Treeview绑定SQL.doc

SQL: string; vDRec, vGRec: integer; vDNode, vGNode, vUNode: TTreeNode; vRDept: PrDepartment; vRGroup: PrGroup; vRUser: PrUser;begin SQL := SELECT D.RecordID DRecID,D.DepartmentID,D.DName,D.DDescription, + D.DepartmentPopedom,G.RecordID GRecID,G.GroupID,G.GName,G.GDescription, + G.GroupPopedom,U.RecordID URecID,U.UserID,U.OName,U.OPassword, + U.UserPopedom,U.CreateTime,U.ModifyTime FROM ODEPARTMENT D + LEFT JOIN OGROUP G ON G.DepartmentID = D.DepartmentID + LEFT JOIN OUSER U ON D.DepartmentID = U.DepartmentID and U.GroupID = G.GroupID + ORDER BY D.RecordID,G.RecordID,U.RecordID; qrUser.SQL.Text := SQL; qrUser.Open; vDRec := 0; vGRec := 0; vGNode := nil; vDNode := nil; while not qrUser.Eof do begin if vDRec qrUser.FieldByName(DRecID).AsInteger then begin /增加部门节点 vRDept := New(PrDepartment); vRDept.RecID := qrUser.FieldByName(DRecID).AsInteger; vRDept.DeptID := qrUser.FieldByName(DepartmentID).AsString; vRDept.Name := qrUser.FieldByName(DName).AsString; vRDept.Desc := qrUser.FieldByName(DDescription).AsString; vRDept.Popedom := Trim(qrUser.FieldByName(DepartmentPopedom).AsString); vRDept.Popedom := TCommFun.DencryptStr(vRDept.Popedom, vRDept.Name); vDNode := tvUser.Items.AddChildObject(nil, vRDept.Name, vRDept); vDNode.SelectedIndex := 0; vDNode.ImageIndex := 0; vDRec := vRDept.RecID; vGRec := 0; end; if vGRec qrUser.FieldByName(GRecID).AsInteger then begin /增加班组节点 vRGroup := New(PrGroup); vRGroup.RecID := qrUser.FieldByName(GRecID).AsInteger; vRGroup.GroupID := qrUser.FieldByName(GroupID).AsString; vRGroup.DeptID := qrUser.FieldByName(DepartmentID).AsString; vRGroup.Name := qrUser.FieldByName(GName).AsString; vRGroup.Desc := qrUser.FieldByName(GDescription).AsString; vRGroup.Popedom := Trim(qrUser.FieldByName(GroupPopedom).AsString); vRGroup.Popedom := TCommFun.DencryptStr(vRGroup.Popedom, vRGroup.Name); vGNode := tvUser.Items.AddChildObject(vDNode, vRGroup.Name, vRGroup); vGNode.SelectedIndex := 1; vGNode.ImageIndex := 1; vGRec := vRGroup.RecID; end; /增加用户节点 vRUser := New(PrUser); vRUser.RecID := qrUser.FieldByName(URecID).AsInteger; vRUser.UserID := qrUser.FieldByName(UserID).AsString; vRUser.DeptID := qrUser.FieldByName(DepartmentID).AsString; vRUser.GroupID := qrUser.FieldByName(GroupID).AsString; vRUser.Name := qrUser.FieldByName(OName).AsString; vRUser.Pwd := TCommFun.DencryptStr(qrUser.FieldByName(OPassword).AsString, vRUser.Name); vRUser.Popedom := Trim(qrUser.FieldByName(UserPopedom).AsString); vRUser.Popedom := TCommFun.DencryptStr(vRUser.Popedom, vRuser.Name); vRUser.CreateTime := qrUser.FieldByName(CreateTime).AsString; vRUser.ModifiedTime := qrUser.FieldByName(ModifyTime).AsString; vUNode := tvUser.Items.AddChildObject(vGNode, vRUser.Name, vRUser); vUNode.SelectedIndex := 2; vUNode.ImageIndex := 2; qrUser.Next; end;* 根据数据库结构生成TreeView分类： Delphi2010-09-02 09:04 1774人阅读 评论(8) 收藏 举报数据库integerfunction现在大多数的方法是采用递归，但这种操作会频繁的去select数据库，降低了执行效率，稍微改动一些，效果会好不少，这里介绍一种方法，即一次性select出所有数据，然后按照ParentID排序，逐条添加到TreeView，添加前，先遍历TreeView，查找他的上一级节点，遍历Treeview可比select数据快啊，下面看代码： 数据表结构，最顶级parentid值我用的是-1，大家也可以用其他值，但要保证最顶级parentid值最小，层次越深，parentid越大1 procedure TUIOperate.FillTree(treeview: TTreeView); 2 var 3 findq: TADOQuery; 4 node: TTreeNode; 5 6 /这个方法是根据记录的id字段值，查找TreeView上的父节点 7 function FindParentNode(id:Integer):TTreeNode; 8 var 9 i:Integer; 10 begin 11 Result := nil; 12 for i := 0 to treeview.Items.Count - 1 do 13 begin 14 /比较Node的Data值和记录的id值 15 if Integer(treeview.Itemsi.Data) = id then 16 begin 17 Result := treeview.Itemsi; 18 Break; 19 end; 20 end; 21 end; 22 23 begin 24 findq := TADOQuery.Create(nil); 25 findq.Connection := controler.DataConnect.Connection; /这里的Connection指向你的数据连接 26 try 27 treeview.Items.BeginUpdate; 28 treeview.Items.Clear; 29 /选出所有记录，并按parentid排序，这样可以保证先添加顶级节点，在下级记录添加时，总能找到父节点 30 findq.SQL.Text := SELECT * FROM Catalog order by parentid,id; 31 findq.Open; 32 while not findq.Eof do 33 begin 34 /如果是最顶级，直接添加到Treeview 35 if findq.FieldByName(parentid).AsInteger = -1 then 36 /将id值，保存在Node的Data中，以便查找用 37 treeview.Items.AddObject(nil,findq.FieldByName(CatalogName).AsString,Pointer(findq.FieldByName(id).AsInteger) 38 else 39 begin 40 /查找上级节点 41 node := FindParentNode(findq.FieldByName(parentid).AsInteger); 42 if node nil then 43 treeview.Items.AddChildObject(node,findq.FieldByName(CatalogName).AsString,Pointer(findq.FieldByName(id).AsInteger); 44 end; 45 findq.Next; 46 end; 47 finally 48 findq.Free; 49 treeview.Items.EndUpdate; 50 end; 51 end; 对于id，parentid结构的记录集，2种做法：1、sql就使用cte（sql2005开始支持）先递归得到完整的子树，典型sql：注意fowith cte as(select *,1 flevel,cast(Chld_litm as nvarchar(4000) fo from t where Parn_litm=001000333union allselect b.*,a.flevel+1,fo+b.Chld_litm from cte a join t b on a.Chld_litm=b.Parn_litm)select * from cteorder by fodelphi代码：s:=;while not q.eof dobegins:=s+stringofchar( ,q.fieldbyname(flevel).asinteger-1)+q.fieldbyname(Chld_litm).asstring+$d;q.next;end;然后让treeview.loadfromstream(stringstreams);就是完整的树了2、全部可能相关的记录加载到本地内存记录集，程序反复循环扫描内存记录集生成完整的子树* 在DELPHI中用TreeView控件从数据库中动态装载信息分类： Delphi2007-04-27 17:14 1469人阅读 评论(0) 收藏 举报delphi数据库querystringformsinterface1.PInfo表结构ID VARCHAR(50)FullName VARCHAR(50)ParentID VARCHAR(50)2.Unit文件unit Info;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, ComCtrls, DB, ADODB;type PNodeInfo=TNodeInfo; TNodeInfo=record ID:string; FullName:string; end; TfmInfo = class(TForm) TreeView1: TTreeView; btnShowInfo: TButton; ADOQuery1: TADOQuery; ADOConnection1: TADOConnection; procedure CreateChildTree(ParentNode: TTreeNode); procedure btnShowInfoClick(Sender: TObject); private Private declarations public Public declarations end;var fmInfo: TfmInfo;implementation$R *.dfm/创建子树procedure TfmInfo.CreateChildTree(ParentNode: TTreeNode);var Query:TADOQuery; ChildNode:TTreeNode; /孩子结点 ChildNodeInfo:PNodeInfo; /孩子结点信息begin Query:=TADOQuery.Create(nil); with Query do begin Connection:=ADOConnection1; SQL.Add(SELECT ID,FullName FROM PInfo WHERE ParentID = +PNodeInfo(ParentNode.Data).ID+); /获取孩子结点信息 Open; while not Eof do begin New(ChildNodeInfo); ChildNodeInfo.ID:=FieldByName(ID).AsString; ChildNodeInfo.FullName:=FieldByName(FullName).AsString; ChildNode:=TreeView1.Items.AddChildObject(ParentNode,(ChildNodeInfo.ID