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

Исследуя обширные пространства интернета мне пришлось потрудиться над тем как же все таки создать не тяжеловесный код на 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 и выше.

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers. Five Filters recommends: 'You Say What You Like, Because They Like What You Say' - http://www.medialens.org/index.php/alerts/alert-archive/alerts-2013/731-you-say-what-you-like-because-they-like-what-you-say.html