C # infinite classification, recursively regenerate the ordered tree data source

The following table structure is available:

Create table OA_GoodType (ID int not null, -- ID field, u can also be said to be a child ID Name varchar (50) null, -- Name Pid int not null, -- parent class ID, if it is a top-level category when it is equal to ID (or a top-level category when it is set to 0) Path varchar (100) not null, -- Path (parent class set, all contain names starting with '0, ', and the last digit is the value of its own id.) orderId int null, -- Sort constraint PK_OA_GOODTYPE primary key (ID ))

The Path fields mentioned above all start with '0, '. The advantage is that you can directly use the path like', 3, 'when selecting the subclass under a class (ID 3 ,'
Insert the following data to indicate the structure relationship:

Insert into OA_GoodType ([Id], [Name], [Pid], [Path], [OrderId]) VALUES (1, 'server', 1, '0, 1 ', 4) insert into OA_GoodType ([Id], [Name], [Pid], [Path], [OrderId]) VALUES (2, 'Flower being', 1, '0, 1, 2, 2) insert into OA_GoodType ([Id], [Name], [Pid], [Path], [OrderId]) VALUES (3, 'military quilt ', 1, '0, 1, 3 ', 1) insert into OA_GoodType ([Id], [Name], [Pid], [Path], [OrderId]) VALUES (4, 'Military quilt 1.2 meters', 2, '0, 1, 2, 4 ', 1) insert into OA_GoodType ([Id], [Name], [Pid], [Path], [OrderId]) VALUES (37, 'rescue tool class', 37, '0, 37', 2) insert into OA_GoodType ([Id], [Name], [Pid], [Path], [OrderId]) VALUES (43, 'cereals and oils food', 43, '0, 43 ', 6)

In general, it is very simple to select a tree structure for the infinite classification of this structure: Only one SQL statement is enough:
Select * from OA_GoodType order by path asc
However, such statements cannot be sorted by OrderID.
Therefore, if sorting is required, the table must be recursive,
The following method generates a tree-like able for data in order:

/// <Summary> /// dt generates a tree data source from the data source // IdField ID field column name // ParentField parent ID column name // Pid parent ID value // /OrderField sorting field // </summary> public DataTable GetTreeList (DataTable dt, string IdField, string ParentField, int Pid, string OrderField = "") {DataTable newDT = dt. clone (); // Clone the dt structure, including all dt architectures and constraints without data; DataRow [] rows; /** if the parent-level pid is equal to the current ID, which indicates the top-level category, use the following statement */if (Pid = 0) // to select the top-level category {rows = dt. select (ParentField + "=" + IdField, OrderField); // query matching records from dt (Select all categories );} else // select the subclass {rows = dt. select (ParentField + "<>" + IdField + "and" + ParentField + "=" + Pid, OrderField ); // query the records that meet the condition from dt (select a subclass of the big-class pid);}/** if the parent ID is 0, it indicates the top-level category, use the following statement * rows = dt. select (ParentField + "=" + Pid, OrderField); */if (rows. length> 0) {foreach (DataRow row in rows) // Add the query result to dt; {newDT. rows. add (row. itemArray); int innerpid = int. parse (row ["id"]. toString (); DataRow [] Childows = dt. select (ParentField + "<>" + IdField + "and" + ParentField + "=" + innerpid, OrderField); // Select the subclass if (Childows. length> 0) // If a subclass exists, perform recursion on the subclass {DataTable innerDT = GetTreeList (dt, IdField, ParentField, innerpid, OrderField ); // obtain the subclass ableableforeach (DataRow innerrow in innerDT. rows) // Add the sub-class able to the total data source {newDT. rows. add (innerrow. itemArray) ;}}} dt. dispose (); // it is unclear whether this statement is required. return newDT ;}

The usage is as follows:

DataTable dt = comm_table.GetList ("select * from OA_GoodType "). tables [0]; // reorganize the tree data source. Therefore, the SQL statement here does not need to sort subCate. dataSource = comm_table.GetTreeList (dt, "id", "Pid", 0, "OrderID"); subCate. dataBind (); // subCate is the Repeater control

Attach some SQL operations for infinitely classified tables

1. Select the parent class path (including name and id) to which a class belongs. For this table, this method is generally used to list the details of an item under a class, by the way, a series of categories of the item are listed.

Create proc getpathinfo @ ID varchar (120), -- select the ID or path of the record of the path name @ idfield varchar (30), -- store the field name of the ID @ namefield varchar (30 ), -- storage Name field name @ pathfield varchar (30), -- storage path field name @ table varchar (30), -- Name of the table to be operated @ split varchar (10) = '→', -- Separator Used by the connection path @ ispath bit = 0 -- if the path is passed in, set it to 1 as declare @ SQL nvarchar (300) declare @ path varchar (50) -- declare @ pathname varchar (100) -- store the names of each parent class in a loop (if some names are too long, you can add this field as appropriate) Declare @ allpath varchar (500) -- stores the final string formed by each parent class name (if some names are too long, you can increase this field as appropriate) -- declare @ index int set @ allpath = ''if (@ ispath = 1) -- set @ Path = @ ID else begin set @ SQL = 'select @ Path = '+ @ pathfield + 'from' + @ table + 'where' + @ idfield + '=' + @ ID exec sp_executesql @ SQL, n' @ path as varchar (50) output', @ path output end -- print (@ path) declare @ namecursor cursor set @ SQL = n' select' + @ namefield + 'from' + @ table + 'where' + @ idfield + 'in ('+ @ path + ') order by '+ @ pathfield + 'asc' -- cursor query statement -- set @ SQL = n' set @ namecursor = cursor forward_only for '+ @ SQL + N' open @ namecursor 'exec sp_executesql @ SQL, n' @ namecursor cursor output ', @ namecursor output fetch next from @ namecursor into @ pathname while @ fetch_status = 0 begin set @ allpath = @ allpath + @ split + @ pathname -- connect the path with a connector, since @ allpath is empty at the beginning, the first separator fetch next from @ namecursor into @ pathname end should be truncated when returning the path; -- if the first separator is truncated for the name, the SQL Server String subscript starts with 1. For IDS, All IDS contain 0, so select substring (@ allpath, len (@ split) + 1, Len (@ allpath) as pathname, substring (@ path, 3, Len (@ path) as pathid close @ namecursor deallocate @ namecursor

Use the exec getPathInfo 20, 'id', 'name', 'path', and 'oa _ goodtype' statements to test the returned results of the preceding statements.

2. Select All subclass IDS under a class. This method is of great use. For example, there are multiple classes under a category, each category contains items (for the article system, it can be said that it is an article ),
Generally, when you click the parent class, you need to list all the articles under the category, including its subclass or subclass;

Create proc getChildInfo @ Id varchar (100), -- select the id (or path) of the record of the path name @ IdField varchar (30 ), -- storage ID field name @ NameField varchar (30), -- storage Name field name @ PathField varchar (30), -- storage path field name @ Table varchar (30 ), -- Name of the table to be operated @ Split varchar (10) = ',', -- connect all sub-classes using the separator @ isPath bit = 0 -- if the passed path is set to 1 as DECLARE @ SQL nvarchar (300) DECLARE @ ChildID varchar (20) -- DECLARE @ AllChild varchar (120) SET @ AllChild = ''-- cursor query statement if (@ isPath = 1) -- SET @ SQL = n' select' + @ IdField + N' from' + @ Table + N' where '+ @ PathField + N' =' ''+ @ Id + N' or '+ @ PathField + N' like ''' + @ Id + N ', % ''' else SET @ SQL = n' select' + @ IdField + N' from' + @ Table + N' where' + @ PathField + N' like'' %, '+ @ Id + N' or' + @ PathField + N' like ''%, '+ @ Id + N ', % ''' DECLARE @ IDCursor cursor -- set @ SQL = n' SET @ IDCursor = CURSOR FORWARD_ONLY FOR '+ @ SQL + N' OPEN @ IDCursor' EXEC sp_executesql @ SQL, n' @ IDCursor CURSOR output ', @ IDCursor OUTPUT fetch NEXT from @ IDCursor into @ ChildID while @ fetch_status = 0 begin SET @ AllChild = @ AllChild + @ Split + @ ChildID -- connect the path with a connector, since @ AllPath is empty at the beginning, the first separator fetch NEXT from @ IDCursor into @ ChildID end should be truncated when returning the path; -- For the first separator truncated by the name, the SQL Server String subscript starts with 1. For the ID, All IDS contain 0, so select substring (@ AllChild, len (@ Split) + 1, len (@ AllChild) as PathName close @ IDCursor deallocate @ IDCursor

Use the exec getPathInfo 1, 'id', 'name', 'path', and 'oa _ goodtype' statements to test the returned results of the preceding statements.

End !!!!!!!!!

Appendix: Here are some operations for querying, adding, and modifying unlimited categories:

/* Query the direct subclass of A Class (assuming id is 3) */Select * from OA_GoodType where Pid = 3/* query a class (assuming id is 3) all sub-classes under */Select * from OA_GoodType where pid = 3 or path like '%, 3, %'

When adding and modifying values of the main category, use path as follows:

On the page, use the select box to list all categories:

If you want to sort the columns by sorting, use the above method to sort them first, or directly use: Select * from OA_GoodType order by path asc.

Then we use this method to list a cascade select: (because C # is lost, PHP is used instead.) assume that the data source is an array: $ cateArr

$ Options = ''; foreach ($ step_1 [0] as $ v) {$ options. = '<option id = "'. $ v ['id']. '"value = "'. $ v ['path']. '">'; $ cate_count = intval ($ v ['level']); if ($ cate_count> 1) {// if there are two or more levels, // replace each level of category in the Category field with four spaces, and set indentation $ options. = str_repeat ('', $ cate_count-1); $ options. = '| --'. $ v ['name']. '</option>';} else {$ options. = $ v ['name']. '</option> ';}}

Then you can directly output it:

<Select name = "belong" id = "belong"> <option id = "0" value = "0"> top-level category </option> <? Php echo $ options?> </Select>

Shown as follows:

In this way, you can perform the following operations during storage:

$ Belong = preg_replace ('/[^ \ d |,]/', '', $ _ POST ['belong ']; $ pid = 0; $ level = 1; $ path = ''; // process the input data if ($ belong & strpos ($ belong, ',') == false) {// if a region is selected, however, the region is invalid. MessageShowAndGoback ('the region is incorrect! ');} If ($ belong = '0') {// when the top-level category is used, the path is 0, + the class id $ path_pre = '0 ,';} else {// when the class is not top-level, the path is the parent class path $ belong + the class id $ path_pre = $ belong. ','; $ Arr = explode (',', $ belong); $ pid = end ($ Arr); $ level = count ($ Arr );} $ insertArr = array (// here there is an additional field: level. The benefit is that when you only need a certain level of classification, you can directly obtain: select * from table where level = 2; 'level' => $ level, 'pid '=> $ pid, 'path' => $ path ); // Insert the corresponding record and remember to update the path field of the record! Because the last number of the path field is the value of the id of the current record, this value is auto-incrementing, so it can only be inserted and updated: // assume that the auto-incrementing id of the record is: $ pid $ SQL = 'Update table set path = '. $ path_pre. $ id. 'Where id = '. $ id;

The modification process is complex and involves many problems. For example, 1. You cannot change the category of the class to its subclass. 2. We need to transfer the subclass of this class to the new category, then, we need to change the path of its subclass to the corresponding category listed on the New path page. We still use path as the value:

(You can use the following statement to set the category to which the category belongs :)

jQuery("#"+$id).selected=true;

Determine whether the js stage violates the conditions described in 1:

Store the original path in a hidden domain named old_path

VaR path_old = form. old_path.value; var path_new = form. Belong. value; If (path_new.indexof (path_old + ",")! =-1) {// The category to be moved contains the original category alert ('cannot be moved to this category's subcategory! '); Return false ;}

When saving PHP files, do the following:

$ Belong = preg_replace ('/[^ \ d |,]/', '', $ this-> input-> post ('belong ')); // obtain the original region information $ old_path = preg_replace ('/[^ \ d |,]/', '', $ this-> input-> post ('old _ path'); // obtain the original category region based on the path $ parents_arr = explode (',', $ old_path ); $ old_pid = $ parents_arr [count ($ parents_arr)-2]; // store the original region $ fixPath = false; // determine whether a region is modified. // 1. Determine whether the original category is consistent with the current one. $ newParends = explode (',', $ belong ); $ newPID = end ($ newParends); if ($ old_pid! = $ NewPID) {// is the original category consistent with the current category? // It is not moved to the current category if ($ rid! = $ NewPID) {$ fixPath = true ;}// 1. If you modify the region, you need to transfer all types in the region, change path and levelif ($ fixPath) {// if (strpos ($ belong, $ old_path) when there is a change to the category )! = False) {MessageShowAndGoback ('cannot be moved to the subcategory of this class');} $ Arr = explode (',', $ belong ); $ updatetArr ['pid'] = end ($ Arr); $ updatetArr ['level'] = count ($ Arr ); // new region level $ updatetArr ['path'] = $ belong. ','. $ rid; $ old_level = count ($ parents_arr)-1; // original region level} if ($ fixPath) {// when a path is modified, change all its subclasses to $ where = '(pid = \''. $ rid. '\' or path like \''. $ old_path. ', % \') '; $ newLevel = $ updatetArr ['level']-$ old_level; $ rows = $ this-> AreaClassifys-> Update (array ('path' => "replace (path, '$ old_path ','". $ updatetArr ['path']. "')", 'level' => "level + $ newLevel"), $ where, false); // $ SQL = "update cms_category set path = replace (path, '$ src_path', '$ new_path') where path like '$ src_path %' or pid = $ rid}

End