Rapid Generation of database table TreeView tree

Generate a TreeView tree structure based on the data table content. The common practice is to start from the top level, and then generate one by one recursive query traversal. This method is easy to implement and easy to think of, but it is inefficient because of Database Retrieval (SQL statements need to be interpreted and executed, and database files are operated) it is time-consuming, especially when there are many layers and nodes in the tree. Here, we will introduce the method of exchanging space for time. We can retrieve all the data in a database only once, and then generate a TreeView tree structure once. Use an SQL statement to sort the returned records by the parent node ID and node ID. This ensures that the parent node of the node record to be added has been added to the TreeView tree, the rest is how to find the parent node in the TreeView tree. Here I use a sorted TStringList. By using the fast performance of binary search in the sorting list, I can quickly find the parent node of the node to be added, insert it to the correct position of the TreeView tree.

The source code is as follows (assume that the data table Name is FTree and the field has ID, ParentID, Name ):
Procedure MakeTree (Query: TQuery; TreeView: TTreeView );
Var
List: TStringList;
Node: TTreeNode;
Index: Integer;
Begin
TreeView. Items. BeginUpdate;
Try
TreeView. Items. Clear;

List: = TStringList. Create;
Try
List. Sorted: = True;

While not Query. Eof do
Begin
If Query. FieldByName ('parentid'). AsInteger = 0 then {ParentID = 0, top-layer node}
Node: = TreeView. Items. AddChild (nil, Query. FieldByName ('name'). AsString)
Else
Begin
Index: = List. IndexOf (Query. FieldByName ('parentid'). AsString );
Node: = TreeView. Items. AddChild (TTreeNode (List. Objects [Index]),
Query. FieldByName ('name'). AsString );
End;
List. AddObject (Query. FieldByName ('id'). AsString, Node );
Query. Next;
End;
Finally
List. Free;
End;
Finally
TreeView. Items. EndUpdate;
End;
End;

Procedure TForm1.Button1Click (Sender: TObject );
Var
T: DWORD;
Begin
T: = GetTickCount;
Query1. SQL. Text: = 'select * FROM FTree ORDER BY ParentID, id ';
Query1.Open;
MakeTree (Query1, TreeView1 );
Label1.Caption: = Format ('maketree time: % d Ms', [GetTickCount-T]);
End;