Save and Load a TreeView to SQL Database

[cjinsocal581]

Hi all, I am struggling with the following:

Environement:
VB.NET 2005
TreeView Control
SQL Database

I need a simple way to be able to save a TreeView's nodes into a SQL database and then be able to call them from the Table back into the TreeView when the app starts up. (If the table is empty, then just exit the sub)

The structure of the TreeView will be something like this.

ParentNode
--childNode1
--childNode2
--childNode3
ParentNode2
--childNode1
--childNode2
ParentNode3
--childNode1

The Treeview will only have one level of children nodes for each parent node.

So my question is, how should I format/design my table to save this data and are they any code samples to show me how to do this?

Any assistance would be greatly appreciated.

 

[mansii]

Here's a workarround:

Create 2 tables:
1. ParentNodeTable, consisting:
- ParentNodeID
- ParentNodeText
2. ChildNodeTable
- ParentNodeID
- ChildNodeID
- ChildNodeText

Then, add this sample codes:

Dim DataConn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source = C:\Work\TempDBs\db1.mdb;Jet OLEDB:Database Password=")

Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM ParentNodeTable ORDER BY ParentNodeID", DataConn)

Dim dtParent As New DataTable("Parent")
Try
	da.Fill(dtParent)
Catch ex As Exception
	MessageBox.Show(Me, ex.ToString)
End Try

For Each ParentRow As DataRow In dtParent.Rows
	Try
		Dim pNode As New TreeNode(ParentRow("ParentNodeText"))
		TreeView1.Nodes.Add(pNode)
		Dim dtChild As New DataTable("Child")
		da.SelectCommand.CommandText = "SELECT * FROM ChildNodeTable WHERE ParentNodeID=" & ParentRow("ParentNodeID") & " ORDER BY ChildNodeID"
		da.Fill(dtChild)
		For Each ChildRow As DataRow In dtChild.Rows
			Dim cNode As New TreeNode(ChildRow("ChildNodeText"))
			pNode.Nodes.Add(cNode)
		Next
	Catch ex As Exception
		MessageBox.Show(Me, ex.ToString)
	End Try
Next

Hope this helps,
mansii

 

[cjinsocal581]

Thanks for that. What about when I need to save the tree?

 

[mansii]

First, if you mean that you want to save the nodes info to a database, there are several ways to do that. the sample is a read only treeview, I mean, the nodes is uneditable, except that it is made to be.

If you enable the node LabelEdit property, then maybe this is what you are looking for:

ListView1.Items.Clear()
ListView2.Items.Clear()
For Each ParentNode As TreeNode In TreeView1.Nodes
	Dim lst1Item As New ListViewItem
	lst1Item.Text = ParentNode.Index
	lst1Item.SubItems.Add(ParentNode.Text)
	ListView1.Items.Add(lst1Item)
	If ParentNode.GetNodeCount(False) > 0 Then
		For Each ChileNode As TreeNode In ParentNode.Nodes
			Dim lst2Item As New ListViewItem
			lst2Item.Text = ParentNode.Index
			lst2Item.Text = ChileNode.Index
			lst2Item.SubItems.Add(ChileNode.Text)
			ListView2.Items.Add(lst2Item)
		Next
	End If
Next

Please note that the sample use 2 listviews to save the Parent and Chil nodes. Add some lines to save it to your database.

Hope this helps,
mansii