Fundamental work with VB.NET Datatable and Treeview

neytal 1 Reputation point
2021-01-18T16:26:48.053+00:00

Hi,
I am coming with maybe silly question but I just cant figure it out.
I have started project, not small from my point of view but I relisted after finishing first part that I handle in this program so big data that it will be really bad for user to maintain the data.
It is going to be software for sales. At this point I have created database for the basic items. Every item is branched in tree view in 4 levels. In 4th level user could add any hourly based or price items. It comes from another library (data table) witch will maintained by purchase department (price, supplier, date of validate, ect). In the main data table of the library I have every item from Treeview in datarow where I have cca 16 columns (ID in treeview, Text, Tooltip, Parent , Fullpath and values (type of node) value currency, validation ect).
When I started coding I worked with cca 50 items (50x cca 25 nodes) and everything worked good and fast. Than I made imported and imported all 2500 item what we currently work with in sales. It is 125000 datarows to work with everytime something changes.

My question is how to handle the data and update the treeview. Id already did some changes like, I loaded datatable to 2d array and loop thru it (it helped little). I tried to avoid dirty loops by exit for after finding and calling Sub to handle. But I think that I do fundamentally something wrong. Is there someone who could help me with it, I just want to work with sotcha big data in comfortable way, but I cant figure out how.

I paste here two codes, first is to load treeview after opening library. Second is Sub what is for update and recalculation of all parents and update icons by validation.

Public Sub loadTreviewLibrabryTest()
        Me.TreeView2.Nodes.Clear()
        Dim dt As DataTable = DsUsers.dtUsers
        Dim new_node As TreeNode

        Debug.Print("Start load tree: " & Now)

        Dim a()() As Object = dt.Rows.Cast(Of DataRow).Select(Function(dr) dr.ItemArray).ToArray

        For j As Integer = 0 To a.GetLength(0) - 1
            If a(j)(NodeType).ToString = "Bs" Then
                new_node = New TreeNode(a(j)(NodeID))
                new_node.Name = a(j)(NodeID).ToString
                new_node.Text = a(j)(NodeText).ToString
                new_node.ImageIndex = a(j)(NodeImageIndex)
                new_node.SelectedImageIndex = a(j)(NodeImageIndex)
                If Not a(j)(NodeText2).ToString Is Nothing Then new_node.ToolTipText = a(j)(NodeText2).ToString
                Me.TreeView2.Nodes.Add(new_node)
            ElseIf a(j)(NodeType).ToString = "Mech" Then
                new_node = New TreeNode(a(j)(NodeID))
                new_node.Name = a(j)(NodeID).ToString
                new_node.Text = a(j)(NodeText).ToString
                new_node.ImageIndex = a(j)(NodeImageIndex)
                new_node.SelectedImageIndex = a(j)(NodeImageIndex)
                If Not a(j)(NodeText2).ToString Is Nothing Then new_node.ToolTipText = a(j)(NodeText2).ToString
                Dim no() As TreeNode = Me.TreeView2.Nodes.Find((a(j)(NodeParent)), True)
                no(0).Nodes.Add(new_node)
            ElseIf a(j)(NodeType).ToString = "Ele" Then
                new_node = New TreeNode(a(j)(NodeID))
                new_node.Name = a(j)(NodeID).ToString
                new_node.Text = a(j)(NodeText).ToString
                new_node.ImageIndex = a(j)(NodeImageIndex)
                new_node.SelectedImageIndex = a(j)(NodeImageIndex)
                If Not a(j)(NodeText2).ToString Is Nothing Then new_node.ToolTipText = a(j)(NodeText2).ToString
                Dim no() As TreeNode = Me.TreeView2.Nodes.Find((a(j)(NodeParent)), True)
                no(0).Nodes.Add(new_node)
            ElseIf a(j)(NodeType).ToString = "Area" Then
                new_node = New TreeNode(a(j)(NodeID))
                new_node.Name = a(j)(NodeID).ToString
                new_node.Text = a(j)(NodeText).ToString
                new_node.ImageIndex = a(j)(NodeImageIndex)
                new_node.SelectedImageIndex = a(j)(NodeImageIndex)
                If Not a(j)(NodeText2).ToString Is Nothing Then new_node.ToolTipText = a(j)(NodeText2).ToString
                Dim no() As TreeNode = Me.TreeView2.Nodes.Find((a(j)(NodeParent)), True)
                no(0).Nodes.Add(new_node)
            ElseIf a(j)(NodeType).ToString = "Price" Then
                new_node = New TreeNode(a(j)(NodeID))
                new_node.Name = a(j)(NodeID).ToString
                new_node.Text = a(j)(NodeText).ToString
                new_node.ImageIndex = a(j)(NodeImageIndex)
                new_node.SelectedImageIndex = a(j)(NodeImageIndex)
                If Not a(j)(NodeText2).ToString Is Nothing Then new_node.ToolTipText = a(j)(NodeText2).ToString
                Dim no() As TreeNode = Me.TreeView2.Nodes.Find((a(j)(NodeParent)), True)
                no(0).Nodes.Add(new_node)
            ElseIf a(j)(NodeType).ToString = "Hours" Then
                new_node = New TreeNode(a(j)(NodeID))
                new_node.Name = a(j)(NodeID).ToString
                new_node.Text = a(j)(NodeText).ToString
                new_node.ImageIndex = a(j)(NodeImageIndex)
                new_node.SelectedImageIndex = a(j)(NodeImageIndex)
                If Not a(j)(NodeText2).ToString Is Nothing Then new_node.ToolTipText = a(j)(NodeText2).ToString
                Dim no() As TreeNode = Me.TreeView2.Nodes.Find((a(j)(NodeParent)), True)
                no(0).Nodes.Add(new_node)
            End If

        Next

        Debug.Print("End load tree: " & Now)
    End Sub

 Public Sub Validate_changes(ByVal ID As Integer)
        Dim found_node As Boolean = False
        Dim dt As DataTable = DsUsers.dtUsers
        Dim Pr As DataTable = DsLibSet.dsPriceMirrior
        Dim Ip As DataTable = DsLibSet.dsIntPerf
        Dim Red As Integer
        Dim green As Integer
        Dim Value As Double
        Dim Valid As Date

        Debug.Print("Start validate changes: " & Now)

        For Each R As DataRow In dt.Rows
            If R.Item("NodeID").ToString = ID Then

                If R.Item("NodeType").ToString = "Price" Then
                    found_node = False
                    For Each P As DataRow In Pr.Rows
                        If P.Item("PrMiName").ToString = R.Item("NodeName").ToString Then
                            R.Item("NodeValueCurrencey") = P.Item("PrMiCur").ToString
                            R.Item("NodePriceItemUnitPrice") = P.Item("PrMiPrice").ToString
                            R.Item("NodeValue") = R.Item("ItemAmount") * P.Item("PrMiPrice").ToString
                            R.Item("NodeText") = P.Item("PrMiName").ToString & " (" & R.Item("ItemAmount").ToString & " x " & R.Item("NodePriceItemUnitPrice").ToString & R.Item("NodeValueCurrencey").ToString & ")"

                            If P.Item("PrValidType").ToString = "Local" Then
                                If P.Item("PrValid") = Nothing Or R.Item("NodeValid") = "1.1.1900" Or R.Item("NodeValid") = "1.1.0001" Then
                                    R.Item("NodeValid") = DBNull.Value
                                    R.Item("NodeImageIndex") = 16
                                    R.Item("NodeText2") = "No validation"
                                Else
                                    R.Item("NodeValid") = P.Item("PrValid")
                                    R.Item("NodeImageIndex") = 15
                                    R.Item("NodeText2") = "Validated on " & R.Item("NodeValid").ToString
                                End If
                            ElseIf P.Item("PrValidType").ToString = "External" Then
                                If R.Item("NodeValid") = Nothing Or R.Item("NodeValid") = "1.1.1900" Or R.Item("NodeValid") = "1.1.0001" Then
                                    R.Item("NodeImageIndex") = 16
                                    R.Item("NodeText2") = "No validation"
                                Else
                                    R.Item("NodeImageIndex") = 15
                                    R.Item("NodeText2") = "Validated on " & R.Item("NodeValid").ToString
                                End If
                            End If
                            found_node = True
                        End If
                    Next
                    If found_node = False Then
                        R.Item("NodeImageIndex") = 18
                        R.Item("NodeText") = R.Item("NodeText") & " Not found in Price List"
                    End If

                    Dim no() As TreeNode = Me.TreeView2.Nodes.Find(R.Item("NodeID"), True)
                    no(0).Text = R.Item("NodeText")
                    no(0).ImageIndex = R.Item("NodeImageIndex")
                    no(0).SelectedImageIndex = R.Item("NodeImageIndex")
                    If Not R.Item("NodeText2") Is Nothing Then no(0).ToolTipText = R.Item("NodeText2").ToString
                    Call Validate_changes(R.Item("NodeParent"))

                ElseIf R.Item("NodeType").ToString = "Hours" Then
                    found_node = False
                    For Each P As DataRow In Ip.Rows
                        If P.Item("InPerName").ToString = R.Item("NodeName").ToString Then
                            R.Item("NodeValueCurrencey") = P.Item("InPerCur").ToString
                            R.Item("NodeHrRate") = P.Item("InPerRate").ToString
                            R.Item("NodePriceItemUnitPrice") = P.Item("InPerHrs") * HrRateValue(P.Item("InPerRate"))
                            R.Item("NodeValue") = R.Item("NodeHrAmount") * R.Item("NodePriceItemUnitPrice")
                            R.Item("NodeText") = R.Item("NodeName").ToString & " (" & R.Item("NodeHrAmount").ToString & " x " & R.Item("NodePriceItemUnitPrice").ToString & R.Item("NodeValueCurrencey").ToString & ")"
                            If P.Item("InValidType").ToString = "Local" Then
                                If P.Item("InValid") = Nothing Or R.Item("NodeValid") = "1.1.1900" Or R.Item("NodeValid") = "1.1.0001" Then
                                    R.Item("NodeValid") = DBNull.Value
                                    R.Item("NodeImageIndex") = 13
                                    R.Item("NodeText2") = "No validation"
                                Else
                                    R.Item("NodeValid") = P.Item("InValid")
                                    R.Item("NodeImageIndex") = 12
                                    R.Item("NodeText2") = "Validated on " & R.Item("NodeValid").ToString
                                End If
                            ElseIf P.Item("InValidType").ToString = "External" Then
                                If R.Item("NodeValid") = Nothing Or R.Item("NodeValid") = "1.1.1900" Or R.Item("NodeValid") = "1.1.0001" Then
                                    R.Item("NodeImageIndex") = 13
                                    R.Item("NodeText2") = "No validation"
                                Else
                                    R.Item("NodeImageIndex") = 12
                                    R.Item("NodeText2") = "Validated on " & R.Item("NodeValid").ToString
                                End If
                            End If
                            found_node = True
                        End If
                    Next
                    If found_node = False Then
                        R.Item("NodeImageIndex") = 18
                        R.Item("NodeText") = R.Item("NodeText") & " Not found in Internal performance List"
                    End If
                    Dim no() As TreeNode = Me.TreeView2.Nodes.Find(R.Item("NodeID"), True)
                    no(0).Text = R.Item("NodeText")
                    no(0).ImageIndex = R.Item("NodeImageIndex")
                    no(0).SelectedImageIndex = R.Item("NodeImageIndex")
                    If Not R.Item("NodeText2") Is Nothing Then no(0).ToolTipText = R.Item("NodeText2").ToString
                    Call Validate_changes(R.Item("NodeParent"))

                ElseIf R.Item("NodeType").ToString = "Area" Then
                    Red = 0
                    green = 0
                    Value = 0
                    Valid = Today
                    R.Item("NodeValueCurrencey") = GenCurencey
                    For Each P As DataRow In dt.Rows
                        If P.Item("NodeParent").ToString = R.Item("NodeID").ToString Then
                            If P.Item("NodeImageIndex") = 12 Or P.Item("NodeImageIndex") = 15 Then green = green + 1
                            If P.Item("NodeImageIndex") = 13 Or P.Item("NodeImageIndex") = 16 Or P.Item("NodeImageIndex") = 18 Then Red = Red + 1
                            If IsDBNull(P.Item("NodeValueCurrencey")) = False Then Value = Value + CurenceyTransfer(GenCurencey, P.Item("NodeValueCurrencey"), P.Item("NodeValue"))
                            If P.Item("NodeValid") < Valid Then Valid = P.Item("NodeValid")
                        End If
                    Next
                    R.Item("NodeValue") = Value
                    R.Item("NodeValid") = Valid
                    If green > 0 And Red = 0 Then
                        R.Item("NodeImageIndex") = 9
                    ElseIf Red > 0 Then
                        R.Item("NodeImageIndex") = 10
                    Else
                        R.Item("NodeImageIndex") = 11
                    End If
                    Dim no() As TreeNode = Me.TreeView2.Nodes.Find(R.Item("NodeID"), True)
                    no(0).Text = R.Item("NodeText")
                    no(0).ImageIndex = R.Item("NodeImageIndex")
                    no(0).SelectedImageIndex = R.Item("NodeImageIndex")
                    If Not R.Item("NodeText2") Is Nothing Then no(0).ToolTipText = R.Item("NodeText2").ToString
                    Call Validate_changes(R.Item("NodeParent"))

                ElseIf R.Item("NodeType").ToString = "Mech" Then
                    Red = 0
                    green = 0
                    Value = 0
                    Valid = Today
                    R.Item("NodeValueCurrencey") = GenCurencey
                    For Each P As DataRow In dt.Rows
                        If P.Item("NodeParent").ToString = R.Item("NodeID").ToString Then
                            If P.Item("NodeImageIndex") = 9 Then green = green + 1
                            If P.Item("NodeImageIndex") = 10 Or P.Item("NodeImageIndex") = 18 Then Red = Red + 1
                            If IsDBNull(P.Item("NodeValueCurrencey")) = False Then Value = Value + CurenceyTransfer(GenCurencey, P.Item("NodeValueCurrencey"), P.Item("NodeValue"))
                            If P.Item("NodeValid") < Valid Then Valid = P.Item("NodeValid")
                        End If
                    Next
                    R.Item("NodeValue") = Value
                    R.Item("NodeValid") = Valid
                    If green > 0 And Red = 0 Then
                        R.Item("NodeImageIndex") = 3
                    ElseIf Red > 0 Then
                        R.Item("NodeImageIndex") = 4
                    Else
                        R.Item("NodeImageIndex") = 5
                    End If
                    Dim no() As TreeNode = Me.TreeView2.Nodes.Find(R.Item("NodeID"), True)
                    no(0).Text = R.Item("NodeText")
                    no(0).ImageIndex = R.Item("NodeImageIndex")
                    no(0).SelectedImageIndex = R.Item("NodeImageIndex")
                    If Not R.Item("NodeText2") Is Nothing Then no(0).ToolTipText = R.Item("NodeText2").ToString
                    Call Validate_changes(R.Item("NodeParent"))

                ElseIf R.Item("NodeType").ToString = "Ele" Then
                    Red = 0
                    green = 0
                    Value = 0
                    Valid = Today
                    R.Item("NodeValueCurrencey") = GenCurencey
                    For Each P As DataRow In dt.Rows
                        If P.Item("NodeParent").ToString = R.Item("NodeID").ToString Then
                            If P.Item("NodeImageIndex") = 9 Then green = green + 1
                            If P.Item("NodeImageIndex") = 10 Or P.Item("NodeImageIndex") = 18 Then Red = Red + 1
                            If IsDBNull(P.Item("NodeValueCurrencey")) = False Then Value = Value + CurenceyTransfer(GenCurencey, P.Item("NodeValueCurrencey"), P.Item("NodeValue"))
                            If P.Item("NodeValid") < Valid Then Valid = P.Item("NodeValid")
                        End If
                    Next
                    R.Item("NodeValue") = Value
                    R.Item("NodeValid") = Valid
                    If green > 0 And Red = 0 Then
                        R.Item("NodeImageIndex") = 6
                    ElseIf Red > 0 Then
                        R.Item("NodeImageIndex") = 7
                    Else
                        R.Item("NodeImageIndex") = 8
                    End If
                    Dim no() As TreeNode = Me.TreeView2.Nodes.Find(R.Item("NodeID"), True)
                    no(0).Text = R.Item("NodeText")
                    no(0).ImageIndex = R.Item("NodeImageIndex")
                    no(0).SelectedImageIndex = R.Item("NodeImageIndex")
                    If Not R.Item("NodeText2") Is Nothing Then no(0).ToolTipText = R.Item("NodeText2").ToString
                    Call Validate_changes(R.Item("NodeParent"))

                ElseIf R.Item("NodeType").ToString = "Bs" Then
                    Red = 0
                    green = 0
                    Value = 0
                    Valid = Today
                    R.Item("NodeValueCurrencey") = GenCurencey
                    For Each P As DataRow In dt.Rows
                        If P.Item("NodeParent").ToString = R.Item("NodeID").ToString Then
                            If P.Item("NodeImageIndex") = 3 Or P.Item("NodeImageIndex") = 6 Then green = green + 1
                            If P.Item("NodeImageIndex") = 4 Or P.Item("NodeImageIndex") = 7 Or P.Item("NodeImageIndex") = 18 Then Red = Red + 1
                            If IsDBNull(P.Item("NodeValueCurrencey")) = False Then Value = Value + CurenceyTransfer(GenCurencey, P.Item("NodeValueCurrencey"), P.Item("NodeValue"))
                            If P.Item("NodeValid") < Valid Then Valid = P.Item("NodeValid")
                        End If
                    Next
                    R.Item("NodeValue") = Value
                    R.Item("NodeValid") = Valid
                    If green > 0 And Red = 0 Then
                        R.Item("NodeImageIndex") = 0
                    ElseIf Red > 0 Then
                        R.Item("NodeImageIndex") = 1
                    Else
                        R.Item("NodeImageIndex") = 2
                    End If
                    Dim no() As TreeNode = Me.TreeView2.Nodes.Find(R.Item("NodeID"), True)
                    no(0).Text = R.Item("NodeText")
                    no(0).ImageIndex = R.Item("NodeImageIndex")
                    no(0).SelectedImageIndex = R.Item("NodeImageIndex")
                    If Not R.Item("NodeText2") Is Nothing Then no(0).ToolTipText = R.Item("NodeText2").ToString

                End If

            End If
        Next
        Debug.Print("End validate changes: " & Now)
    End Sub
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,607 questions
{count} votes

3 answers

Sort by: Most helpful
  1. neytal 1 Reputation point
    2021-01-20T15:06:31.653+00:00

    Hi thank you for reply,
    so the 'DsUsers.dtUsers' is a DataTable what I use for holding informations for each node in treeview. Every node.name is PrimaryKey column in DataTable NodeID. Than the row holds informations about Icon Tooltip and not the last Value and Date of Validation. This is importat and it is basically that the Sub Validate_Changes do. Only items Price/Hours which are in the 4th level has option to change Validation date or value (this is calculated by amount of Items from another DataTable which I made for Heads of department and Purchase to calculate their Internal performances). Once the Validation Date or Value chages and you call save it should change Validation Date and value in their parents (in table) up to level 0 and on that basis change icons relevant to status of validation.

    I understand that Method is Sub with ByVal variables on calling as Function but with no return. Or? If I break it down to small Subs/Methods it will run faster?

    Is there a tool in Visual basic what I could use to see the bottle neck in my code? Or do I have to do Debug.Print or write line with name of process and NowTime?

    58703-table-info.jpg58763-tree-info.jpg

    0 comments No comments

  2. Xingyu Zhao-MSFT 5,356 Reputation points
    2021-01-21T07:55:33.157+00:00

    Hi @neytal ,
    Thanks for your feedback.
    It will take some time to convert datatable to array, so I modified your 'loadTreviewLibrabryTest' method.

        Public Sub loadTreviewLibrabryTest()  
            Dim new_node As TreeNode  
            For j As Integer = 0 To dt.Rows.Count - 1  
      
                If Convert.ToInt32(dt.Rows(j)("NodeLevel")) > 0 Then  
                    new_node = New TreeNode(dt.Rows(j)("NodeID").ToString)  
                    SetNode(new_node, dt.Rows(j)("NodeID").ToString, dt.Rows(j)("NodeText").ToString)  
                    Dim no() As TreeNode = Me.TreeView2.Nodes.Find(dt.Rows(j)("NodeParent"), True)  
                    no(0).Nodes.Add(new_node)  
                Else  
                    new_node = New TreeNode(dt.Rows(j)("NodeID").ToString)  
                    SetNode(new_node, dt.Rows(j)("NodeID").ToString, dt.Rows(j)("NodeText").ToString)  
                    Me.TreeView2.Nodes.Add(new_node)  
                End If  
      
            Next  
        End Sub  
        Public Sub SetNode(new_node As TreeNode, NodeID As String, NodeText As String,...)  
            new_node.Name = NodeID  
            new_node.Text = NodeText  
            '...  
        End Sub  
    

    For ‘Validate_changes’ method, You need to extract lines like

    ElseIf R.Item("NodeType").ToString = "..." Then ...  
    

    into a method. It will increases the readability of the code.
    Hope it could be helpful.

    Besides, if you need further assistance, please provide more information about 'DsLibSet.dsPriceMirrior'.

    Best Regards,
    Xingyu Zhao
    *
    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Peter Fleischer (former MVP) 19,306 Reputation points
    2021-01-23T11:02:38.903+00:00

    Hi,
    try following demo. For demo purposes you can create demo data: 157500 records in SQL database in 15 minutes.

    TreeNodes will be loaded only for one level in 50 milliseconds. Expanding node the demo code loads nodes in next (expanded) level.

    Imports System.Data.SqlClient  
      
    Public Class Form15  
      
      ''' <summary>  
      ''' simulate designer  
      ''' </summary>  
      ''' <param name="sender"></param>  
      ''' <param name="e"></param>  
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
        Me.Controls.AddRange(New Control() {lblState, TreeView2, pan})  
        pan.Controls.AddRange(New Control() {btnCreate, btnLoad})  
      End Sub  
      
      Private WithEvents btnCreate As New Button With {.Text = "Create demo data", .Width = 100}  
      Private WithEvents btnLoad As New Button With {.Text = "Load data"}  
      Private pan As New FlowLayoutPanel With {.Dock = DockStyle.Top, .Height = 50}  
      Private WithEvents TreeView2 As New TreeView With {.Dock = DockStyle.Fill}  
      Private lblState As New Label With {.Dock = DockStyle.Bottom}  
      
      Private Model As New DataModel(AddressOf setStatusText)  
      
      ''' <summary>  
      ''' load treeview  
      ''' </summary>  
      ''' <param name="sender"></param>  
      ''' <param name="e"></param>  
      Private Async Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click  
        btnLoad.Enabled = False  
        lblState.Text = "Is creating"  
        lblState.Text = Await Task.Factory.StartNew(New Func(Of String)(AddressOf LoadTreeView))  
        btnLoad.Enabled = True  
      End Sub  
      
      Private Function LoadTreeView() As String  
        Dim sw As New Stopwatch  
        sw.Start()  
        Me.TreeView2.Invoke(Sub()  
                              Me.TreeView2.Nodes.Clear()  
                            End Sub)  
        For Each row As DataRow In Model.GetData(0).Rows  
          Dim n = New MyTreeNode(row)  
          Me.TreeView2.Invoke(Sub()  
                                Me.TreeView2.Nodes.Add(n)  
                              End Sub)  
          If Model.GetCount(n.NodeID) > 0 Then Me.TreeView2.Invoke(Sub()  
                                                                     n.Nodes.Add("Dummy")  
                                                                   End Sub)  
        Next  
        Return $"time to load root nodes: {sw.ElapsedMilliseconds} ms"  
      End Function  
      
      ''' <summary>  
      ''' expand node  
      ''' </summary>  
      ''' <param name="sender"></param>  
      ''' <param name="e"></param>  
      Private Sub TreeView2_BeforeExpand(sender As Object, e As TreeViewCancelEventArgs) Handles TreeView2.BeforeExpand  
        Dim sw As New Stopwatch  
        sw.Start()  
        Dim node = CType(e.Node, MyTreeNode)  
        If node.Nodes.Count = 1 And node.Nodes(0).Text = "Dummy" Then node.Nodes.RemoveAt(0)  
        For Each row As DataRow In Model.GetData(node.NodeID).Rows  
          Dim n = New MyTreeNode(row)  
          node.Nodes.Add(n)  
          If Model.GetCount(n.NodeID) > 0 Then n.Nodes.Add("Dummy")  
        Next  
        lblState.Text = $"time to load subnodes: {sw.ElapsedMilliseconds} ms"  
      End Sub  
      
      ''' <summary>  
      ''' create new demo data  
      ''' </summary>  
      ''' <param name="sender"></param>  
      ''' <param name="e"></param>  
      Private Async Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click  
        btnCreate.Enabled = False  
        lblState.Text = "Is creating"  
        lblState.Text = Await Task.Factory.StartNew(New Func(Of String)(AddressOf Model.CreateData))  
        btnCreate.Enabled = True  
      End Sub  
      
      ''' <summary>  
      ''' show state  
      ''' </summary>  
      ''' <param name="msg"></param>  
      Private Sub setStatusText(msg As String)  
        If lblState.InvokeRequired Then  
          lblState.Invoke(Sub()  
                            lblState.Text = msg  
                          End Sub)  
        Else  
          lblState.Text = msg  
        End If  
      End Sub  
      
      ''' <summary>  
      ''' own treenode to hold DataRow   
      ''' </summary>  
      Private Class MyTreeNode  
        Inherits TreeNode  
      
        Public Sub New(drow As DataRow)  
          Me.row = drow  
          Me.Name = drow("NodeID").ToString  
          Me.Text = drow("NodeText").ToString  
          Me.ImageIndex = drow.Field(Of Integer)("NodeImageIndex")  
          Me.SelectedImageIndex = Me.ImageIndex  
          Dim nodeText2 = drow("NodeParent").ToString  
          If Not String.IsNullOrEmpty(nodeText2) Then Me.ToolTipText = nodeText2  
        End Sub  
      
        Private row As DataRow  
      
        Public ReadOnly Property NodeID As Integer  
          Get  
            Return row.Field(Of Integer)("NodeID")  
          End Get  
        End Property  
      
        Public ReadOnly Property NodeParent As Integer  
          Get  
            Return row.Field(Of Integer)("NodeParent")  
          End Get  
        End Property  
      
      End Class  
      
      ''' <summary>  
      ''' Model class to generate demo data and load data  
      ''' </summary>  
      Friend Class DataModel  
      
    #Region " Constructor, fields, Connection"  
      
        Public Sub New(setStatusText As Action(Of String))  
          Me.SetStatusText = setStatusText  
        End Sub  
      
        Private SetStatusText As Action(Of String)  
        Private _cn As SqlConnection = Nothing  
      
        Private ReadOnly Property Cn As SqlConnection  
          Get  
            If Me._cn Is Nothing Then  
              Me._cn = New SqlConnection(My.Settings.cnSQL)  
              Me._cn.Open()  
            End If  
            Return Me._cn  
          End Get  
        End Property  
      
    #End Region  
      
    #Region " Load data"  
      
        Friend Function GetData(parent As Integer) As DataTable  
          SyncLock Me  
            Using da As New SqlDataAdapter("SELECT * FROM [Table1] WHERE NodeParent=@parent", Cn)  
              da.SelectCommand.Parameters.AddWithValue("@parent", parent)  
              Dim dt As New DataTable  
              da.Fill(dt)  
              Return dt  
            End Using  
          End SyncLock  
        End Function  
      
        Friend Function GetCount(parent As Integer) As Integer  
          SyncLock Me  
            Using cmd As New SqlCommand("SELECT Count(ID) FROM [Table1] WHERE NodeParent=@parent", Cn)  
              cmd.Parameters.AddWithValue("@parent", parent)  
              Return CType(cmd.ExecuteScalar, Integer)  
            End Using  
          End SyncLock  
        End Function  
      
    #End Region  
      
    #Region " Generate demo data"  
      
        Friend Function CreateData() As String  
          Dim sw As New Stopwatch()  
          sw.Start()  
          Dim rnd As New Random  
          Try  
            Using cmd As New SqlCommand With {.Connection = Cn}  
              ' delete previous delete previous table in previous versions of SQL server 2016  
              cmd.CommandText = "If OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE IF EXISTS Table1;"  
              cmd.ExecuteNonQuery()  
              ' Create Table with columns  
              ' NodeType  
              ' NodeID - int  
              ' NodeText - string  
              ' NodeImageIndex - int  
              ' NodeText2 - string  
              ' NodeParent - int  
              cmd.CommandText = "CREATE Table [Table1]([ID] Integer Identity, [NodeType] NVARCHAR(10), [NodeID] Integer, [NodeText] NVARCHAR(50), [NodeImageIndex] Integer, [NodeText2]  NVARCHAR(50), [NodeParent] Integer, CONSTRAINT [PK_TabN] PRIMARY KEY ([ID]))"  
              cmd.ExecuteNonQuery()  
              ' Insert values  
              cmd.CommandText = "INSERT INTO Table1([NodeType], [NodeID], [NodeText], [NodeImageIndex], [NodeText2], [NodeParent]) VALUES(@NodeType, @NodeID, @NodeText, @NodeImageIndex, @NodeText2, @NodeParent)"  
              cmd.Parameters.Add("@NodeType", SqlDbType.NVarChar, 10)  
              cmd.Parameters.Add("@NodeID", SqlDbType.Int)  
              cmd.Parameters.Add("@NodeText", SqlDbType.NVarChar, 255)  
              cmd.Parameters.Add("@NodeImageIndex", SqlDbType.Int)  
              cmd.Parameters.Add("@NodeText2", SqlDbType.NVarChar, 255)  
              cmd.Parameters.Add("@NodeParent", SqlDbType.Int)  
              Dim nodeID As Integer = 0  
              For i = 1 To 2500  
                ' Root nodes  
                nodeID += 1  
                Dim nodeIDi = nodeID  
                cmd.Parameters(0).Value = "Bs"    ' NodeType  
                cmd.Parameters(1).Value = nodeIDi  ' NodeID - int  
                cmd.Parameters(2).Value = $"Bs {i}"     ' NodeText - string  
                cmd.Parameters(3).Value = 1       ' NodeImageIndex - int  
                cmd.Parameters(4).Value = $""     ' NodeText2 - string  
                cmd.Parameters(5).Value = 0       ' NodeParent - int  
                cmd.ExecuteNonQuery()  
                ' sub node Mech  
                nodeID += 1  
                Dim nodeIDMech = nodeID  
                cmd.Parameters(0).Value = "Mech"    ' NodeType  
                cmd.Parameters(1).Value = nodeIDMech  ' NodeID - int  
                cmd.Parameters(2).Value = $"Mech {i}"     ' NodeText - string  
                cmd.Parameters(3).Value = 1       ' NodeImageIndex - int  
                cmd.Parameters(4).Value = $""     ' NodeText2 - string  
                cmd.Parameters(5).Value = nodeIDi       ' NodeParent - int  
                cmd.ExecuteNonQuery()  
                For k = 1 To 5  
                  nodeID += 1  
                  Dim nodeIDk = nodeID  
                  cmd.Parameters(0).Value = "Area"    ' NodeType  
                  cmd.Parameters(1).Value = nodeIDk  ' NodeID - int  
                  cmd.Parameters(2).Value = $"Area {i} - {nodeIDMech} - {k}"     ' NodeText - string  
                  cmd.Parameters(3).Value = 1       ' NodeImageIndex - int  
                  cmd.Parameters(4).Value = $""     ' NodeText2 - string  
                  cmd.Parameters(5).Value = nodeIDMech       ' NodeParent - int  
                  cmd.ExecuteNonQuery()  
                  SetStatusText($"Area {i} - {k}")  
                  For l = 1 To 5  
                    nodeID += 1  
                    If rnd.NextDouble > 0.5 Then  
                      cmd.Parameters(0).Value = "Price"    ' NodeType  
                      cmd.Parameters(2).Value = $"Price {i} -{k} - {l}"     ' NodeText - string  
                    Else  
                      cmd.Parameters(0).Value = "Construction"    ' NodeType  
                      cmd.Parameters(2).Value = $"Construction {i} -{k} - {l}"     ' NodeText - string  
                    End If  
                    cmd.Parameters(1).Value = nodeID  ' NodeID - int  
                    cmd.Parameters(3).Value = 1       ' NodeImageIndex - int  
                    cmd.Parameters(4).Value = $""     ' NodeText2 - string  
                    cmd.Parameters(5).Value = nodeIDk       ' NodeParent - int  
                    cmd.ExecuteNonQuery()  
                  Next  
                Next  
                ' sub node Ele  
                nodeID += 1  
                Dim nodeIDEle = nodeID  
                cmd.Parameters(0).Value = "Ele"    ' NodeType  
                cmd.Parameters(1).Value = nodeIDEle  ' NodeID - int  
                cmd.Parameters(2).Value = $"Ele {i}"     ' NodeText - string  
                cmd.Parameters(3).Value = 1       ' NodeImageIndex - int  
                cmd.Parameters(4).Value = $""     ' NodeText2 - string  
                cmd.Parameters(5).Value = nodeIDi       ' NodeParent - int  
                cmd.ExecuteNonQuery()  
                For k = 1 To 5  
                  nodeID += 1  
                  Dim nodeIDk = nodeID  
                  cmd.Parameters(0).Value = "Area"    ' NodeType  
                  cmd.Parameters(1).Value = nodeIDk  ' NodeID - int  
                  cmd.Parameters(2).Value = $"Area {i} - {nodeIDEle} - {k}"     ' NodeText - string  
                  cmd.Parameters(3).Value = 1       ' NodeImageIndex - int  
                  cmd.Parameters(4).Value = $""     ' NodeText2 - string  
                  cmd.Parameters(5).Value = nodeIDEle       ' NodeParent - int  
                  cmd.ExecuteNonQuery()  
                  SetStatusText($"Area {i} - {k}")  
                  For l = 1 To 5  
                    nodeID += 1  
                    If rnd.NextDouble > 0.5 Then  
                      cmd.Parameters(0).Value = "Price"    ' NodeType  
                      cmd.Parameters(2).Value = $"Price {i} -{k} - {l}"     ' NodeText - string  
                    Else  
                      cmd.Parameters(0).Value = "Construction"    ' NodeType  
                      cmd.Parameters(2).Value = $"Construction {i} -{k} - {l}"     ' NodeText - string  
                    End If  
                    cmd.Parameters(1).Value = nodeID  ' NodeID - int  
                    cmd.Parameters(3).Value = 1       ' NodeImageIndex - int  
                    cmd.Parameters(4).Value = $""     ' NodeText2 - string  
                    cmd.Parameters(5).Value = nodeIDk       ' NodeParent - int  
                    cmd.ExecuteNonQuery()  
                  Next  
                Next  
              Next  
              cmd.CommandText = "SELECT Count(ID) From [Table1]"  
              Dim count = CType(cmd.ExecuteScalar, Integer)  
              Return $"Creating complete, {count} records created, {sw.Elapsed.TotalSeconds} s"  
            End Using  
          Catch ex As Exception  
            Return $"Error {ex.Message}"  
          End Try  
        End Function  
      
    #End Region  
      
      End Class  
      
    End Class  
    

    Result:

    59725-x.gif

    0 comments No comments