Блог

У меня есть один вид дерева, который показывает мне записи 1 таблицы в SQL Server, пока все хорошо прикреплено к дереву,

Запрос, который я выполняю, чтобы показать мне данные из sql в TREEVIEW, выглядит следующим образом:

 Dim sqlConsulta As String = "SELECT DESCRIPCION AS nodeText,'DEPA'   CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeKey,'' AS nodeParentKey "  
"FROM DEPARTAMENTO UNION ALL SELECT DESCRIPCION AS nodeText,'PROV'   CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,'DEPA'   CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey "  
"FROM PROVINCIA UNION ALL SELECT DESCRIPCION AS nodeText,'DIST'   CAST(IDUBIGEO AS VARCHAR) AS nodeKey,'PROV'   CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo "
 

У меня есть следующий код на TreeView1_AfterSelect случай, если он показывает мне только то, что я выбираю из последнего узла, но не из других, и, в свою очередь, я хотел бы указать идентификатор РАЙОНА

 Private Sub TreeView1_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect
    'obtem o item selecionado
    Dim itemSelecionado As String = TreeView1.SelectedNode.ToString
    'remove a string TreeNode
    itemSelecionado = itemSelecionado.Replace("TreeNode: ", "")

    'verifica se o item é nulo
    If (e.Node.Parent IsNot Nothing) Then
        'verifica o tipo do no
        If (e.Node.Parent.GetType() Is GetType(TreeNode)) Then
            If e.Node.Parent.Text = nomeArquivo Then
                'mostra o nome da tabela e da coluna selecionada
                txtdistrito.Text = (e.Node.Parent.Text   "."   itemSelecionado)
            Else
                'mostra so o nome da tabela
                txtdistrito.Text = (itemSelecionado)
            End If
        End If
    End If
End Sub
 

КОД ДЛЯ ЗАПОЛНЕНИЯ ДРЕВОВИДНОГО ПРЕДСТАВЛЕНИЯ

  Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conexaoSQLServer As SqlConnection = Nothing
        Dim cmd As SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        DBSQLServer = "prueba"

        Dim strCon As String = "Data Source = DESKTOP-2IM88STSQLEXPRESS; Initial Catalog = " amp; DBSQLServer amp; "; Integrated Security = True"

        'define a consulta para obter as tabelas e suas colunas
        Dim sqlConsulta As String = "SELECT DESCRIPCION AS nodeText,'DEPA'   CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeKey,'' AS nodeParentKey "  
"FROM DEPARTAMENTO UNION ALL SELECT DESCRIPCION AS nodeText,'PROV'   CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,'DEPA'   CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey "  
"FROM PROVINCIA UNION ALL SELECT DESCRIPCION AS nodeText,'DIST'   CAST(IDUBIGEO AS VARCHAR) AS nodeKey,'PROV'   CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo "


        Try
            'define e abre a conexão com o SQL Server
            conexaoSQLServer = New SqlConnection(strCon)
            conexaoSQLServer.Open()

            'atribui o comando usado na conexão
            cmd = New SqlCommand(sqlConsulta, conexaoSQLServer)
            da.SelectCommand = cmd

            'preenche o dataset
            da.Fill(ds, "DATOS_SISTEMAS")

            'Helper dictionaries
            Dim nodes As New Dictionary(Of String, TreeNode) 'Holds the nodes based on their key values
            Dim nodeParents As New Dictionary(Of String, String) 'Holds the parent keys of child nodes

            'Create nodes from data
            For Each row As DataRow In ds.Tables("DATOS_SISTEMAS").Rows
                Dim nodeText As String = row.Field(Of String)("nodeText") 
                Dim nodeKey As String = row.Field(Of String)("nodeKey")
                Dim nodeParentKey As String = row.Field(Of String)("nodeParentKey")

                nodes.Add(nodeKey, New TreeNode(nodeText))

                If Not String.IsNullOrEmpty(nodeParentKey) Then
                    nodeParents.Add(nodeKey, nodeParentKey)
                End If
            Next

            'Add nodes to treeview (and resolve parents)
            For Each kvp In nodes
                Dim node As TreeNode = kvp.Value
                Dim nodeKeys As String = kvp.Key
                Dim nodeParentKeys As String = Nothing

                If nodeParents.TryGetValue(nodeKeys, nodeParentKeys) Then
                    'Child node
                    Dim parentNode As TreeNode = nodes(nodeParentKeys)
                    parentNode.Nodes.Add(node)
                Else
                    'Root node
                    TreeView1.Nodes.Add(node)

                End If
            Next
        Catch ex As Exception
            MessageBox.Show("error when performing this operation:  " amp; ex.Message)
            Exit Sub
        Finally
            'libera os recursos da conexão usada
            conexaoSQLServer.Close()
            conexaoSQLServer.Dispose()
            conexaoSQLServer = Nothing
        End Try

    End Sub 

Во-первых, я бы предложил вам установить Tag свойства ваших узлов с соответствующими nodeKey значениями во время заполнения древовидного представления.

 Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  Dim conexaoSQLServer As SqlConnection = Nothing
  Dim cmd As SqlCommand
  Dim da As New SqlDataAdapter
  Dim ds As New DataSet
  DBSQLServer = "prueba"

  Dim strCon As String = "Data Source = DESKTOP-2IM88STSQLEXPRESS; Initial Catalog = " amp; DBSQLServer amp; "; Integrated Security = True"

  'define a consulta para obter as tabelas e suas colunas
  Dim sqlConsulta As String = "SELECT DESCRIPCION AS nodeText,'DEPA'   CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeKey,'' AS nodeParentKey "  
"FROM DEPARTAMENTO UNION ALL SELECT DESCRIPCION AS nodeText,'PROV'   CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,'DEPA'   CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey "  
"FROM PROVINCIA UNION ALL SELECT DESCRIPCION AS nodeText,'DIST'   CAST(IDUBIGEO AS VARCHAR) AS nodeKey,'PROV'   CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo "


  Try
    'define e abre a conexão com o SQL Server
    conexaoSQLServer = New SqlConnection(strCon)
    conexaoSQLServer.Open()

    'atribui o comando usado na conexão
    cmd = New SqlCommand(sqlConsulta, conexaoSQLServer)
    da.SelectCommand = cmd

    'preenche o dataset
    da.Fill(ds, "DATOS_SISTEMAS")

    'Helper dictionaries
    Dim nodes As New Dictionary(Of String, TreeNode) 'Holds the nodes based on their key values
    Dim nodeParents As New Dictionary(Of String, String) 'Holds the parent keys of child nodes

    'Create nodes from data
    For Each row As DataRow In ds.Tables("DATOS_SISTEMAS").Rows
      Dim nodeText As String = row.Field(Of String)("nodeText")
      Dim nodeKey As String = row.Field(Of String)("nodeKey")
      Dim nodeParentKey As String = row.Field(Of String)("nodeParentKey")

      ''UPDATED CODE START
      Dim node As New TreeNode(nodeText)
      node.Tag = nodeKey
      nodes.Add(nodeKey, node)
      ''UPDATED CODE END

      If Not String.IsNullOrEmpty(nodeParentKey) Then
        nodeParents.Add(nodeKey, nodeParentKey)
      End If
    Next

    'Add nodes to treeview (and resolve parents)
    For Each kvp In nodes
      Dim node As TreeNode = kvp.Value
      Dim nodeKeys As String = kvp.Key
      Dim nodeParentKeys As String = Nothing

      If nodeParents.TryGetValue(nodeKeys, nodeParentKeys) Then
        'Child node
        Dim parentNode As TreeNode = nodes(nodeParentKeys)
        parentNode.Nodes.Add(node)
      Else
        'Root node
        TreeView1.Nodes.Add(node)

      End If
    Next
  Catch ex As Exception
    MessageBox.Show("error when performing this operation:  " amp; ex.Message)
    Exit Sub
  Finally
    'libera os recursos da conexão usada
    conexaoSQLServer.Close()
    conexaoSQLServer.Dispose()
    conexaoSQLServer = Nothing
  End Try

End Sub
 

Далее, в вашем AfterSelect случае, я бы либо придерживался, TreeView1.SelectedNode либо e.Node . Я думаю, они оба должны представлять один и тот же узел. Но доступ e.Node , на мой взгляд, несколько «чище».

Вы не указали имена всех четырех текстовых полей, поэтому я их придумал. Вероятно, вам необходимо обновить код, чтобы в нем использовались правильные имена текстовых полей.

 Private Sub TreeView1_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect
  Dim node As TreeNode = e.Node 'TreeView1.SelectedNode
  Dim nodeKey As String = TryCast(node.Tag, String)

  'A nodeKey value of a district node will look like "DIST123" if the corresponding IDUBIGEO value is 123.
  'So it should be checked if the nodeKey value starts with "DIST".

  If (nodeKey IsNot Nothing AndAlso nodeKey.StartsWith("DIST")) Then
    'It's a district node.
    'So it is also known that its parent node is its province node and its parent's parent node is its department node.

    txtdistrito.Text = node.Text
    txtprovincia.Text = node.Parent.Text
    txtdepartamento.Text = node.Parent.Parent.Text

    'The district code (the IDUBIGEO value) is also available in the nodeKey value after the first four characters ("DIST").

    txtdistritocode.Text = nodeKey.Substring(4) 'Index positions 0-3 contain "DIST", index positions 4 and higher contain the district code (the IDUBIGEO value).
  End If
End Sub
 

Можно было бы заставить его работать без использования свойства узла Tag , но AfterSelect в этом случае проверки в случае, если вы действительно нажмете на узел района, будут несколько сложнее. Кроме того, вам потребуется отдельный SQL-запрос (или другой механизм хранения/кэширования), чтобы найти соответствующее значение IDUBIGEO, поскольку оно не будет храниться в/с выбранным узлом во время заполнения древовидного представления.

Когда вы создаете каждый древовидный код, сохраните nodeKey в древовидном коде.Свойство тега

 Dim node as New TreeNode(data.nodeText) With { .Tag = data.nodeKey }
 

Затем в обработчике после выбора вы можете сделать что-то вроде этого

 tDepartment.Text = String.Empty
tProvince.Text = String.Empty
tUbigeo.Text = String.Empty
tDistrictCode.Text = String.Empty

Dim nodeKey As String = DirectCast(e.Node.Tag, String);

If nodeKey.StartsWith("DEP") Then
    tDepartment.Text = e.Node.Text
End If
If nodeKey.StartsWith("PRO") Then            
    tProvince.Text = e.Node.Text
    tDepartment.Text = e.Node.Parent.Text
End If
If nodeKey.StartsWith("DIS") Then            
    tUbigeo.Text = e.Node.Text;
    tDistrictCode.Text = nodeKey;
    tProvince.Text = e.Node.Parent.Text;
    tDepartment.Text = e.Node.Parent.Parent.Text;
End If