Question how to store hierarchical data in a databse?

[ufancha]

hello there

i was trying to build an application where the user creates a treeview (parent nodes and child nodes) at runtime and then it must be saved in to a database. when the application runs at a later time the saved treeview must populate from the database. could you please help me on how to do this?

best regards
habtamu soressa

 

[jmcilhinney]

Your database table would have at least three columns: ID, ParentID and Name. You could then use code like this to populate the tree:

Using connection As New SqlConnection("connection string here")
    Using command As New SqlCommand("SELECT * FROM MyTable", connection)
        connection.Open()

        Using reader As SqlDataReader = command.ExecuteReader()
            'Stores each node against its ID for easy retrieval.
            Dim nodesByID As New Dictionary(Of Integer, TreeNode)

            While reader.Read()
                Dim id = reader.GetInt32(0)
                Dim nodes As TreeNodeCollection

                If reader.IsDBNull(1) Then
                    'Add the new node to the tree itself.
                    nodes = Me.TreeView1.Nodes
                Else
                    'Add the new node to the appropriate parent.
                    nodes = nodesByID(reader.GetInt32(1)).Nodes
                End If

                Dim node As TreeNode = nodes.Add(reader.GetString(2))

                node.Tag = id
                nodesByID.Add(id, node)
            End While
        End Using
    End Using
End Using

Each time the user adds a new node to the tree, you can either add a corresponding record to the database there and then, or you can add a row to a DataTable and then save them as a batch when you're done using a DataAdapter.