Diskuze: Fundamental work with Datatable and Treeview in VB.NET

Naučíme tě pracovat na home-office.

Zjistit více...

Aktivity

Josef Brzobohaty

Člen

Josef Brzobohaty:18.1.2021 17:35

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

Zkusil jsem: Loading Datatable to Array and work with it, but it is still slow

Chci docílit: Smoot fast work with software

Odpovědět

18.1.2021 17:35

zelvicek

Člen

zelvicek:19.1.2021 6:59

Hi,
why English? Can't you express yourself in Czech?

So, you have realised you have a bottleneck somewhere. Did you find out where it is? Is it the data fetching, treeview filling, tree displaying?

I must say, loading all tables/DB into in-proc memory is usually bad idea - imagine the table contains 2bil rows. Also, are you able to provide/display all the fetched rows to the user at once?
You can load the data on demand - as user scroll your the treeview and expand the nodes.

Further, the DataSet do quite much work around DataReader so if you no need that for another purpose - use just DR for data loading.

Nahoru Odpovědět

19.1.2021 6:59

Josef Brzobohaty

Člen

Josef Brzobohaty:20.1.2021 15:35

Dobry den,
omlouvam se psal jsem primarne na VBforum a napadlo me to dat I sem ale jelikoz jsem na dovolene a jens se snazim zjistit jak postupovat po navratu tak uz jsem to nechtal psat znova v cestine
Je pravda ze jsem nad tim premyslel nechat pouze level 0 a pri dvojkliku ho expadnovat. Problem ale neni (ted) u nacitani stromu ale kdyz chci zmenit hodnoty a na zaklade toho udelat update ikon/Tooltip ve strome. Pracuji se softwary Process Dessigner a Process Simulate ktere maji stromy s o mnoho vice polozkami a vse bezi rychla. Proro premyslim jak to udelat tak aby to bezelo stejne rychle.

Problematicke misto jsem stale nezjistil, dela se to pres Debug.Print a casovym udajem nebo je nejaky sofistikovanejsi postup jak analyzovat co jak dlouho trva?

Nechapu tu posledni vetu. Datareader je to co pouzivam pro nacitani tabulky z XML do Datasetu. Nebo?

DEkuji za odpoved

Nahoru Odpovědět

20.1.2021 15:35

Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 3 zpráv z 3.