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:
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(57.599999999999994, 53%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN%3C/text%3E%3C/svg%3E)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(233.6, 28.999999999999996%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EXZ%3C/text%3E%3C/svg%3E)
