c# - SQL数据层次结构

Question

我看过一些SQL层次结构教程，但是它们对我的应用程序都没有多大意义。也许我只是不正确地理解它们。我正在编写一个C＃ASP.NET应用程序，我想从SQL数据创建树形视图层次结构。

这是层次结构的工作方式：

SQL TABLEID     | Location ID | Name_______| __________  |_____________1331   | 1331        | House1321   | 1331        | Room2141   | 1321        | Bed1251   | 2231        | Gym

If the ID and Location ID are the same, this would determine the top Parent. Any Children of that Parent would have the same Location ID as the Parent. Any Grandchildren of that Child would have a Location ID equal to the ID of the Child, and so on.

For the above example:

- House   -- Room       --- Bed

Any help or direction to easy to follow tutorials would be greatly appreciated.

EDIT:

Code I have so far, but it only gets the Parent and Children, no GrandChildren. I can't seem to figure out how to get it to recursively get all of the nodes.

using System;
using System.Data;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;

namespace TreeViewProject
{
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        PopulateTree(SampleTreeView);

    }



    public void PopulateTree(Control ctl)
    {

        // Data Connection
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AssetWhereConnectionString1"].ConnectionString);
        connection.Open();

        // SQL Commands
        string getLocations = "SELECT ID, LocationID, Name FROM dbo.Locations";
        SqlDataAdapter adapter = new SqlDataAdapter(getLocations, connection);
        DataTable locations = new DataTable();
        // Fill Data Table with SQL Locations Table
        adapter.Fill(locations);
        // Setup a row index
        DataRow[] myRows;
        myRows = locations.Select();

        // Create an instance of the tree
        TreeView t1 = new TreeView();
        // Assign the tree to the control
        t1 = (TreeView)ctl;
        // Clear any exisiting nodes
        t1.Nodes.Clear();

        // BUILD THE TREE!
        for (int p = 0; p < myRows.Length; p++)
        {
            // Get Parent Node
            if ((Guid)myRows[p]["ID"] == (Guid)myRows[p]["LocationID"])
            {
                // Create Parent Node
                TreeNode parentNode = new TreeNode();
                parentNode.Text = (string)myRows[p]["Name"];
                t1.Nodes.Add(parentNode);

                // Get Child Node
                for (int c = 0; c < myRows.Length; c++)
                {
                    if ((Guid)myRows[p]["LocationID"] == (Guid)myRows[c]["LocationID"] 
                        && (Guid)myRows[p]["LocationID"] != (Guid)myRows[c]["ID"] /* Exclude Parent */)
                    {
                        // Create Child Node
                        TreeNode childNode = new TreeNode();
                        childNode.Text = (string)myRows[c]["Name"];
                        parentNode.ChildNodes.Add(childNode);
                    }
                }
            }
        }
        // ALL DONE BUILDING!

        // Close the Data Connection
        connection.Close();
    }

}
}

这是实际SQL表的摘录：位置

ID位置ID名称
____________________________________ ____________________________________ ______________
DEAF3FFF-FD33-4ECF-910B-1B07DF192074 48700BC6-D422-4B26-B123-31A7CB704B97下降F
48700BC6-D422-4B26-B123-31A7CB704B97 7EBDF61C-3425-46DB-A4D5-686E91FD0832 Olway
06B49351-6D18-4595-8228-356253CF45FF 6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0下落E 5
E98BC1F6-4BAE-4022-86A5-43BBEE2BA6CD DEAF3FFF-FD33-4ECF-910B-1B07DF192074下落F 6
F6A2CF99-F708-4C61-8154-4C04A38ADDC6 7EBDF61C-3425-46DB-A4D5-686E91FD0832 Pree
0EC89A67-D74A-4A3B-8E03-4E7AAAFEBE51 6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0跌落E 4
35540B7A-62F9-487F-B65B-4EA5F42AD88A 48700BC6-D422-4B26-B123-31A7CB704B97 Olway发生故障
5000AB9D-EB95-48E3-B5C0-547F5DA06FC6 6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0输出1
53CDD540-19BC-4BC2-8612-5C0663B7FDA5 6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0 Drop E 3
7EBDF61C-3425-46DB-A4D5-686E91FD0821 B46C7305-18B1-4499-9E1C-7B6FDE786CD6测试1
7EBDF61C-3425-46DB-A4D5-686E91FD0832 7EBDF61C-3425-46DB-A4D5-686E91FD0832 HMN


谢谢。

Answer 1

您正在寻找使用公用表表达式（或简称CTE）的递归查询。 SQL Server 2008中对此的详细记录可以为found on MSDN。
通常，它们的结构类似于以下内容：

WITH cte_name ( column_name [,...n] )
AS (
    –- Anchor
    CTE_query_definition

    UNION ALL

    –- Recursive portion
    CTE_query_definition
)
-- Statement using the CTE
SELECT * FROM cte_name

执行此操作后，SQL Server会执行以下操作（从MSDN解释为更简单的语言）：

将CTE表达式分为锚成员和递归成员。
运行锚，创建第一个结果集。
以上一步为输入，运行递归部分。
重复步骤3，直到返回空集。
返回结果集。这是锚点和所有递归步骤的UNION ALL。

对于此特定示例，请尝试以下操作：

With hierarchy (id, [location id], name, depth)
As (
    -- selects the "root" level items.
    Select ID, [LocationID], Name, 1 As depth
    From dbo.Locations
    Where ID = [LocationID]

    Union All

    -- selects the descendant items.
    Select child.id, child.[LocationID], child.name,
        parent.depth + 1 As depth
    From dbo.Locations As child
    Inner Join hierarchy As parent
        On child.[LocationID] = parent.ID
    Where child.ID != parent.[Location ID])
-- invokes the above expression.
Select *
From hierarchy

给定您的示例数据，您应该获得如下内容：

ID     | Location ID | Name  | Depth
_______| __________  |______ | _____
1331   | 1331        | House |     1
1321   | 1331        | Room  |     2
2141   | 1321        | Bed   |     3

注意，排除了“健身房”。根据您的示例数据，它的ID与它的[Location ID]不匹配，因此它不是根级别的项目。它的位置ID 2231没有出现在有效的父ID列表中。

编辑1：
您已经问过要将其放入C＃数据结构中的问题。用C＃表示层次结构的方式有很多很多。这是一个为简单起见而选择的示例。实际的代码示例无疑会更广泛。
第一步是定义层次结构中每个节点的外观。除了包含节点中每个基准的属性外，我还包含了 Parent和 Children属性，以及用于 Add子级和 Get子级的方法。 Get方法将搜索节点的整个后代轴，而不仅仅是节点自己的子代。

public class LocationNode {
    public LocationNode Parent { get; set; }
    public List<LocationNode> Children = new List<LocationNode>();
    
    public int ID { get; set; }
    public int LocationID { get; set; }
    public string Name { get; set; }
    
    public void Add(LocationNode child) {
        child.Parent = this;
        this.Children.Add(child);
    }
    
    public LocationNode Get(int id) {
        LocationNode result;
        foreach (LocationNode child in this.Children) {
            if (child.ID == id) {
                return child;
            }
            result = child.Get(id);
            if (result != null) {
                return result;
            }
        }
        return null;
    }
}

现在，您要填充树。您在这里遇到了问题：以错误的顺序填充树很困难。在添加子节点之前，确实需要引用父节点。如果必须不按顺序进行操作，则可以通过两次通过来缓解问题（一次创建所有节点，然后另一次创建树）。但是，在这种情况下，这是不必要的。
如果采用我上面提供的SQL查询并按 depth列排序，则可以从数学上确定在遇到父节点之前，您永远不会遇到子节点。因此，您可以一次性完成此操作。
您仍然需要一个节点作为树的“根”。您可以决定这将是“ House”（从您的示例中得出），还是仅为此目的而创建的虚构占位符节点。我建议稍后。
所以，到代码！再次，这是优化的简单性和可读性。您可能需要在生产代码中解决一些性能问题（例如，并不需要经常查找“父”节点）。我在这里避免了这些优化，因为它们会增加复杂性。

// Create the root of the tree.
LocationNode root = new LocationNode();

using (SqlCommand cmd = new SqlCommand()) {
    cmd.Connection = conn; // your connection object, not shown here.
    cmd.CommandText = "The above query, ordered by [Depth] ascending";
    cmd.CommandType = CommandType.Text;
    using (SqlDataReader rs = cmd.ExecuteReader()) {
        while (rs.Read()) {
            int id = rs.GetInt32(0); // ID column
            var parent = root.Get(id) ?? root;
            parent.Add(new LocationNode {
                ID = id,
                LocationID = rs.GetInt32(1),
                Name = rs.GetString(2)
            });
        }
    }
}

- root LocationNode现在包含您的整个层次结构。顺便说一句，我实际上尚未执行此代码，所以如果发现任何明显的问题，请告诉我。

编辑2
要修复示例代码，请进行以下更改：
删除此行：

// Create an instance of the tree
TreeView t1 = new TreeView();

该行实际上不是问题，但应将其删除。您在这里的评论不正确；您并没有真正为控件分配树。相反，您将创建一个新的TreeView，将其分配给 t1，然后立即将一个不同的对象分配给 t1。下一行执行后，您创建的TreeView就会丢失。
修正您的SQL陈述式

// SQL Commands
string getLocations = "SELECT ID, LocationID, Name FROM dbo.Locations";

将此SQL语句替换为我之前建议的SQL语句，并带有ORDER BY子句。阅读我以前的编辑，其中解释了“深度”为何重要的原因：您确实确实想按特定顺序添加节点。在拥有父节点之前，您无法添加子节点。
（可选）我认为您在这里不需要SqlDataAdapter和DataTable的开销。我最初建议的DataReader解决方案更简单，更易于使用，并且在资源方面更有效。
而且，大多数C＃SQL对象都实现 IDisposable，因此您将需要确保正确使用它们。如果某些东西实现了 IDisposable，请确保将其包装在 using语句中（请参阅我之前的C＃代码示例）。
修复树构建循环
您只有父级和子级节点，因为您有一个用于父级的循环和一个用于子级的内部循环。正如您已经知道的那样，您没有孙子，因为您没有添加孙子的代码。
您可以添加一个内部-内部循环来获取孙辈，但是显然您正在寻求帮助，因为您已经意识到这样做只会导致疯狂。如果您当时想要曾孙，该怎么办？内部-内部-内部循环？此技术不可行。
您可能在这里想到了递归。这是一个理想的地方，如果您要处理树状结构，那么它最终会出现的。既然您已经编辑了问题，那么很显然，您的问题与SQL几乎没有关系。您真正的问题是递归。最终可能有人会为此设计一个递归解决方案。那将是一个完全有效且可能是更可取的方法。
但是，我的答案已经涵盖了递归部分-它只是将其移至SQL层。因此，我将保留以前的代码，因为我认为它是对该问题的适当通用答案。对于您的特定情况，您需要进行一些其他修改。
首先，您不需要我建议的 LocationNode类。您使用的是 TreeNode，它将正常工作。
其次， TreeView.FindNode与我建议的 LocationNode.Get方法类似，除了 FindNode需要到节点的完整路径。要使用 FindNode，必须修改SQL才能为您提供此信息。
因此，整个 PopulateTree函数应如下所示：

public void PopulateTree(TreeView t1) {

    // Clear any exisiting nodes
    t1.Nodes.Clear();

    using (SqlConnection connection = new SqlConnection()) {
        connection.ConnectionString = "((replace this string))";
        connection.Open();

        string getLocations = @"
            With hierarchy (id, [location id], name, depth, [path])
            As (

                Select ID, [LocationID], Name, 1 As depth,
                    Cast(Null as varChar(max)) As [path]
                From dbo.Locations
                Where ID = [LocationID]

                Union All

                Select child.id, child.[LocationID], child.name,
                    parent.depth + 1 As depth,
                    IsNull(
                        parent.[path] + '/' + Cast(parent.id As varChar(max)),
                        Cast(parent.id As varChar(max))
                    ) As [path]
                From dbo.Locations As child
                Inner Join hierarchy As parent
                    On child.[LocationID] = parent.ID
                Where child.ID != parent.[Location ID])

            Select *
            From hierarchy
            Order By [depth] Asc";

        using (SqlCommand cmd = new SqlCommand(getLocations, connection)) {
            cmd.CommandType = CommandType.Text;
            using (SqlDataReader rs = cmd.ExecuteReader()) {
                while (rs.Read()) {
                    // I guess you actually have GUIDs here, huh?
                    int id = rs.GetInt32(0);
                    int locationID = rs.GetInt32(1);
                    TreeNode node = new TreeNode();
                    node.Text = rs.GetString(2);
                    node.Value = id.ToString();

                    if (id == locationID) {
                        t1.Nodes.Add(node);
                    } else {
                        t1.FindNode(rs.GetString(4)).ChildNodes.Add(node);
                    }
                }
            }
        }
    }
}

如果您发现任何其他错误，请告诉我！