Hierarchical data. In search of the optimal solution

Exploring the vast spaces of the Internet, I had to work hard on how to still create a non-heavy asp.net c # code to handle the so-called “pig ears”, which contain the classical parent-child model in the database. Immediately make a reservation in SQL Server 2008, which already has the ability to work with hierarchical data in the OTV (CTE is the English version of the abbreviation).

In my system, a business process model is built, which must meet the SADT standard and the table looks as shown below in Figure 1.


Figure 1. Model

Here BPNumber is a number that characterizes the order of a business process at its level of hierarchy. For example, the subsystem of the SU (“Educational Management”) -1, the “Department” - 2, and then the “Dean's Office” - 3 comes first (fig. 2).
')
For example, the result should look something like this:

Figure 2. Result

Having considered the examples of implementation described by other authors, as well as the help from msdn, the author came to the conclusion that dragging everything out with one list to the Public Television and processing asp.net later on the server to build the request with recursion, and bypassing it by building xml for downloading In the tree, you still need to write numerous code. I did not think optimally.
Having decided that t-sql is not short, I continued the search in other directions. And I found this article working with hierarchical data in asp.net mvc . Can really help someone?

But the solution found here Display Hierarchical Data with TreeView in ASP.NET 2.0 was struck with simplicity and taking it as a basis, I solved the above task to display such a list as necessary. To convert a NULL value to -1, change the query as follows:

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

Listing 1. Search for the top node
Well, the whole listing looks like this

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

Listing 2. Solving a problem in 2 methods

But what about the OTV. So for the tasks described below, I think they are better suited:

• Search for a specified node level
• Path calculations (for example with files)
• Finding nodes that have the necessary ancestors
• Well, mass calculations, which are defined in the set

For example, here’s the t-sql code with the OTB:

 /******    ******/ 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 

Listing 3. Working with OTV

Here is the result returned:


Figure 3. Query result

I think this example will help those who, like the author of this topic, are looking for a trivial and fast solution for displaying hierarchical data stored on SQL Server 2008 and higher.