First, we establish a table TBTree in SQL Server 2000, the structure design of the table is as follows:
Column name
type of data
description
length
Primary key
Id
Int
Node number
4
Yes
Context
Nvarchar
The node content we have to display
50
ParentID
Int
Parent node number
4
DEPTH
Int
depth
4
Regarding the DEPTH field, it is mainly the number of layers of the node, that is, which layer of this node is in the tree. With a Depth field, we will be more convenient when we program, and only one WHERE condition can be queried in the SQL query. All nodes of the current depth layer can be queried. If we don't design a Depth field, you can also do a similar query, which requires loop processing in the SQL query in the background. Alternatively, you can do these processes on the front desk without processing in the background database server. Below we will introduce these ways:
Construction tables in SQL Server 2000:
Create Table [DBO]. [TBTree] (
[ID] [INT] Identity (1, 1) Not NULL,
[Context] [nvarchar] (50) collate chinese_prc_ci_as null,
[Parentid] [int] NULL
) On [primary]
Add the following records in the table:
Set Identity_Insert Tbtree On
INSERT TBTREE (ID, Context, Parentid) Values (1, 'China ", 0)
INSERT TBTREE (ID, Context, Parentid Values (2, 'Beijing ", 11)
INSERT TBTREE (ID, Context, Parentid) Values (3, 'Tianjin ", 1)
INSERT TBTREE (ID, Context, Parentid) Values (4, 'Hebei Province ", 1)
INSERT TBTREE (ID, Context, Parentid) Values (5, 'Guangdong ", 1)
INSERT TBTREE (ID, Context, Parentid Values (6, 'Guangzhou ", 5)
INSERT TBTREE (ID, Context, Parentid) Values (7, 'Sichuan ", 1)
INSERT TBTREE (ID, Context, Parentid) Values (8, 'Chengdu', 7)
INSERT TBTREE (ID, Context, Parentid) Values (9, 'Shenzhen', 5)
INSERT TBTREE (ID, Context, Parentid) VALUES (10, 'Shijiazhuang', 4)
INSERT TBTREE (ID, Context, Parentid) Values (11, 'Liaoning Province ", 1)
INSERT TBTREE (ID, Context, Parentid) Values (12, 'Dalian', 11)
INSERT TBTREE (ID, Context, Parentid) Values (13, 'Shanghai ", 1)
INSERT TBTREE (ID, Context, Parentid) Values (14, 'Tianhe Software Park ", 6)
INSERT TBTREE (ID, Context, Parentid) Values (15, 'Shantou ", 5)
Set Identity_INSERT TBTREE OFF The implementation in VB6 when there is a DEPTH field:
Let's take a look, add a new node to the TreeView to TreeView, the syntax is as follows:
Nodes.add (Relative, [RELATIONSHIP] [, Key] [, Text] [, Image] [, SELECTEDIMAGE])
From the above grammar, you can see that adding a node, just know the key number of the parent node number, you can add a child node through this Key.
If the result is queried in the database, you can first add the first layer of nodes, add the node of the second layer to the node. So, I wrote an AddTree function below, the parameter is the number of layers (depth), and the RS is open smaller than or equal to this number of records, and sorts them by layers. Therefore, one layer is added, and a tree can be completed by a circular record set. Simple enough!
DIM CN as adodb.connection 'Defines the connection of the database
DIM RS as adodb.recordset
'Project ---> Quote ---> Microsoft ActiveX Data Object 2.x (version number)
Private sub flow_load ()
Set cn = new adodb.connection
'Connect to the database
Cn.connectionstring = "provider = sqloledb; data source = pmserver; initial catalog = Benchmark; user ID = sa; password = sa;"
Cn.open
Call Addtree (3)
End Sub
Private sub addtree (byval intDepth as integer)
'Open the record set, get all the nodes that are less than some depth, and sort by depth
SET RS = New Adodb.Recordset
Rs.open "SELECT * from TBTree WHERE Depth <= '" & INTDEPTH & "' Order By Depth", CN, AdoPENDYNAMIC, ADLOCKREADOONLY
DIM XNOD As Node
Do While Not Rs.eof
IF = 0 THEN
'Join root nodes
Set XNOD = TreeView1.nodes.add (, "Key" & rs.fields ("ID"), RS.Fields ("Context"))
Else
'Join child node
Set XNOD = TreeView1.nodes.add ("Key" & rs.fields ("ParentID"), TVWChild, "Key" & rs.fields ("id"), rs.fields ("context")))
END IF
Xnod.ensurevisible
Rs.movenext
Loop
Rs.close
End Sub
The program operation results are shown below:
Realization when there is no defth (depth)
The above program is completely relying on the column of Depth. If there is no depth of this column to sort, you can see that the above code will be wrong! From the design of the TBTree table, it can be seen that if there is no defth, you can query all nodes under a node as long as you have the ID field and the ParentID field, the answer is yes! See the stored procedure below, the role is that you can find all the nodes below! And these nodes are sorted by hierarchy!
Establish a stored procedure:
Create Procedure Spgettree
@ID int)
AS
Set nocount on
Declare @TMP Table (ID INT, Context Varchar (50), ParentId Int, Depth Int
Insert @tmp select * from tbtree where id = @ id
While exists (SELECT 1 from TBTree A, @ Tmp B Where a.parentId = B.ID and A.ID Not in (Select ID from @TMP))
INSERT @TMP SELECT A. * from TBTree a, @ Tmp B Where a.parentId = B.ID and A.ID Not in (Select ID from @TMP)
Select * from @TMP
Set nocount off
Go
Analysis: The above stored procedure, the While statement is a layer of gently will insert the node of the tree into the target table @TMP. Interested readers can track themselves.
We use the above stored procedure to easily write code to add a tree structure with VB6, because the data obtained by this stored procedure is in order to be sequentially arranged, and we can add nodes in the order of recurring records.
Private Sub AddTreeex (Byval InTid As Integer)
SET RS = New Adodb.Recordset
Rs.Open "Spgettree" & intid, CN, AdoPENDYNAMIC, ADLOCKREADONLY
DIM XNOD As Node
Do While Not Rs.eof
IF = 0 THEN
Set XNOD = TreeView1.nodes.add (, "Key" & rs.fields ("ID"), RS.Fields ("Context"))
Else
Set XNOD = TreeView1.nodes.add ("Key" & rs.fields ("ParentID"), TVWChild, "Key" & rs.fields ("id"), rs.fields ("context")))
END IF
Xnod.ensurevisible
Rs.movenext
Loop
Rs.close
End Sub
Implementation in VB.NET
In .NET, because the usage of the TreeView control and the usage in VB6 are different! The previous VB6 programmer is worried because there is no key attribute of the node! In .NET, the TreeView tree node is a collection, and each Treenode can contain a collection of other TREENODE objects. To determine where you are in the tree structure, you have to use the fullpath property. We know that the addition node can only be added to this node after finding a node. Now VB.NET has a key attribute, which is a big inconvenience to the operation. Microsoft MSDN has an article with inheritance and overloading, extending the TreeView control, adds a Key property to the node. Interested readers can look at How to: Create a Key Property for a TreeView Node In Visual Basic .NET This article. But the universal is: This article is just a NodeKey property for Treenode, but does not provide a good Key value search function. Although all this can be extended with code, the code is lengthy.
Therefore, the tree structure of many layers of layer is added can only be recursive. Moreover, the code below us is very refined, and only one ParentId is transmitted to the recursive process, and all nodes under this number are loaded into the tree structure!
Fully reflected: Simple is good idea.
Design ideas: Query from the database to all nodes, add to DataView
Using DataView
The.rowfilter property gets all the records under a parent node number ParentID, and recursively recursively loops.
Implementation in VB.NET:
Private DS as new dataset ()
The 'AddTree recursive function uses a table in the data set each time, so defined as a private
Private Sub Form1_Load (Byval E AS System.Object, Byval E AS System.Eventargs) Handles MyBase.Load
'' Define Database Connections
DIM CN As New SqlConnection ()
Try
'Initializing the connection string
Cn.connectionstring = "Data Source = PMServer; Initial Catalog = Benchmark; Persist Security Info = FALSE; User ID = SA; Password = sa;"
Cn.open ()
'Add command, get data from the database
DIM SQLCMD As New SqlCommand ()
Sqlcmd.connection = CN
Sqlcmd.commandtext = "SELECT * from TBTree"
Sqlcmd.commandtype = commandType.text
DIM ADP As SqldataAdapter = New SqldataAdapter (SQLCMD)
ADP.FILL (DS)
Catch exception
MSGBOX (ex.Message)
Finally
'Close connection
Cn.close ()
END TRY
'Call the recursive function, complete the generation of the tree structure
AddTree (0, Nothing)
End Sub
'? Recursively add a tree node
Private Sub Addtree (byval Parentid As Integer, Byval Pnode As Treenode)
Dim Node as Treenode
DIM DVTREE As New DataView () DVTree = New DataView (DS.TABLES (0))
'Filter ParentID to get all current child nodes
Dvtree.rowfilter = "ParentId =" ParentId.tostring
DIM ROW AS DATAROWVIEW
For Each Row in Dvtree
If pnode is nothing then 'judgments if the root node
'? Add root node
Node = TreeView1.nodes.Add (Row ("Context"). Tostring ())
'? Recurns again
AddTree (INT32.PARSE (Row ("ID"). Tostring ()), NODE
Else
'Add a child node of the current node
Node = pnode.nodes.add (Row ("Context"). TOSTRING ())
'? Recurns again
AddTree (INT32.PARSE (Row ("ID"). Tostring ()), NODE
END IF
Node.ensurevisible ()
NEXT
End Sub
The program operation results are shown below:
Implementation in C #:
With the code implemented in VB.NET, we can change to C #'s grammar:
DataSet DS = New Dataset ();
Private Void Form1_Load (Object Sender, System.EventArgs E)
{
/ / Define database connections
SqlConnection CN = New SQLCONNECTION ();
Try
{
// Initialize the connection string
Cn.connectionstring = "data source = pmserver; initial catalog = Benchmark; Persist security info = false; user ID = sa; password = sa;";
Cn.open ();
// Add command to get data from the database
Sqlcommand sqlcmd = new SQLCOMMAND ();
Sqlcmd.connection = cn;
Sqlcmd.commandtext = "SELECT * from TBTree";
Sqlcmd.commandtype = commandtype.text;
SqlDataAdapter ADP = New SqlDataAdapter (SQLCMD);
ADP.FILL (DS);
}
Catch (Exception EX)
{
Throw (ex);
}
Finally
{
Cn.close ();
}
// Call the recursive function and complete the generation of the tree structure
AddTree (0, (Treenode) NULL;
}
// Regenerate the node of the tree
Public void addtree (int parentid, treenode pnode)
{
DataView DVTree = New DataView (ds.tables [0]);
// Filter ParentID to get all the current child nodes
Dvtree.rowfilter = "[parentid] =" ParentID;
Foreach (DataRowView Row In Dvtree)
{
IF (pnode == null)
{// '? Add root node
Treenode Node = TreeView1.nodes.add (Row ["Context"]. TOSTRING ()); addtree (INT32.PARS (Row ["Id"]. Tostring ()), Node); // Regeneration again
}
Else
{// Add the child node of the current node
Treenode node = pnode.nodes.add (row ["context"]. TOSTRING ());
AddTree (Int32.Parse (ROW ["ID"]. TOSTRING ()), Node); // Regeneration again
}
}
}
Postscript: Please read the reader to modify the connection string settings in the program.
Attachment: Related Microsoft MSDN documents, including build a tree structure from XML in VB6 and .NET
http://support.microsoft.com/default.aspx?kbid=311318
http://support.microsoft.com/default.aspx?kbid=308063
Http://support.microsoft.com/default.aspx?kbid=317597
Http://support.microsoft.com/default.aspx?kbid=244954
