您使用的是公用表前pression，或CTE短期寻找一个递归查询。在SQL Server 2008的详细写了这个可以在MSDN 发现。

You are looking for a recursive query using a common table expression, or CTE for short. A detailed write-up for this in SQL Server 2008 can be found on MSDN.

在一般情况下，它们具有类似于以下的结构：

In general, they have a structure similar to the following:

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简单的语言）：

When this executes, SQL Server will do something similar to the following (paraphrased into simpler language from the MSDN):

1. 拆分CTE前pression到锚和递归成员。


2. 运行锚，创造了第一个结果集。


3. 运行递归部分，与现有的步骤作为输入。


4. 重复步骤3，直到空集被返回。


5. 返回结果集。这是一个UNION ALL锚和所有递归步骤。

有关这个具体的例子，尝试这样的事情：

For this specific example, try something like this:

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

鉴于你的数据。例如，你应该得到的东西是这样的：

Given your example data, you should get something like this:

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

请注意，健身房被排除在外。根据您的样本数据，它的ID不匹配的[位置编号]，所以它不会是一个根级别的项目。它的位置ID，2231，不会出现在有效的父ID列表。

Note that "Gym" is excluded. Based on your sample data, it's ID does not match its [Location ID], so it would not be a root-level item. It's location ID, 2231, does not appear in the list of valid parent IDs.

您已经询问进入一个C＃数据结构这一点。有很多很多不同的方式来重新present在C＃中的层次结构。这里是一个例子，选择了它的简单性。一个真正的code样品无疑会更加广泛。

You've asked about getting this into a C# data structure. There are many, many different ways to represent a hierarchy in C#. Here is one example, chosen for its simplicity. A real code sample would no doubt be more extensive.

的第一步骤是定义什么层次结构中的每个节点的模样。除了包含在节点中的每个数据的属性，我已经包括父和孩子属性，再加上方法添加儿童，并为获取的孩子。在获取方法将搜索节点的全部后代轴，而不仅仅是节点的自己的孩子。

The first step is to define what each node in the hierarchy looks like. Besides containing properties for each datum in the node, I've included Parent and Children properties, plus methods to Add a child and to Get a child. The Get method will search the node's entire descendant axis, not just the node's own children.

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;
    }
}

现在你要填充你的树。这里有一个问题：它很难以错误的顺序来填充树。在添加一个子节点，你真正需要的父节点的引用。如果你的包含以做出来的顺序，可以通过两个通道（一个创建所有的节点，然后又创造了树）缓解这个问题。然而，在这种情况下，即unnecessay

Now you'll want to populate your tree. You have a problem here: it's difficult to populate a tree in the wrong order. Before you add a child node, you really need a reference to the parent node. If you have to do it out of order, you can mitigate the problem by making two passes (one to create all the nodes, then another to create the tree). However, in this case, that is unnecessay.

如果你把我上面和秩序由深度列提供的SQL查询，您可以在数学上肯定的是，你永远不会遇到一个子节点遇到其父前节点。因此，您可以一次做到这一点。

If you take the SQL query I provided above and order by the depth column, you can be mathematically certain that you will never encounter a child node before you encounter its parent node. Therefore, you can do this in one pass.

您仍需要一个节点充当你的树的根。您有权决定是否这将是豪斯医生（从你的例子），或者是否是您刚才为此创建一个虚构的占位符节点。我建议以后的。

You will still need a node to serve as the "root" of your tree. You get to decide whether this will be "House" (from your example), or whether it is a fictional placeholder node that you create just for this purpose. I suggest the later.

所以，到了code！再次，这是为了简化和可读性优化。有迹象表明，你可能想解决生产code一些性能问题（例如，它是不是真的有必要不断查找父节点）。因为它们增加的复杂性，我在这里避免了这些优化。

So, to the code! Again, this is optimized for simplicity and readability. There are some performance issues that you may want to address in production code (for instance, it is not really necessary to constantly look up the "parent" node). I've avoided these optimizations here because they increase complexity.

// 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)
            });
        }
    }
}

当当！在根 LocationNode现在包含您的整个层次。顺便说一句，我还没有实际执行该code，所以请让，如果你发现任何明显的问题，我知道了。

Ta-da! The root LocationNode now contains your entire hierarchy. By the way, I haven't actually executed this code, so please let me know if you spot any glaring issues.

要解决您的样本code，进行这些更改：

To fix your sample code, make these changes:

删除这一行：

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

此线是不实际的问题，但它应被删除。您的意见在这里是不准确的;你是不是真的分配树控制。相反，你要创建一个新的TreeView，将其分配给 T1 ，然后立即指派一个不同的对象，以 T1 。您创建的TreeView的是，一旦丢失下一行执行。

This line is not actually an issue, but it should be removed. Your comments here are inaccurate; you are not really assigning a tree to the control. Instead, you are creating a new TreeView, assigning it to t1, then immediately assigning a different object to t1. The TreeView that you create is lost as soon as the next line executes.

解决您的SQL语句

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

替换我前面所说，用一个ORDER BY子句SQL语句这条SQL语句。阅读我的previous编辑解释了为什么深度是很重要的：你真的想在一个特定的顺序添加节点。直到你有父节点，你不能添加一个子节点。

Replace this SQL statement with the SQL statement that I suggested earlier, with an ORDER BY clause. Read my previous edit that explains why the "depth" is important: you really do want to add the nodes in a particular order. You cannot add a child node until you have the parent node.

可选的，我想你不需要的SqlDataAdapter和DataTable这里的开销。我原来提出的解决方案的DataReader更简单，更易于使用，更加effecient在资源方面。

Optionally, I think you don't need the overhead of an SqlDataAdapter and DataTable here. The DataReader solution I originally suggested is simpler, easier to work with, and more effecient in terms of resources.

此外，大多数C＃SQL对象实施的IDisposable ，所以你要确保你正确使用它们。如果事情实现了的IDisposable ，请确保您使用包装在 语句（见我之前，C＃code样品）

Also, most C# SQL objects implement IDisposable, so you will want to make sure you are using them correctly. If something implements IDisposable, be sure you wrap it in using statements (see my prior C# code sample).

解决您的树构建循环

你只得到了家长和孩子的节点，因为你有父母一个循环，并为孩子们的内部循环。正如你一定已经知道了，你没有得到的孙子，因为你没有code，增加了他们。

You're only getting the parent and child nodes because you have a loop for the parents and an inner loop for the children. As you must already know, you aren't getting the grandchildren because you have no code that adds them.

您可以添加一个内内循环，以获得天伦之乐，但显然你寻求帮助，因为你已经意识到，这样做只会导致疯狂。会发生什么，如果你再想要的曾孙？一个内内内循环？这种技术是不可行的。

You could add an inner-inner loop to get the grandchildren, but clearly you're asking for help because you've realized that doing so will only lead to madness. What would happen if you then wanted the great-grandchildren? An inner-inner-inner loop? This technique is not viable.

您可能已经想到递归这里。这是它的一个完美的地方，如果你正在处理的树状结构，它要来了也说不定。现在，你已经修改了你的问题，很明显的您的问题不大，如果有的话，使用SQL 做的。你真正的问题是递归。有人可能最终一起去，并制定该递归的解决方案。这将是一个完全有效的，并可能preferable方法。

You have probably thought of recursion here. This is a perfect place for it, and if you are dealing with tree-like structures, it's going to come up eventually. Now that you've edited your question, it's clear that your problem has little, if anything, to do with SQL. Your real problem is with recursion. Somebody may eventually come along and devise a recursive solution for this. That would be a perfectly valid, and possibly preferable approach.

不过，我的答案已经覆盖递归部分 - 它简直把它移到到SQL层。因此，我会继续我的previous code左右，因为我觉得这是一个适当的通用问题的答案。为了您的具体情况，你需要做一些更多的修改。

However, my answer has already covered the recursive part--it has simply moved it into the SQL layer. Therefore, I'll keep my previous code around, as I feel it is a suitable generic answer to the question. For your specific situation, you'll need to make a few more modifications.

首先，你不需要我建议 LocationNode 类。您正在使用树节点来代替，这将正常工作。

First, you won't need the LocationNode class that I suggested. You are using TreeNode instead, and that will work fine.

其次， TreeView.FindNode 类似于我建议在 LocationNode.Get 的方法，不同的是 FindNode 要求对节点的完整路径。要使用 FindNode ，则必须修改SQL给你这个信息。

Secondly, the TreeView.FindNode is similar to the LocationNode.Get method that I suggested, except that FindNode requires the complete path to the node. To use FindNode, you must modify the SQL to give you this information.

因此​​，你的整个 PopulateTree 函数应该是这样的：

Therefore, your entire PopulateTree function should look like this:

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);
                    }
                }
            }
        }
    }
}

请让我们，如果你发现任何其他错误，我知道！

Please let me know if you find any additional errors!