Вы ищете рекурсивный запрос с использованием общего табличного выражения или CTE для краткости. Подробное описание этого в SQL Server 2008 можно найти в 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
Обратите внимание, что тренажерный зал исключен. Судя по вашим демонстрационным данным, его идентификатор не соответствует его [идентификатору местоположения], поэтому он не будет элементом корневого уровня. Его идентификатор местоположения, 2231, не отображается в списке допустимых родительских идентификаторов.
Редактировать 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);
}
}
}
}
}
}
Пожалуйста, дайте мне знать, если вы обнаружите какие-либо дополнительные ошибки!
person
Chris Nielsen
schedule
17.05.2011
Location IDнаParent_ID. - person FrustratedWithFormsDesigner schedule 18.05.2011