从Delphi中的sql server表动态创建弹出菜单树 [英] dynamically create popup menu tree from sql server table in Delphi

问题描述

我有一个这样的表:

id     parent_id     name
1          1         Root
2          1         Car
3          1         Plane
4          2         BMW
5          4         CLK

如何在Delphi中动态创建包含所有子项的弹出菜单?

How can I dynamically create popup menu with all subitems in Delphi?

它应该是这样的:

推荐答案

假设根元素的父ID为NULL，则可以发出请求

Assuming root element has NULL as Parent_ID you can issue the request

 Select ID, Parent_ID, Name from all_my_menus 
   order by Parent_ID nulls first, ID 
   where Menu_ID = :MenuIDParameter

1   <NULL>    Root
8   <NULL>    another root
2        1    Car
4        1    Plane
3        2    BMW
5        4    CLK

您还将缓存在内存中创建的菜单项:var MI_by_id: TDictionary<integer, TMenuItem>;

You would also cache in-memory created menu items: var MI_by_id: TDictionary<integer, TMenuItem>;

遍历结果看起来像

var MI: TMenuItem;
    MI_by_id: TDictionary<integer, TMenuItem>;
begin 
  MI_by_id := TDictionary<integer, TMenuItem>.Create;
  try
    While not Query.EOF do begin
        MI := TMenuItem.Create(Self);
        MI.Caption := Query.Fields[2].AsString;
        MI.Tag := Query.Fields[0].AsInteger; // ID, would be helpful for OnClick event
        MI.OnClick := ...some click handler

        if Query.Fields[1].IsNull {no parent}
           then MainMenu.Items.Add(MI)
           else MI_by_id.Items[Query.Fields[1].AsInteger].Add(MI);

        MI_by_id.Add(MI.Tag, MI); //save shortcut to potential parent for future searching
        Query.Next;
    end;
  finally 
    MI_by_id.Free;
  end;
end;

实际上，由于我们在查询中对Parent_ID进行了排序，因此给定父级的所有子级都形成一个连续列表，因此最好在填充最后一个子级之后(即在parent_ID获得新值之后)从词典中删除填充的父级.并以其他方式将先前找到的父级缓存在另一个局部变量中(而不是再次搜索字典). 但是，以人为目标的菜单的合理大小应该不值这个数目.但是您必须了解这种方法最有可能随着O(n * n)的扩展而扩展，因此随着表的增长，其松散速度会非常快.

Actually, since we made sort upon Parent_ID on the query, all the children for given parent make single continuous list, so could be better to remove populated parents from the dictionary after we populated last child (i.e. after parent_ID got new value) and caching previously found parent otherwise in another local variable (instead of making yet another search through the dictionary). However reasonable size for human-targeted menu should be much less to worth this. But you have to understand this approach most probably scales as O(n*n) thus would start loose speed very fast as the table grows.

• http://docwiki.embarcadero.com/Libraries/XE3/zh-CN/Vcl.Menus.TMenuItem.Add
• http://docwiki.embarcadero.com/CodeExamples/XE2/en/Generics.Collections.TDictionary_(Delphi )

• http://docwiki.embarcadero.com/Libraries/XE3/en/Vcl.Menus.TMenuItem.Add
• http://docwiki.embarcadero.com/CodeExamples/XE2/en/Generics.Collections.TDictionary_(Delphi)

注意:这还要求每个非根元素ID> ParentID(将CHECK CONSTRAINT放在表上)

Note: this also requires that for every non-root element ID > ParentID (put CHECK CONSTRAINT on the table)

1   <NULL>    Root
8   <NULL>    another root
7        1    Plane
3        4    BMW
4        7    CLK
5        8    Car

这将导致BMW与其父级CLK创建之前绑定在一起进行创建. 可以通过以下几种方法来克服违反这种条件的情况:

This would lead to BMW tied to create before its parent CLK created. Violation for that conditions can be overcome by few means:

• 递归负载:select <items> where Parent_id is null，然后对每个添加的菜单项执行select <items> where Parent_id = :current_memuitem_id，依此类推.就像VirtualTreeView可以工作一样
• 要求SQL Server对树进行排序和展平-这通常称为自递归SQL选择，并且与服务器有关.
• 引入另一个集合变量-不带父项的菜单项.将每个新项目添加到菜单后，应搜索该集合中是否有待提取的子项并将其移入新创建的父项中.

• recursive load: select <items> where Parent_id is null, then for each of the added menu items do select <items> where Parent_id = :current_memuitem_id and so on that. This is like VirtualTreeView would work
• ask SQL server to sort and flatten the tree - this is usually called self-recursive SQL selection and is server-dependant.
• introduce one more collection variable - menu items w/o parent. After each new item added to the menu this collection should be searched if there are pending children to extract from it and move into the newly created parent.

这篇关于从Delphi中的sql server表动态创建弹出菜单树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！