Drag & drop, Treeview, save changes in SQL server database

Imports System.Data.SqlClient


Public Class Form3
    Inherits System.Windows.Forms.Form

    Private con As New SqlConnection()
    Private WithEvents cmdSelect As New SqlCommand()
    Private WithEvents cmdUpdate As New SqlCommand()
    Private WithEvents cmdInsert As New SqlCommand()
    Private WithEvents cmdDelete As New SqlCommand()


    Private WithEvents da As New SqlDataAdapter()
    Private WithEvents ds As New DataSet()
    Private WithEvents cb As New SqlCommandBuilder(da)
    Dim CTree As New CTreeView()
    Dim m_tnSource As TreeNode

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        con.ConnectionString = "Data Source=S_A-PC\SQLEXPRESS1;Initial Catalog=AdventureWorks;Integrated Security=True"
        con.Open()

        Dim dg As New DataGridView
        da.SelectCommand = cmdSelect
        da.SelectCommand.CommandText = "SELECT * FROM CostC223"
        da.SelectCommand.Connection = con

        da.Fill(ds, "dtCosts1")
        Me.Grid1.SetDataBinding(ds, "dtCosts1")
        ''''''''''''''''''''''''''''''''''''''''
        da.UpdateCommand = cb.GetUpdateCommand()
        da.InsertCommand = cb.GetInsertCommand()
        da.DeleteCommand = cb.GetDeleteCommand()
        ''''''''''''''''''''''''''''''''''''''''
        Call Rootnode()

    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd.Click
        da.Update(ds.Tables("dtCosts1"))
        con.Close()
    End Sub

    Private Sub Rootnode()
        TreeView1.Nodes.Clear()
        ds.Relations.Add("Parenttochild", ds.Tables("dtCosts1").Columns("ID"), ds.Tables("dtCosts1").Columns("Reports"), False)
        LoadTreeView(ds)
        TreeView1.CollapseAll()

    End Sub


    Private Sub LoadTreeView(ByVal ds As DataSet)
        Dim oTreeView As TreeView = New TreeView()
        Dim oDataRow As DataRow
        oTreeView = Me.TreeView1

        For Each oDataRow In ds.Tables("dtCosts1").Rows

            If oDataRow("Reports") = -1 Then
                Dim oNode As New System.Windows.Forms.TreeNode
                oNode.Tag() = oDataRow("ID")
                oNode.Text = oDataRow("ID") & (" " + oDataRow("TCID_name").ToString())
                oTreeView.Nodes.Add(oNode)


                RecursivelyLoadTree(oDataRow, oNode)
            End If
        Next oDataRow
        Controls.Add(oTreeView)

    End Sub

    Private Sub RecursivelyLoadTree(ByVal oDataRow As DataRow, _
   ByRef oNode As TreeNode)
        Dim oChildRow As DataRow

        For Each oChildRow In oDataRow.GetChildRows("Parenttochild")

            Dim oChildNode As New TreeNode()
            oChildNode.Tag = oChildRow("ID")
            oChildNode.Text = oChildRow("ID") & (" " + oChildRow("TCID_name").ToString())
            oNode.Nodes.Add(oChildNode)

            RecursivelyLoadTree(oChildRow, oChildNode)
        Next oChildRow


    End Sub


    Public Sub TreeView1_ItemDrag(ByVal sender As System.Object, _
        ByVal e As System.Windows.Forms.ItemDragEventArgs) _
        Handles TreeView1.ItemDrag

        m_tnSource = CType(e.Item, TreeNode)
        DoDragDrop(e.Item, DragDropEffects.Move)


    End Sub

    Public Sub TreeView1_DragEnter(ByVal sender As System.Object, _
        ByVal e As System.Windows.Forms.DragEventArgs) _
        Handles TreeView1.DragEnter


        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
            True) Then

            e.Effect = DragDropEffects.Move
        Else

            e.Effect = DragDropEffects.None
        End If

    End Sub

    Public Sub TreeView1_DragOver(ByVal sender As System.Object, _
         ByVal e As System.Windows.Forms.DragEventArgs) _
        Handles TreeView1.DragOver


        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", _
               True) = False Then Exit Sub


        Dim selectedTreeview As TreeView = CType(sender, TreeView)


        Dim pt As Point = _
            CType(sender, TreeView).PointToClient(New Point(e.X, e.Y))
        Dim targetNode As TreeNode = selectedTreeview.GetNodeAt(pt)


        If Not (selectedTreeview.SelectedNode Is targetNode) Then

            selectedTreeview.SelectedNode = targetNode


            Dim dropNode As TreeNode = _
                CType(e.Data.GetData("System.Windows.Forms.TreeNode"),  _
                TreeNode)

            Do Until targetNode Is Nothing
                If targetNode Is dropNode Then
                    e.Effect = DragDropEffects.None
                    Exit Sub
                End If
                targetNode = targetNode.Parent
            Loop
        End If


        e.Effect = DragDropEffects.Move


    End Sub

    Public Sub TreeView1_DragDrop(ByVal sender As System.Object, _
       ByVal e As System.Windows.Forms.DragEventArgs) _
       Handles TreeView1.DragDrop

        Dim pt As Point
        Dim tnDestination As TreeNode
        Dim tnNew As TreeNode
        Dim tnSourceParent As TreeNode

        If e.Data.GetDataPresent("System.Windows.Forms.TreeNode", True) Then


            pt = TreeView1.PointToClient(New Point(e.X, e.Y))
            tnDestination = TreeView1.GetNodeAt(pt)

            If CTree.IsDropAllowed(m_tnSource, tnDestination) = True Then
                tnSourceParent = m_tnSource.Parent

                tnNew = CType(e.Data.GetData("System.Windows.Forms.TreeNode"), TreeNode)
                tnDestination.Nodes.Add(CType(tnNew.Clone, TreeNode))
                tnDestination.ExpandAll()
                tnNew.Remove()

            Else
                
                MsgBox("The dragged item cannot be dropped here because" & _
                       "doing so will create a circular reference.", _
                       MsgBoxStyle.Exclamation)
            End If

        End If

    End Sub
End Class

Select allOpen in new window