Ответ 1
Вы ищете рекурсивный запрос, используя общее табличное выражение, или 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 до тех пор, пока не будет возвращен пустой набор.
- Возвращает набор результатов. Это СОЕДИНЕНИЕ ВСЕХ якорей и всех рекурсивных шагов.
В этом конкретном примере попробуйте что-то вроде этого:
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:
Вы спросили о том, как это сделать в структуру данных С#. Существует множество различных способов представления иерархии на С#. Вот один пример, выбранный по своей простоте. Без сомнения, реальный образец кода будет более обширным.
Первый шаг - определить, как выглядит каждый node в иерархии. Помимо свойств свойств для каждой базы данных в node, я включил свойства Parent и Children, плюс методы для Add дочернего элемента и Get дочернего элемента. Метод Get будет выполнять поиск по всей оси потомков node, а не только для собственных детей node.
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;
}
}
Теперь вы хотите заполнить свое дерево. У вас есть проблема: трудно заполнить дерево в неправильном порядке. Прежде чем добавить дочерний элемент node, вам действительно нужна ссылка на родительский node. Если вы имеете, чтобы сделать это не по порядку, вы можете уменьшить проблему, выполнив два прохода (один для создания всех узлов, а затем другой для создания дерева). Однако в этом случае это необязательно.
Если вы берете SQL-запрос, указанный выше, и заказываете столбец depth, вы можете быть математически уверены, что никогда не столкнетесь с дочерним node, прежде чем вы столкнетесь с его родительским node. Поэтому вы можете сделать это за один проход.
Вам все равно понадобится node, чтобы служить "корнем" вашего дерева. Вы решаете, будет ли это "Дом" (из вашего примера), или это вымышленный заполнитель node, который вы создаете именно для этой цели. Я предлагаю позже.
Итак, к коду! Опять же, это оптимизировано для простоты и удобочитаемости. Есть некоторые проблемы с производительностью, которые вы можете захотеть решить в производственном коде (например, нет необходимости постоянно искать "родительский" node). Я избегал этих оптимизаций здесь, потому что они увеличивают сложность.
// 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. Прочтите мои предыдущие изменения, которые объясняют, почему важна "глубина": вы действительно хотите добавить узлы в определенном порядке. Вы не можете добавить дочерний элемент node, пока не будет родительский node.
По желанию, я думаю, что здесь вам не нужны накладные расходы SqlDataAdapter и DataTable. Первоначально предлагаемое решение DataReader было проще, проще в работе и более эффективно с точки зрения ресурсов.
Кроме того, большинство объектов С# SQL реализуют IDisposable, поэтому вы захотите убедиться, что используете их правильно. Если что-то реализует IDisposable, обязательно заверните его в инструкции using (см. Пример предыдущего кода С#).
Исправьте цикл построения дерева
Вы получаете только родительский и дочерний узлы, потому что у вас есть петля для родителей и внутренний цикл для детей. Как вы уже должны знать, вы не получаете внуков, потому что у вас нет кода, который их добавляет.
Вы могли бы добавить внутренний внутренний цикл, чтобы получить внуков, но ясно, что вы просите о помощи, потому что вы поняли, что это приведет только к безумию. Что произойдет, если вы тогда захотите правнуков? Внутренняя внутренняя внутренняя петля? Этот метод не является жизнеспособным.
Вероятно, вы подумали о рекурсии. Это идеальное место для него, и если вы имеете дело с древовидными структурами, это в конечном итоге появится. Теперь, когда вы отредактировали свой вопрос, ясно, что у вашей проблемы мало что может быть с SQL. Ваша настоящая проблема заключается в рекурсии. Кто-то может в конце концов прийти и разработать для этого рекурсивное решение. Это был бы совершенно правильный и, возможно, предпочтительный подход.
Однако мой ответ уже рассмотрел рекурсивную часть - он просто переместил ее в слой SQL. Поэтому я сохраню свой предыдущий код, поскольку я считаю, что это подходящий общий ответ на вопрос. Для вашей конкретной ситуации вам нужно сделать еще несколько изменений.
Сначала вам не понадобится класс LocationNode, который я предложил. Вместо этого вы используете TreeNode, и это будет работать нормально.
Во-вторых, TreeView.FindNode похож на метод LocationNode.Get, который я предложил, за исключением того, что FindNode требует полного пути к node. Чтобы использовать 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);
}
}
}
}
}
}
Пожалуйста, дайте мне знать, если вы найдете дополнительные ошибки!