VB.net List of Checked TreeView items

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

In my VB.net windows form I have the following TreeView that show Tables and columns.
What VB.net code would I use to gather a list of tables that are checked and then a list of columns
that are checked for that table?

Thanks

1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
How do you populate your treeview?

-saige-
Murray BrownHead Developer

Author

Commented:
    Private Function Populate_TreeView_From_SQL() As Boolean
        Try


            Dim myImageList As New ImageList()
            myImageList.Images.Add(My.Resources.Database) '0
            myImageList.Images.Add(My.Resources.Table) '1
            myImageList.Images.Add(My.Resources.View) '2
            myImageList.Images.Add(My.Resources.Column) '3
            myImageList.Images.Add(My.Resources.Column_Key) '4
            myImageList.Images.Add(My.Resources.Table_Selected) '5
            myImageList.Images.Add(My.Resources.Column_Selected) '6
            myImageList.Images.Add(My.Resources.View_Selected) '7
            myImageList.Images.Add(My.Resources.TableGrey) '8
            myImageList.Images.Add(My.Resources.ViewGrey) '9
            myImageList.Images.Add(My.Resources.ColumnGrey) '10
            myImageList.Images.Add(My.Resources.Column_KeyGrey) '11
            myImageList.Images.Add(My.Resources.Table_SelectedGrey) '12
            myImageList.Images.Add(My.Resources.Column_SelectedGrey) '13
            myImageList.Images.Add(My.Resources.View_SelectedGrey) '14
            myImageList.Images.Add(My.Resources.J_Yellow)

            Dim SQLExpression As String
            SQLExpression = "SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.columns Order By TABLE_NAME, ordinal_position"

            Dim Rootnode As TreeNode = Nothing
            Dim Mainnode As TreeNode = Nothing
            Dim Childnode As TreeNode = Nothing

            Dim MainName As String = String.Empty

            Dim xTable As String
            Dim xColumn As String
            Dim xTableColumn As String

            Dim cn As New SqlConnection(oConnectionString)

            Dim adp As New SqlDataAdapter(SQLExpression, cn)

            Dim ds As New DataSet

            adp.Fill(ds, "SystemData")

            Me.TreeView_From.Nodes.Clear()

            Me.TreeView_From.ImageList = myImageList


            'Note the image list was loaded in above...
            Rootnode = Me.TreeView_From.Nodes.Add(key:="Root", text:="SQL Database",
                                            imageIndex:=0, selectedImageIndex:=1)

            Dim oIndex As Integer = -1

            For Each row As DataRow In ds.Tables("SystemData").Rows

                If sTABLES <> "" Then
                    Dim arrSplitAllowTables As Object = Split(sTABLES, ",")
                    Dim blnAllowTable As Boolean = False
                    For j As Integer = 0 To UBound(arrSplitAllowTables)
                        If row(0).ToString = arrSplitAllowTables(j) Then
                            blnAllowTable = True
                        End If
                    Next
                    If blnAllowTable = False Then GoTo JumpOver
                End If

                oIndex = oIndex + 1

                If MainName <> row(0).ToString Then

                    Dim oText = row(0).ToString

                    MainName = row(0).ToString

                    xTable = "[" & MainName & "]"
                    xColumn = "[" & row(1) & "]"


                    If oIs_Table_or_View(MainName) = "Table" Then


                        Mainnode = Rootnode.Nodes.Add(key:="Table", text:=oText,
                                                              imageIndex:=1, selectedImageIndex:=5)


                    Else

                        Mainnode = Rootnode.Nodes.Add(key:="Table", text:=oText,
imageIndex:=2, selectedImageIndex:=7)

                    End If

                End If

                'COLUMNS


                Childnode = Mainnode.Nodes.Add(key:="Column", text:=row(1).ToString,
imageIndex:=3, selectedImageIndex:=6)



                xTableColumn = "[" & MainName & "].[" & row(1) & "]"


JumpOver:
            Next

            ' intNumberItems = oIndex + 1

            Me.TreeView_From.Nodes(0).EnsureVisible()


            Me.TreeView_From.CollapseAll()
            For Each n As TreeNode In Me.TreeView_From.Nodes
                n.Expand()
            Next


            Me.TreeView_From.Scrollable = True


            ds.Dispose()
            ds = Nothing
            adp.Dispose()
            adp = Nothing
            cn.Close()
            cn.Dispose()
            cn = Nothing

            Populate_TreeView_From_SQL = True

        Catch ex As Exception
            Populate_TreeView_From_SQL = False
            MsgBox("There was an error loading the Tree View from SQL. " & ex.Message & "003")
        Finally
            'cn = Nothing
        End Try
    End Function

Open in new window

Commented:
You could do something like this:

Form1.vb -
Imports System.Data.SqlClient

Public Class Form1
    Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
        PopulateTreeView()
    End Sub

    Private Sub PopulateTreeView()
        Dim query As String = "SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.columns ORDER BY TABLE_NAME, ordinal_position"
        Dim ds As New DataSet()
        Try
            Using connection As New SqlConnection(My.Settings.EE_Q29143556_DB)
                Using adapter As New SqlDataAdapter(query, connection)
                    adapter.Fill(ds, "SystemData")
                End Using
            End Using

            TreeView1.Nodes.Clear()
            Dim root As TreeNode = TreeView1.Nodes.Add(key:="Root", text:="SQL Database")
            For Each row In ds.Tables("SystemData").Rows.Cast(Of DataRow).GroupBy(Function(r) r("TABLE_NAME"))
                Dim table As TreeNode = root.Nodes.Add(key:="Table", text:=row.First()("TABLE_NAME"))
                For Each column In row.Select(Function(r) r)
                    table.Nodes.Add(key:="Column", text:=column("COLUMN_NAME"))
                Next
            Next
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub

    Private Sub OnClick(sender As Object, e As EventArgs) Handles Button1.Click
        Dim selected = GetChildNodes(TreeView1.Nodes).Where(Function(n) n.Checked).GroupBy(Function(n) n.Parent.Text)
        MessageBox.Show(String.Join($"{Environment.NewLine}{Environment.NewLine}", selected.Where(Function(g) g.First().Parent.Name.Equals("Table")).Select(Function(g) $"TABLE: {g.Key}{Environment.NewLine}Columns:{Environment.NewLine}{String.Join(Environment.NewLine, g.Select(Function(c) c.Text))}")))
    End Sub

    Private Iterator Function GetChildNodes(tree As TreeNodeCollection) As IEnumerable(Of TreeNode)
        For Each node In tree.OfType(Of TreeNode)
            Yield node
            For Each child In GetChildNodes(node.Nodes)
                Yield child
            Next
        Next
    End Function
End Class

Open in new window

Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.TreeView1 = New System.Windows.Forms.TreeView()
        Me.SplitContainer1 = New System.Windows.Forms.SplitContainer()
        Me.Button1 = New System.Windows.Forms.Button()
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SplitContainer1.Panel1.SuspendLayout()
        Me.SplitContainer1.Panel2.SuspendLayout()
        Me.SplitContainer1.SuspendLayout()
        Me.SuspendLayout()
        '
        'TreeView1
        '
        Me.TreeView1.CheckBoxes = True
        Me.TreeView1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.TreeView1.Location = New System.Drawing.Point(0, 0)
        Me.TreeView1.Name = "TreeView1"
        Me.TreeView1.Size = New System.Drawing.Size(284, 375)
        Me.TreeView1.TabIndex = 0
        '
        'SplitContainer1
        '
        Me.SplitContainer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.SplitContainer1.Location = New System.Drawing.Point(0, 0)
        Me.SplitContainer1.Name = "SplitContainer1"
        Me.SplitContainer1.Orientation = System.Windows.Forms.Orientation.Horizontal
        '
        'SplitContainer1.Panel1
        '
        Me.SplitContainer1.Panel1.Controls.Add(Me.TreeView1)
        '
        'SplitContainer1.Panel2
        '
        Me.SplitContainer1.Panel2.Controls.Add(Me.Button1)
        Me.SplitContainer1.Size = New System.Drawing.Size(284, 411)
        Me.SplitContainer1.SplitterDistance = 375
        Me.SplitContainer1.TabIndex = 1
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(151, 4)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(127, 23)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Get Checked Items"
        Me.Button1.UseVisualStyleBackColor = True
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(284, 411)
        Me.Controls.Add(Me.SplitContainer1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.SplitContainer1.Panel1.ResumeLayout(False)
        Me.SplitContainer1.Panel2.ResumeLayout(False)
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.SplitContainer1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

    Friend WithEvents TreeView1 As TreeView
    Friend WithEvents SplitContainer1 As SplitContainer
    Friend WithEvents Button1 As Button
End Class

Open in new window

Which produces the following output -Capture.PNG-saige-
Murray BrownHead Developer

Author

Commented:
Thank you very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial