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 := qrUserFieldByName('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.Items[i].Data) = id then
16 begin
17 Result := treeview.Items[i];
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：注意fo
with cte as
(
select *,1 flevel,cast([Chld_litm] as nvarchar(4000)) fo from @t where [Parn_litm]='001000333'
union all
select b.*,a.flevel+1,fo+'\'+b.[Chld_litm] from cte a
join @t b on a.[Chld_litm]=b.[Parn_litm]
)
select * from cte
order by fo
delphi代码：
s:='';
while not q.eof do
begin
s:=s+stringofchar(' ',q.fieldbyname('flevel').asinteger-1)+q.fieldbyname('Chld_litm').asstring+$d;
q.next;
end;
然后让treeview.loadfromstream(stringstream{s});就是完整的树了
2、全部可能相关的记录加载到本地内存记录集，程序反复循环扫描内存记录集生成完整的子树

****************************
在DELPHI中用TreeView控件从数据库中动态装载信息
分类： Delphi2007-04-27 17:14 1469人阅读 评论(0) 收藏 举报
delphi数据库querystringformsinterface
1.PInfo表结构
ID VARCHAR(50)
FullName VARCHAR(50)
ParentID VARCHAR(50)

2.Unit文件
unit Info;

interface

uses
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+ChildNodeInfo^.FullName),ChildNodeInfo); //添加孩子结点，并关联孩子结点信息

CreateChildTree(ChildNode); //进行递归

Next;
end;

Close;
end;
end;

procedure TfmInfo.btnShowInfoClick(Sender: TObject)
var
BootNode:TTreeNode; //根结点
BootNodeInfo:PNodeInfo; //根结点信息
begin
with ADOQuery1 do
begin
SQL.Clear;
SQL.Add('SELECT ID,FullName FROM PInfo WHERE ParentID IS NULL'); //获取根结点信息
Open;

New(BootNodeInfo);
BootNodeInfo^.ID:=FieldByName('ID').AsString;
BootNodeInfo^.FullName:=FieldByName('FullName').AsString;

TreeView1.Items.Clear;
BootNode:=TreeView1.Items.AddChildObject(nil,(BootNodeInfo^.ID+BootNodeInfo^.FullName),BootNodeInfo); //添加根结点，并关联根结点信息

Close;
end;

CreateChildTree(BootNode); //创建子树
TreeView1.FullExpand; //展开所有树结点
end;

end.
**********************************************************************************************************************