Иерархические данные. В поиске оптимального решения

Исследуя обширные пространства интернета мне пришлось потрудиться над тем как же все таки создать не тяжеловесный код на asp.net c# для обработки так называемых «свинных ушей», которые содержат классическую модель родитель-потомок в базе данных. Сразу оговорюсь база в SQL Server 2008, который уже имеет возможность работы с иерархическими данными в ОТВ(CTE — английский вариант аббревиатуры).

В моей системе строится модель бизнес-процессов, которая должна удовлетворять стандарту SADT и таблица выглядит как приведено ниже на рисунке 1.

Рисунок 1. Модель

Здесь BPNumber — число которое характеризует порядок бизнес-процесса на своем уровне иерархии. К примеру сначала идет подсистема УУ(«Учебное управление») -1, «Кафедра» — 2, а затем «Деканат» — 3(рис. 2).

К примеру, результат должен выглядеть приблизительно так:

Рисунок 2. Результат

Рассмотрев примеры реализации, описанные другими авторами, а так же справки с msdn автор пришла к выводу, что вытаскивать все одним списком в ОТВ и обрабатывать потом средствами asp.net лишняя загрузка сервера на построение запроса с рекурсией, да и обойти ее выстроив xml для загрузки в дерево все равно надо будет писать многочисленный код. Не оптимально подумала я.
Решив, что средствами t-sql не коротко, продолжила поиск уже в других направлениях. И нашла вот эту статью работа с иерархическими данными в asp.net mvc. Может действительно кому-то поможет?

А вот решение найденное здесь Display Hierarchical Data with TreeView in ASP.NET 2.0 поразило простотой и взяв его за основу я решила поставленную выше задачу для отображения такого как надо списка. Для преобразования значения NULL в -1 следует изменить запрос следующим образом:

SqlCommand dbCommand = new SqlCommand("SELECT  [BusinessProcessID], [BPName], [ProjectID], case when [BusinessProcessIDTOP] IS NULL  then -1 else [BusinessProcessIDTOP] end as [BusinessProcessIDTOP] FROM [BusinessProcess] WHERE ([ProjectID] = " + HiddenField1.Value.ToString() + ") order by BPNumber", myConn); 

Листинг 1. Поиск верхнего узла
Ну а весь листинг выглядит вот таким образом

using System.Data.SqlClient;   public partial class bptree : System.Web.UI.Page {     DataTable dtTree = new DataTable();     protected void Page_Load(object sender, EventArgs e)     {                 String strConnect = SqlDataSource1.ConnectionString;         SqlConnection myConn = new SqlConnection(strConnect);         myConn.Open();         SqlCommand dbCommand = new SqlCommand("SELECT  [BusinessProcessID], [BPName], [ProjectID], case when [BusinessProcessIDTOP] IS NULL  then -1 else [BusinessProcessIDTOP] end as [BusinessProcessIDTOP] FROM [BusinessProcess] WHERE ([ProjectID] = " + HiddenField1.Value.ToString() + ") order by BPNumber", myConn);         SqlDataAdapter da = new SqlDataAdapter(dbCommand);         da.Fill(dtTree);         da.Dispose();         dbCommand.Dispose();         myConn.Dispose();         if (!IsPostBack)         AddNodes(-1, TreeView1.Nodes);             }     void AddNodes(int id, TreeNodeCollection tn)     {         foreach (DataRow dr in dtTree.Select("BusinessProcessIDTOP = " + id))         {             TreeNode sub = new TreeNode(dr["BPName"].ToString(), dr["BusinessProcessID"].ToString());             tn.Add(sub);             AddNodes(Convert.ToInt32(sub.Value), sub.ChildNodes);         }     }  } 

Листинг 2. Решение задачи в 2 методах

Ну а что же касается ОТВ. То для описанных ниже задач думаю они подходят лучше:

• Поиск заданного уровня узла
• Вычисления пути(к примеру с файлами)
• Нахождения узлов, имеющих необходимых предков
• Ну и массовые вычисления, которые определены во множестве

К примеру приведу код t-sql с ОТВ:

/****** Сценарий для ОТВ  ******/ with cte_bp([BusinessProcessID]       ,[BPName],[BusinessProcessIDTOP], level ,paths)       as (SELECT  [BusinessProcessID]       ,[BPName]       ,[BusinessProcessIDTOP], 0 as level, CAST([BPName]as nvarchar(max))+'/'          FROM [cmk].[dbo].[BusinessProcess]   where [ProjectID]=1 and BusinessProcessIDTOP is null    UNION ALL SELECT  [BusinessProcess].[BusinessProcessID]       ,[BusinessProcess].[BPName]       ,[BusinessProcess].[BusinessProcessIDTOP], level+1, d.paths +cast([BusinessProcess].[BPName] as nvarchar(max))+'/'          FROM [cmk].[dbo].[BusinessProcess]   Inner join cte_bp as d on d.BusinessProcessID=BusinessProcess.BusinessProcessIDTOP  ) -- Statement using the CTE SELECT *, SPACE(level)+BPName as ch FROM cte_bp order by paths,BusinessProcessID 

Листинг 3. Работа с ОТВ

Вот возвращенный результат:

Рисунок 3. Результат запроса

Думаю приведенный пример поможет тем, кто как и автор этого топика ищет тривиальное и быстрое решение для отображения иерархических данных, хранящихся на SQL Server 2008 и выше.