Implementing multi-level trees in MS SQL Server

Introduction

Many projects now is in need of management of multi-tree level data. There is some ways to does this, but in this article I'll introduce a good to setup and install Multi-tree level Stru Ctures by using a MS SQL Server database. Our result was fully tested in MS SQL Server 2005/2008.

The tree is shown and managed by the value of a column, I call it by "index-dictionary" term. By seeing this value or ordering this value we can understand the tree ' s structure.

My article'll show you a simple-to-work with tree data. This solution can deal with any tree-level you want, I'll use a sample tree level = 3.

When you had a requirement which needs a tree data structure, you can use this solution and it would save you time. After installing this solution you don ' t need-pay attention to learn about CTE queries or other complicated ways to Bui LD a tree. Just Select all rows and order by ' index-dictionary ' column, the tree is built up! Really easy.

This solution uses ordinal, alphabets, and Romans to mark the tree level:

• Upper case letters such as A, type B 0-root level.
• Roman Letters for Level 1
• Numbers for Level 2

You can download the SQL script or full DB restoration in the Zip sample file.

Purpose and Design

Our purpose would is to create a multi-level tree and index the line by using multiple types of sequence numbers, such as O Rdinal numbers (1, 2, 3,.), Alphabets (A, B, C.), or Romans (I, II, III ...). The result may be shown as:

Figure 1

To deploy this solution we'll create a tree-data table in MS SQL Server using this structure:

Figure 2

Tree_IDIs the auto increasing sequence. It is a known as the tree NodeID . Is null or 0 if is the root of the Parent_ID Tree_ID tree. When Tree_ID was a child node, was the the Parent_ID Tree_ID parent node. Seq_Index is a indicator for Child-node sequence-number, a Valid value is only a single-index:a, B, C or 1, 2, 3 ... Full_index Shows the Full-path dictionary to the child node from the root, such as:a.i.1, A.II.1, B.I.2, b.i.3 ...

Using the Code

We'll create a function and a Stored Procedure (SP) and separate them to three-groups. You can find all these functions and the SP in the scripts file or restore the full database.

Group 1:functions

• [count_tree_level]: Function to check level of Tree-node.
• [get_seq_by_level]: Function to calculate the by seq_index tree level.
• [count_tree_full_index]: Function to calculate the full_index by tree_id .
• [get_reverse_ascii]: Function to convert ordinal number to ASCII, example 1-> ' A ', 2 to ' B '. By using the This function, you can convert a ordinal number to upper case characters or lower characters; This can is done by passing the ascii_pattern parameter.
• [convert_integer_to_roma]: function to convert ordinal number to Roman (this function I found on the internet).
• [check_parent]: Function to return value 1 if Node_id was a child, or Grand-child of the selected parent_ID .

Group 2:sp to edit the tree

• [insert_tree_node]: SP to insert a new tree node, also re-calculate directory
• [remove_node]: SP to delete a tree-node, also re-calculate directory
• [move_node_up]: SP to move-up a tree node, also re-calculate directory
• [move_node_down]: SP to move down a tree node, also re-calculate directory

Group 3:viewing the RESULTLT

• [view_tree]: SP to view tree in order by index directory
• [view_human_tree]: SP to view tree in order by index directory, it's well-known by human and same as Figure 1

Code Example

These actions below would demonstrate how to use the code to implement a multi-level tree structure. First of all, it is clear all the data in DATATREETBL. This code would insert three root nodes named "Tree A", "Tree B", and "Tree C".

Go'tree A ',0go'tree B ',0go'tree C ',0go   

From now, after running a code block, we'll check again the tree by running the query:

Asc

The result after running that code:

Remember the node ID, we'll add child nodes to the above node.

First of all, we'll add a first level child data to the root node (the tree_id value was dependent on your real tree_id a Fter running the code block above).

GoEXEC Dbo.insert_tree_node tree a.i ', 73 goexec dbo.insert_tree_node  Span class= "code-string" >tree a.ii ', 73go exec dbo.insert_tree_node  ' Tree B. I ', 74goexec Dbo.insert_tree_node  tree b.ii ', 74 goexec dbo.insert_tree_node  Span class= "code-string" >tree C.I., 75go     

The result is:

Now, we'll add a second to the above tree:

GoEXEC Dbo.insert_tree_node‘Tree A.I.1 ',76GoEXEC Dbo.insert_tree_node‘Tree A.I.2 ',76GoEXEC Dbo.insert_tree_node‘Tree A.I.3 ',76GoEXEC Dbo.insert_tree_node‘Tree A.II.1 ',77GoEXEC Dbo.insert_tree_node tree b.i.1 ', 78 goexec dbo.insert_tree_node  Span class= "code-string" >tree b.i.2 ', 78go exec dbo.insert_tree_node  ' Tree C. I.1 ', 80goexec dbo.insert_tree_ Node  tree c.i.2 ', 80goexec dbo.insert_tree_node  ' tree c.i.3", 80go      

The result is:

Now we'll edit this tree. First of all, we'll move up the tree node 82. Let us run the This code:

82

The result is node id=82 is moved above node id=81. and is full_Index re-counted!

You can move up and move down to any node. This time we'll move down tree_id=74, a root node tree!

74

In result, you can see all nodes in "tree B" are returned after "Tree C", and the Index directory (full_index) is Re-counted also.

Now we'll remove a node from a tree. I'll try to remove the root node "Tree C"-tree_id=75.

75

All Node C and its relationships is removed. "Tree B" is moved up and we re-count seq_Index and Full_index .

And finally, we'll use a Stored procedures to view the tree. Result when running SP [ view_tree ]:

Result when running SP [ view_human_tree ]:

Conclusion

As I mentioned above, we can use the CTE queries to generate a tree. Let us use the This CTE query:

With Tree_cte (tree_id, Tree_name, parent_id, Seq_index, Full_index, Tree_level) as(    0  on childnode.parent_id = Tree_cte. tree_id) bytree_level   

The Tree-result is:

You can see the CTE method can count Tree-level, but it cannot does as this solution can. All row levels is show in a sequence. In addition, CTE cannot help order row data at the same level by itself, you can only use fields to order. But by using this solution you can easily order the node position in a parent ID node.

This solution would give a tree-output visually. You can easily understand, see tree-structure, and Count Tree-level by seeing the output result.

Points of Interest

This solution is more than three levels deep as you can see in the sample picture. If you want seq_index to change the of the tree level, customize it in the [ get_seq_by_level ] function. There is problems with the tree when using Roman characters:i, II, III, IV, V, VI, VII, VIII, IX, X ... Number (9) in Roman = IX would occur before number (5) in Roman = v. You can find a easy to fix this! This solution can is applied for many cases. A multi-level menu for applications or exporting reports ...

Hope This article would help you gain the time when your project needs to be installed as a multi-level tree data.

License

This article, along with any associated source code and files, is licensed under the Code Project Open License (Cpol)

Http://www.codeproject.com/Articles/162368/Implementing-a-Tree-Structure-with-Database

Implementing multi-level trees in MS SQL Server