Non-Recursive Implementation of unlimited classification (Stored Procedure edition) _ Asp.net skills

 

More or less tree directories, product categories, and other level-2 or level-3 menus have been created. For more levels of classification, recursion is generally used. Using Recursion in a program will increase performance overhead more or less.

Previously, I used Asp.net to implement non-recursive unlimited classification directories in the program, but considering the portability is not strong, I changed it into a stored procedure. I will share it with you. So far, no problems have been found during the test. In addition, the Code has not been optimized.

Generally, more operations are performed to read the directory. Therefore, in the following implementation, we only need one SELECT statement to read data. Instead of recursion, the read data is theoretically infinite ~!
========================================================== ====================================
Table Structure:
Table Name: tb_column
Table Structure (all fields are not empty ):
Column_id int primary key (Note: Not identified)
Column_name nvarchar (50) category name
Parent_id int parent category ID (0 by default)
Column_path nvarchar (1000) classification path
Column_depth int classification depth (0 by default)
Column_order int sorting (0 by default)
Column_intro nvarchar (1000) Category Description

========================================================== ==================================
Stored Procedure 1: Create a category

Create procedure sp_column_insert
(
@ Parent_id int,
@ Column_name nvarchar (50 ),
@ Column_intro nvarchar (1000)
)
As
Declare @ err as int
Set @ err = 0

Begin tran
-- Obtain the column ID from the existing record
Declare @ column_id as int
Declare @ column_depth as int
Select @ column_id = max (column_id) from tb_column
If @ column_id is not null
Set @ column_id = @ column_id + 1
Else
Set @ column_id = 1

-- Determine whether it is a top-level column and set its column_path and column_order
Declare @ column_path as nvarchar (1000)
Declare @ column_order as int
If @ parent_id = 0
Begin
Set @ column_path = ltrim (STR (@ column_id ))
Select @ column_order = max (column_order) from tb_column
If @ column_order is not null
Set @ column_order = @ column_order + 1
Else -- if no record is found, this is the first record.
Set @ column_order = 1

-- Depth
Set @ column_depth = 1
End
Else
Begin
-- Obtain the path and depth of the parent node
Select @ column_path = column_path, @ column_depth = column_depth from tb_column where

Column_id = @ parent_id
If @ column_path is null
Begin
Set @ err = 1
Goto theend
End

-- Obtain the maximum number of the same parent node
Select @ column_order = max (column_order) from tb_piccolumn where column_path like

+ @ Column_path + | % or column_id = @ parent_id
If @ column_order is not null -- if the sequence number exists, add 1 to all the sequence numbers after the sequence number.
Begin
-- Update the sequence numbers of all nodes to be inserted.
Update tb_column set column_order = column_order + 1 where column_order

> @ Column_order
-- Add 1 to the maximum serial number of the parent node to create its own serial number.
Set @ column_order = @ column_order + 1
End
Else
Begin
Set @ err = 1
Goto theend
End

-- Add the ID of the parent node to form the path.
Set @ column_path = @ column_path + | + ltrim (STR (@ column_id ))

-- Depth
Set @ column_depth = @ column_depth + 1

End

Insert into tb_column (column_id, column_name, parent_id, column_path, column_depth, column_order, column_intro)

Values (@ column_id, @ column_name, @ parent_id, @ column_path, @ column_depth, @ column_order, @ column_intro)

If @ error <> 0
Begin
Set @ err = 1
Goto theend
End

-- Update the order of the record after the current record
-- Update tb_column set column_order = column_order + 1 where column_order> @ column_order

Theend:
If @ err = 0
Begin
Commit tran
Return @ column_id
End
Else
Begin
Rollback tran
Return 0
End
Go

========================================================== ============================================
Stored Procedure 2: delete a category
Create procedure sp_column_delete
(
@ Column_id int
)
As
Declare @ err as int
Set @ err = 0
Begin tran
-- First, query whether a subnode exists under the node.
Select column_id from tb_column where parent_id = @ column_id
If @ rowcount <> 0
Begin
Set @ err = 1
Goto theend
End

-- Obtain the column_order of the node. Sort other records after deletion.
Declare @ column_order as int
Select @ column_order = column_order from tb_column where column_id = @ column_id
If @ column_order is null
Begin
Set @ err = 2
Goto theend
End

-- Update column_order of other records
Update tb_column set column_order = column_order-1 where column_order> @ column_order
If @ error <> 0
Begin
Set @ err = 3
Goto theend
End

-- Delete operation
Delete from tb_column where column_id = @ column_id
If @ error <> 0
Begin
Set @ err = 4
Goto theend
End

-- Update column_id of other records
-- Update tb_column set column_id = column_id-1 where column_id> @ column_id
-- If @ error <> 0
-- Begin
-- Set @ err = 5
-- Goto theend
-- End

Theend:
If @ err = 0
Begin
Commit tran
Return 0 -- deletion successful
End
Else
Begin
If @ err = 1
Begin
Rollback tran
Return 1 -- subnode exists
End
Else
Begin
Rollback tran
Return 2 -- unknown error
End
End
Go
========================================================== ======================================
Stored Procedure 3: edit a category
Create procedure sp_column_update
(
@ Column_id int,
@ Parent_id int,
@ Column_name nvarchar (50 ),
@ Column_intro nvarchar (1000)
)
As
Declare @ err as int
Set @ err = 0

Begin tran

-- Get the parent_id, column_depth, and column_order before the modification.
Declare @ oparent_id as int
Declare @ ocolumn_depth as int
Declare @ ocolumn_order as int
Declare @ ocolumn_path as nvarchar (1000)

Select @ oparent_id = parent_id, @ ocolumn_depth = column_depth, @ ocolumn_order = column_order, @ ocolumn_path = column_path from tb_column where column_id = @ column_id
If @ oparent_id is null
Begin
Set @ err = 1
Goto theend
End

-- If the parent ID is not changed, modify the topic name and topic description.
If @ oparent_id = @ parent_id
Begin
Update tb_column set column_name = @ column_name, column_intro = @ column_intro where column_id = @ column_id
If @ error <> 0
Set @ err = 2
Goto theend
End

Declare @ ncolumn_path as nvarchar (1000)
Declare @ ncolumn_depth as int
Declare @ ncolumn_order as int

-- Get the number of nodes contained by the current node as the parent node [including itself] Note: If "1" is returned, it indicates a single node.
Declare @ thecount as int
Select @ thecount = count (column_id) from tb_column where column_id = @ column_id or column_path like + @ ocolumn_path + | %
If @ thecount is null
Begin
Set @ err = 3
Goto theend
End

If @ parent_id = 0 -- if it is set to a top-level node, set the node to the last top-level node.
Begin
-- Set print to top-level topic
Set @ ncolumn_path = ltrim (STR (@ column_id ))
Set @ ncolumn_depth = 1

Select @ ncolumn_order = max (column_order) from tb_column
If @ ncolumn_order is null
Begin
Set @ err = 4
Goto theend
End

Set @ ncolumn_order = @ ncolumn_order-@ thecount + 1

-- Update the sequence of records after the change in the tree of three parts 1 Node itself 2 all child nodes 2
-- Print: column_order
Update tb_column set column_order = column_order-@ thecount where (column_order> @ ocolumn_order) and (column_path not like + @ ocolumn_path + | %)
If @ error <> 0
Begin
Set @ err = 7
Goto theend
End

-- Print: parent_id, column_path, column_depth, column_order, column_name, column_intro
Print order: + ltrim (STR (@ ncolumn_order ))
Update tb_column set parent_id = @ parent_id, column_path = @ ncolumn_path, column_depth = @ override, column_order = @ ncolumn_order, column_name = @ column_name, column_intro = @ column_intro where column_id = @ column _
If @ error <> 0
Begin
Set @ err = 5
Goto theend
End

-- Print: column_path, column_depth, column_order
Update tb_column set column_path = Replace (column_path, @ ocolumn_path, @ ncolumn_path), column_depth = column_depth + (@ resolve-@ resolve), column_order = column_order + (@ ncolumn_order-@ ocolumn_order) where column_path like + @ ocolumn_path + | %
If @ error <> 0
Begin
Set @ err = 6
Goto theend
End

End
Else
Begin
-- Obtain information about the future parent node and set the value of the current node.
Select @ ncolumn_depth = column_depth, @ ncolumn_path = column_path from tb_column where column_id = @ parent_id
If @ ncolumn_depth is null or @ ncolumn_path is null
Begin
Set @ err = 8
Goto theend
End
Set @ ncolumn_depth = @ ncolumn_depth + 1
Select @ ncolumn_order = max (column_order) from tb_column where column_id = @ parent_id or column_path like + @ ncolumn_path + | %
If @ ncolumn_order is null
Begin
Set @ err = 9
Goto theend
End

Set @ ncolumn_path = @ ncolumn_path + | + ltrim (STR (@ column_id ))

If @ ncolumn_order = @ ocolumn_order + 1 -- if the new parent node is the closest brother at the top of the original position, the order of all nodes remains unchanged.
Begin
Update tb_column set parent_id = @ parent_id, column_path = @ ncolumn_path, column_depth = @ ncolumn_depth, column_name = @ column_name, column_intro = @ column_intro where column_id = @ column_id
If @ error <> 0
Begin
Set @ err = 10
Goto theend
End
End

Set @ ncolumn_order = @ ncolumn_order + 1

-- Update Part 3. 1. Sequence of records after (or before) changes in the current tree. 1. Node itself. 3. All subnodes.
-- Move up or move down
-- Print: column_order of [or after this topic] [excluding subtopics under this topic]
If @ ncolumn_order <@ ocolumn_order
Begin
Update tb_column set column_order = column_order + @ thecount where column_order <@ ocolumn_order and column_order> = @ ncolumn_order and (column_path not like + @ ocolumn_path + | %) and column_id <> @ column_id
If @ error <> 0
Begin
Set @ err = 12
Goto theend
End
End
Else
Begin
Update tb_column set column_order = column_order-@ thecount where column_order> @ ocolumn_order and column_order <@ ncolumn_order and (column_path not like + @ ocolumn_path + | %) and column_id <> @ column_id
If @ error <> 0
Begin
Set @ err = 13
Goto theend
End
End

-- Print: parent_id, column_path, column_depth, column_order, column_name, column_intro
Print order: + ltrim (STR (@ ncolumn_order ))
If @ ncolumn_order> @ ocolumn_order
Set @ ncolumn_order = @ ncolumn_order-@ thecount
Update tb_column set parent_id = @ parent_id, column_path = @ ncolumn_path, column_depth = @ override, column_order = @ ncolumn_order, column_name = @ column_name, column_intro = @ column_intro where column_id = @ column _
If @ error <> 0
Begin
Set @ err = 10
Goto theend
End

-- Print: column_paht, column_depth, and column_order
Update tb_column set column_path = Replace (column_path, @ ocolumn_path, @ ncolumn_path), column_depth = column_depth + (@ resolve-@ resolve), column_order = column_order + (@ ncolumn_order-@ ocolumn_order) where column_path like + @ ocolumn_path + | %
If @ error <> 0
Begin
Set @ err = 11
Goto theend
End
End

Theend:
If @ err <> 0 -- if an error exists, the error number is returned.
Begin
Rollback tran
Return @ err
End
Else -- returns 0 if no error exists.
Begin
Commit tran
Return 0
End
Go
========================================================== ==========================================
Stored Procedure 4: Display classification (only one SELECT statement)
CATEGORY list:
Create procedure sp_column_list
As
Select column_id, column_name, parent_id, column_path, column_depth,
Column_order, column_intro
From tb_column
Order by column_order
Go