SQL数据层级结构

Question

我已经查看了几个SQL层次结构的教程，但是对我的应用程序没有多大意义。也许我只是没有正确地理解它们。我正在编写一个C# ASP.NET应用程序，并希望从SQL数据创建树形视图层次结构。

这是层次结构的工作原理：

如果ID和Location ID相同，则确定顶级父级。任何该父级的子级都将具有与父级相同的Location ID。该子级的任何子级孙辈都将具有等于Child的ID的Location ID，以此类推。

对于以上示例：

- House -- Room --- Bed

非常感谢您提供的帮助或指向易于跟随的教程。

编辑：到目前为止，我只有获取父级和子级的代码，没有GrandChildren。我似乎无法弄清楚如何递归获取所有节点。

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 表“Locations”的摘录：

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

谢谢。

- Will

---

这些名称很令人困惑。我会将“Location ID”更改为“Parent_ID”。 - FrustratedWithFormsDesigner

一个纯 SQL 的答案可能需要知道你正在使用哪种类型的 SQL。你可以指定你使用的数据库吗？比如 SQL Server 2008？ - Chris Nielsen

很遗憾，我不能更改名称，因为太多其他的东西依赖于它们。我正在使用SQL Server 2008。 - Will

你正在使用HierarchyID数据类型吗？ - goalie7960

Answer 1

您正在寻找使用公共表达式或简称CTE的递归查询。关于SQL Server 2008中此功能的详细介绍可以在MSDN上找到：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中用更简单的语言解释）：

1. 将CTE表达式分成锚定成员和递归成员。
2. 运行锚点，创建第一个结果集。
3. 运行递归部分，并以前一步骤作为输入。
4. 重复步骤3，直到返回空集合。
5. 返回结果集。这是锚点和所有递归步骤的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

注意，“Gym”被排除在外。根据您的示例数据，它的ID与其[位置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)
            });
        }
    }
}

Ta-da！现在root位置节点包含了整个层次结构。顺便说一下，我实际上还没有执行这段代码，所以如果你发现有什么明显的问题，请告诉我。

---

编辑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语句替换为我之前建议的带有ORDER BY子句的SQL语句。请阅读我的上一次编辑，其中解释了“深度”为什么很重要：您确实希望按特定顺序添加节点。在您拥有父节点之前，无法添加子节点。

可选地，我认为您不需要在这里使用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);
                    }
                }
            }
        }
    }
}

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

- Chris Nielsen

---

太好了，这正是我在寻找的。谢谢！不过还有一个问题。我怎么知道哪个子节点属于哪个父节点？为了开发这个数据的树形视图，我需要知道它们之间的关系。 - Will

我已经包含了一些示例C#代码，利用CTE查询构建了一个类似树形结构的C#结构。这是您要寻找的吗？ - Chris Nielsen

这正是我在寻找的。但我在编译时遇到了一些问题。这段代码能在ASP.NET C#项目中使用吗？ - Will

是的，我认为它应该可以工作。我没有执行它，但我已经验证了它的语法正确性。如果您愿意，可以尝试编辑您的问题并提供更多详细信息，我会尽力帮助您。或者，如果您觉得您理解了层次结构的SQL部分，而且您在C#方面遇到了困难，那么您可以考虑将其作为一个单独的问题，并发布一个新的SO帖子。 - Chris Nielsen

这篇文章非常有帮助，非常感谢您的帮助。在编译过程中我只遇到了一个错误：“对象引用未设置为对象实例”，与以下代码行相关：“t1.FindNode(rs.GetString(4)).ChildNodes.Add(node);”。 - Will

Answer 2

我建议你也看一看 SQL Server 2008 中引入的 HierarchyId 数据类型，它为遍历和操作树形结构提供了很多功能。这是一个教程：

在 .NET 应用程序中使用 SQL Server HierarchyId 数据类型进行工作

- Alireza Maddah

Answer 3

抱歉，我只是在浏览StackOverflow。我看到了你的问题，感觉我三年前写了一篇回答你问题的文章。如果有帮助，请让我知道。
http://www.simple-talk.com/dotnet/asp.net/rendering-hierarchical-data-with-the-treeview/

- Ion Freeman

Answer 4

在 SQL 2008 中有一个新功能，那就是 hierarchyid。这个功能让我的生活更加轻松。

对于 hierarchyid 数据类型，还有一些有用的 方法，比如 GetAncestor()、GetRoot() 等等。这些方法可以降低查询的复杂度，使我在处理层次结构时更加得心应手。

- Pongsathon.keng