none
Treeview HierarchyID RRS feed

  • Question

  • Hi all!

    I need to save a TreeView data to an SQL table using HierarchyID data type.

    Is this possible?

    I been googling for a while and didn't find any solution, even in c#

    Can anyone help me?

    Many, Many Thanks!

    Friday, February 7, 2020 12:54 AM

Answers

  • Hi,
    try this demo.

    Imports System.Data.SqlClient
    Imports Microsoft.SqlServer.Types
    
    Public Class Form74
    
      Private tv As New TreeView With {.Dock = DockStyle.Fill, .Margin = New Padding(5)}
      Private WithEvents btn As New Button With {.Text = "Save TreeView Nodes", .Dock = DockStyle.Top, .Margin = New Padding(5)}
      Private WithEvents btnCreate As New Button With {.Text = "Create DataTable", .Dock = DockStyle.Bottom, .Margin = New Padding(5)}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {tv, btn, btnCreate}) ' design UI
        LoadTreeView(tv.Nodes, 1) ' Load TreeView nodes with random demo data
      End Sub
    
    #Region " for demo generate random TreeView data with unique index for every node"
      Private rnd As New Random
      Private index As Integer = 1
      Private Sub LoadTreeView(tnc As TreeNodeCollection, level As Integer)
        For i As Integer = 1 To CInt(20 / level)
          Dim tn As New MyTreeNode With {.ID = index, .Text = $"Node {index}"}
          tnc.Add(tn)
          index += 1
          If rnd.NextDouble > 0.6 And index < 1500 Then LoadTreeView(tn.Nodes, level + 1)
        Next
      End Sub
    #End Region
    
      ''' <summary>
      ''' Load node info for each node in nodeList
      ''' </summary>
      ''' <param name="sender"></param>
      ''' <param name="e"></param>
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
        LoadList(tv.Nodes, "/")
        SaveList()
      End Sub
    
      Private nodeList As New List(Of Data)
    
      ''' <summary>
      ''' recursively load nodes
      ''' </summary>
      ''' <param name="nodes">parent node collection</param>
      ''' <param name="hierarchy">parent node hieryrchy</param>
      Private Sub LoadList(nodes As TreeNodeCollection, hierarchy As String)
        For Each node As MyTreeNode In nodes
          Dim path = $"{hierarchy}{node.ID}/"
          nodeList.Add(New Data With {.NodeText = node.Name, .Path = path})
          LoadList(node.Nodes, path)
        Next
      End Sub
    
      ''' <summary>
      ''' Save nodeList to SQL server database
      ''' </summary>
      Private Sub SaveList()
        Try
          Using cn As New SqlConnection(My.Settings.cnCloud)
            cn.Open()
            Using cmd As New SqlCommand("INSERT INTO Table1(NodeText, HierarchyID) Values(@NodeText, @HierarchyID)", cn)
              Dim par1 = cmd.Parameters.Add("@NodeText", SqlDbType.NVarChar)
              Dim par2 = cmd.Parameters.Add("@HierarchyID", SqlDbType.NVarChar)
              For Each d In nodeList
                par1.Value = d.NodeText
                par2.Value = d.HierarchyID.ToString
                cmd.ExecuteNonQuery()
                Console.WriteLine(d.HierarchyID.ToString)
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      Public Class MyTreeNode
        Inherits TreeNode
        Public Property ID As Integer ' unique index
      End Class
    
      Public Class Data
        Public Property NodeText As String
        Public Property Path As String
        Public ReadOnly Property HierarchyID As SqlHierarchyId
          Get
            Return SqlHierarchyId.Parse(Path)
          End Get
        End Property
      End Class
    
      Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
        Try
          btnCreate.Enabled = False
          Using cn As New SqlConnection(My.Settings.cnCloud)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              ' delete previous version
              cmd.CommandText = "DROP Table Table1"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              ' Create Table
              cmd.CommandText = "CREATE Table Table1([ID] int Not NULL IDENTITY, NodeText nvarchar(50), [HierarchyID] hierarchyid, Constraint [PK_Table1] PRIMARY KEY ([ID]))"
              cmd.ExecuteNonQuery()
            End Using
          End Using
          MsgBox("finish creating table")
        Catch ex As Exception
          MsgBox(ex.Message)
        Finally
          btnCreate.Enabled = True
        End Try
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by marobi_uy Monday, February 17, 2020 10:10 PM
    Wednesday, February 12, 2020 7:47 AM

All replies

  • Hi,
    try following demo:

    Imports System.Data.SqlClient
    
    Public Class Form71
    
      Private tv As New TreeView With {.Dock = DockStyle.Fill, .Margin = New Padding(5)}
      Private WithEvents btn As New Button With {.Text = "Save TreeView Nodes", .Dock = DockStyle.Top, .Margin = New Padding(5)}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {tv, btn}) ' design UI
        LoadTreeView(tv.Nodes, 1) ' Load TreeView nodes with random demo data
      End Sub
    
    #Region " for demo generate random TreeView data"
      Private rnd As New Random
      Private index As Integer = 1
      Private Sub LoadTreeView(tnc As TreeNodeCollection, level As Integer)
        For i = 1 To 20 / level
          Dim tn As New TreeNode($"Node {index}")
          tnc.Add(tn)
          index += 1
          If rnd.NextDouble > 0.6 And index < 1500 Then LoadTreeView(tn.Nodes, level + 1)
        Next
      End Sub
    #End Region
    
      ''' <summary>
      ''' Load node info for each node in nodeList
      ''' </summary>
      ''' <param name="sender"></param>
      ''' <param name="e"></param>
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
        LoadList(tv.Nodes, 0)
        SaveList()
      End Sub
    
      Private nodeList As New List(Of Data)
    
      ''' <summary>
      ''' recursively load nodes
      ''' </summary>
      ''' <param name="nodes">parent node collection</param>
      ''' <param name="level">actual node level</param>
      Private Sub LoadList(nodes As TreeNodeCollection, level As Integer)
        For Each node As TreeNode In nodes
          nodeList.Add(New Data With {.NodeText = node.Name, .HierarchyID = level})
          LoadList(node.Nodes, level + 1)
        Next
      End Sub
    
      ''' <summary>
      ''' Save nodeList to SQL server database
      ''' </summary>
      Private Sub SaveList()
        Try
          Using cn As New SqlConnection(My.Settings.cnSQL)
            cn.Open()
            Using cmd As New SqlCommand("INSERT INTO Tab1(NodeText,HierarchyID) Values(@NodeText,@HierarchyID)", cn)
              Dim par1 = cmd.Parameters.Add("@NodeText", SqlDbType.NVarChar)
              Dim par2 = cmd.Parameters.Add("@HierarchyID", SqlDbType.Int)
              For Each d In nodeList
                par1.Value = d.NodeText
                par2.Value = d.HierarchyID
                cmd.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      Public Class Data
        Public Property NodeText As String
        Public Property HierarchyID As Integer
      End Class
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    Friday, February 7, 2020 4:25 AM
  • Many thanks, but, what if I have a column type of hierarchyid data?

    In your invaluable example it´s an int type...

    It's possible?

    Or...How to convert node level to hierarchyID?

    Many Thanks!


    • Edited by marobi_uy Wednesday, February 12, 2020 12:31 AM Ampliation
    Wednesday, February 12, 2020 12:21 AM
  • Hi,
    try this demo.

    Imports System.Data.SqlClient
    Imports Microsoft.SqlServer.Types
    
    Public Class Form74
    
      Private tv As New TreeView With {.Dock = DockStyle.Fill, .Margin = New Padding(5)}
      Private WithEvents btn As New Button With {.Text = "Save TreeView Nodes", .Dock = DockStyle.Top, .Margin = New Padding(5)}
      Private WithEvents btnCreate As New Button With {.Text = "Create DataTable", .Dock = DockStyle.Bottom, .Margin = New Padding(5)}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {tv, btn, btnCreate}) ' design UI
        LoadTreeView(tv.Nodes, 1) ' Load TreeView nodes with random demo data
      End Sub
    
    #Region " for demo generate random TreeView data with unique index for every node"
      Private rnd As New Random
      Private index As Integer = 1
      Private Sub LoadTreeView(tnc As TreeNodeCollection, level As Integer)
        For i As Integer = 1 To CInt(20 / level)
          Dim tn As New MyTreeNode With {.ID = index, .Text = $"Node {index}"}
          tnc.Add(tn)
          index += 1
          If rnd.NextDouble > 0.6 And index < 1500 Then LoadTreeView(tn.Nodes, level + 1)
        Next
      End Sub
    #End Region
    
      ''' <summary>
      ''' Load node info for each node in nodeList
      ''' </summary>
      ''' <param name="sender"></param>
      ''' <param name="e"></param>
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
        LoadList(tv.Nodes, "/")
        SaveList()
      End Sub
    
      Private nodeList As New List(Of Data)
    
      ''' <summary>
      ''' recursively load nodes
      ''' </summary>
      ''' <param name="nodes">parent node collection</param>
      ''' <param name="hierarchy">parent node hieryrchy</param>
      Private Sub LoadList(nodes As TreeNodeCollection, hierarchy As String)
        For Each node As MyTreeNode In nodes
          Dim path = $"{hierarchy}{node.ID}/"
          nodeList.Add(New Data With {.NodeText = node.Name, .Path = path})
          LoadList(node.Nodes, path)
        Next
      End Sub
    
      ''' <summary>
      ''' Save nodeList to SQL server database
      ''' </summary>
      Private Sub SaveList()
        Try
          Using cn As New SqlConnection(My.Settings.cnCloud)
            cn.Open()
            Using cmd As New SqlCommand("INSERT INTO Table1(NodeText, HierarchyID) Values(@NodeText, @HierarchyID)", cn)
              Dim par1 = cmd.Parameters.Add("@NodeText", SqlDbType.NVarChar)
              Dim par2 = cmd.Parameters.Add("@HierarchyID", SqlDbType.NVarChar)
              For Each d In nodeList
                par1.Value = d.NodeText
                par2.Value = d.HierarchyID.ToString
                cmd.ExecuteNonQuery()
                Console.WriteLine(d.HierarchyID.ToString)
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      Public Class MyTreeNode
        Inherits TreeNode
        Public Property ID As Integer ' unique index
      End Class
    
      Public Class Data
        Public Property NodeText As String
        Public Property Path As String
        Public ReadOnly Property HierarchyID As SqlHierarchyId
          Get
            Return SqlHierarchyId.Parse(Path)
          End Get
        End Property
      End Class
    
      Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
        Try
          btnCreate.Enabled = False
          Using cn As New SqlConnection(My.Settings.cnCloud)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              ' delete previous version
              cmd.CommandText = "DROP Table Table1"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              ' Create Table
              cmd.CommandText = "CREATE Table Table1([ID] int Not NULL IDENTITY, NodeText nvarchar(50), [HierarchyID] hierarchyid, Constraint [PK_Table1] PRIMARY KEY ([ID]))"
              cmd.ExecuteNonQuery()
            End Using
          End Using
          MsgBox("finish creating table")
        Catch ex As Exception
          MsgBox(ex.Message)
        Finally
          btnCreate.Enabled = True
        End Try
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by marobi_uy Monday, February 17, 2020 10:10 PM
    Wednesday, February 12, 2020 7:47 AM
  • Hi marobi_uy,

    Thanks for your feedback.

    According to your description, you can refer to the following references:

    1. Use a datatable with a hierarchyid datatype to populate a treeview
    2. Loading a TreeView using HierarchyID

    Besides, here's a reference related to saving TreeView data to DB.

    Save TreeView data to a DB and be able to read it back.

    Hope them could be helpful.

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 12, 2020 8:42 AM
    Moderator
  • Hi,
    try the following demo with Save TreeView and Load TreeView with SqlHieryrchyID.

    Imports System.Data.SqlClient
    Imports Microsoft.SqlServer.Types
    
    Public Class Form1
    
      Private tv As New TreeView With {.Dock = DockStyle.Fill, .Margin = New Padding(5)}
      Private WithEvents btnSave As New Button With {.Text = "Save TreeView Nodes", .Dock = DockStyle.Top, .Margin = New Padding(5)}
      Private WithEvents btnLoad As New Button With {.Text = "Load TreeView from database", .Dock = DockStyle.Top, .Margin = New Padding(5)}
      Private WithEvents btnCreate As New Button With {.Text = "Create DataTable", .Dock = DockStyle.Bottom, .Margin = New Padding(5)}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {tv, btnLoad, btnSave, btnCreate}) ' design UI
        LoadTreeView(tv.Nodes, 1) ' Load TreeView nodes with random demo data
      End Sub
    
    #Region " for demo generate random TreeView data with unique index for every node"
      Private rnd As New Random
      Private index As Integer = 1
      Private Sub LoadTreeView(tnc As TreeNodeCollection, level As Integer)
        For i As Integer = 1 To CInt(20 / level)
          Dim tn As New MyTreeNode With {.ID = index, .Text = $"Node {index}"}
          tnc.Add(tn)
          index += 1
          If rnd.NextDouble > 0.6 And index < 1500 Then LoadTreeView(tn.Nodes, level + 1)
        Next
      End Sub
    #End Region
    
      ''' <summary>
      ''' Load node info for each node in nodeList and save nodeList
      ''' </summary>
      ''' <param name="sender"></param>
      ''' <param name="e"></param>
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        LoadList(tv.Nodes, "/")
        SaveList()
        MsgBox("finished saving nodes")
      End Sub
    
      Private nodeList As New List(Of Data)
    
      ''' <summary>
      ''' recursively load nodes
      ''' </summary>
      ''' <param name="nodes">parent node collection</param>
      ''' <param name="hierarchy">parent node hieryrchy</param>
      Private Sub LoadList(nodes As TreeNodeCollection, hierarchy As String)
        For Each node As MyTreeNode In nodes
          Dim path = $"{hierarchy}{node.ID}/"
          nodeList.Add(New Data With {.ID = node.ID, .NodeText = node.Text, .HierarchyID = SqlHierarchyId.Parse(path)})
          LoadList(node.Nodes, path)
        Next
      End Sub
    
      ''' <summary>
      ''' Save nodeList to SQL server database
      ''' </summary>
      Private Sub SaveList()
        Try
          Using cn As New SqlConnection(My.Settings.cnCloud)
            cn.Open()
            Using cmd As New SqlCommand("INSERT INTO Table1(NodeText, HierarchyID) Values(@NodeText, @HierarchyID)", cn)
              Dim par1 = cmd.Parameters.Add("@NodeText", SqlDbType.NVarChar)
              Dim par2 = cmd.Parameters.Add("@HierarchyID", SqlDbType.NVarChar)
              For Each d In nodeList
                par1.Value = d.NodeText
                par2.Value = d.HierarchyID.ToString
                cmd.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      ''' <summary>
      ''' Load nodeList from database and load TreeView
      ''' </summary>
      ''' <param name="sender"></param>
      ''' <param name="e"></param>
      Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
        Try
          btnLoad.Enabled = False
          Using cn As New SqlConnection(My.Settings.cnCloud)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              ' Create Table
              cmd.CommandText = "SELECT [ID], [NodeText], [HierarchyID] FROM Table1;"
              nodeList = New List(Of Data)
              Dim rdr = cmd.ExecuteReader
              While rdr.Read
                nodeList.Add(New Data With {.ID = CInt(rdr("ID")),
                             .NodeText = rdr("NodeText").ToString,
                             .HierarchyID = DirectCast(rdr("HierarchyID"), SqlHierarchyId)})
              End While
            End Using
          End Using
          ' Load TreeView
          tv.Nodes.Clear()
          LoadTreeNodes(tv.Nodes, SqlHierarchyId.Parse("/"))
        Catch ex As Exception
          MsgBox(ex.Message)
        Finally
          btnLoad.Enabled = True
        End Try
      End Sub
    
      Private Sub LoadTreeNodes(nodes As TreeNodeCollection, iID As SqlHierarchyId)
        For Each item In From d In nodeList Where d.HierarchyID.GetAncestor(1).Equals(iID)
          Dim node As New MyTreeNode With {.ID = item.ID, .Text = item.NodeText}
          nodes.Add(node)
          LoadTreeNodes(node.Nodes, item.HierarchyID)
        Next
      End Sub
    
      Public Class MyTreeNode
        Inherits TreeNode
        Public Property ID As Integer ' unique index
      End Class
    
      Public Class Data
        Public Property ID As Integer
        Public Property NodeText As String
        Public Property HierarchyID As SqlHierarchyId
      End Class
    
      Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
        Try
          btnCreate.Enabled = False
          Using cn As New SqlConnection(My.Settings.cnCloud)
            cn.Open()
            Using cmd As New SqlCommand With {.Connection = cn}
              ' delete previous version
              cmd.CommandText = "DROP Table Table1"
              Try
                cmd.ExecuteNonQuery()
              Catch ex As Exception
              End Try
              ' Create Table
              cmd.CommandText = "CREATE Table Table1([ID] int Not NULL IDENTITY, NodeText nvarchar(50), [HierarchyID] hierarchyid, Constraint [PK_Table1] PRIMARY KEY ([ID]))"
              cmd.ExecuteNonQuery()
            End Using
          End Using
          MsgBox("finish creating table")
        Catch ex As Exception
          MsgBox(ex.Message)
        Finally
          btnCreate.Enabled = True
        End Try
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Wednesday, February 12, 2020 2:13 PM