Save TreeView data to a DB and be able to read it back.

Here is the Class that does the Load and Save: (see below it to see the call to it)

_______________________________________________________________________

Option Strict On

Public Class TreeDataFunction

#Region "Structures"
    <Serializable()> Public Structure TreeViewData

        Public Nodes() As TreeNodeData
        Public Sub New(ByVal treeview As TreeView)
            If treeview.Nodes.Count = 0 Then Exit Sub
            ReDim Nodes(treeview.Nodes.Count - 1)
            For i As Integer = 0 To treeview.Nodes.Count - 1
                Nodes(i) = New TreeNodeData(treeview.Nodes(i))
            Next
        End Sub
        Public Sub PopulateTree(ByVal treeview As TreeView)
            If Me.Nodes Is Nothing OrElse Me.Nodes.Length = 0 Then Exit Sub
            treeview.BeginUpdate()
            For i As Integer = 0 To Me.Nodes.Length - 1
                treeview.Nodes.Add(Me.Nodes(i).ToTreeNode)
            Next
            treeview.EndUpdate()
        End Sub
    End Structure
    <Serializable()> Public Structure TreeNodeData
        Public Text As String
        Public ImageIndex As Integer
        Public SelectedImageIndex As Integer
        Public Checked As Boolean
        Public Expanded As Boolean
        Public Tag As Object
        Public Nodes() As TreeNodeData
        Public Sub New(ByVal node As TreeNode)
            Me.Text = node.Text
            Me.ImageIndex = node.ImageIndex
            Me.SelectedImageIndex = node.SelectedImageIndex
            Me.Checked = node.Checked
            Me.Expanded = node.IsExpanded
            If (Not node.Tag Is Nothing) AndAlso node.Tag.GetType.IsSerializable Then Me.Tag = node.Tag
            If node.Nodes.Count = 0 Then Exit Sub
            ReDim Nodes(node.Nodes.Count - 1)
            For i As Integer = 0 To node.Nodes.Count - 1
                Nodes(i) = New TreeNodeData(node.Nodes(i))
            Next
        End Sub
        Public Function ToTreeNode() As TreeNode
            ToTreeNode = New TreeNode(Me.Text, Me.ImageIndex, Me.SelectedImageIndex)
            ToTreeNode.Checked = Me.Checked
            ToTreeNode.Tag = Me.Tag
            If Me.Expanded Then ToTreeNode.Expand()
            If Me.Nodes Is Nothing OrElse Me.Nodes.Length = 0 Then Exit Function
            For i As Integer = 0 To Me.Nodes.Length - 1
                ToTreeNode.Nodes.Add(Me.Nodes(i).ToTreeNode)
            Next
        End Function
    End Structure
#End Region

#Region "Public"
    Public Shared Sub LoadTreeViewData(ByVal treeView As TreeView, ByVal path As String)
        Dim ser As New System.Xml.Serialization.XmlSerializer(GetType(TreeViewData))
        Dim file As New System.IO.FileStream(path, IO.FileMode.Open)
        Dim reader As New System.Xml.XmlTextReader(file)
        Dim treeData As TreeViewData = CType(ser.Deserialize(reader), TreeViewData)
        treeData.PopulateTree(treeView)
        reader.Close()
        file.Close()
        file = Nothing
    End Sub
    Public Shared Sub SaveTreeViewData(ByVal treeView As TreeView, ByVal path As String)
        Dim ser As New System.Xml.Serialization.XmlSerializer(GetType(TreeViewData))
        Dim file As New System.IO.FileStream(path, IO.FileMode.Create)
        Dim writer As New System.Xml.XmlTextWriter(file, Nothing)
        ser.Serialize(writer, New TreeViewData(treeView))
        writer.Close()
        file.Close()
        file = Nothing
    End Sub
#End Region
End Class
____________________________________________________________________________________



When a button is clicked this is what currently is called:

        Dim txt As String, txtfinal As String
        txt = Now
        txt = Replace(txt, ":", "")
        txt = Replace(txt, "/", "")
        txt = Replace(txt, " ", "")
        txtfinal = txt
        TreeDataFunction.SaveTreeViewData(TreeView1, Application.StartupPath & "\TVD" & txtfinal & ".xml")
        MsgBox("Items have been saved to: " & vbCrLf & vbCrLf & Application.StartupPath & "\TVD" & txtfinal & ".xml", MsgBoxStyle.Information, "Categories Saved")

_____________________________________________________________________________________

Here is what I have:

Public Class TreeBuilder

  Private _hashTree As New Hashtable

  Private Sub BuildTree(ByVal tree As TreeView, ByVal table As DataTable, ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)

    For Each row As DataRow In table.Rows
      Dim relationValue As String = row(relationColumn).ToString
      Dim keyValue As String = row(keyColumn).ToString
      Dim node As TreeNode = New TreeNode(row(displayColumn).ToString)

      Dim findParent As TreeNode = CType(_hashTree(relationValue), TreeNode)
      If Not (findParent Is Nothing) Then
        findParent.Nodes.Add(node)
      Else
        If relationValue = "0" OrElse keyValue = relationValue Then
          tree.Nodes.Add(node)
        Else
          Throw New ArgumentException("Parent node not found: " + relationValue)
        End If
      End If
      _hashTree.Add(keyValue, node)
    Next

  End Sub

End Class

NOTES:
1) Define the data something like this:

ID          Text             ParentID
==        ===             =====
1           Root                  0
2           Parent               1
3           Child                 2
4           Child                 2
5           Parent               2

Sample usage:
Dim builder As New TreeBuilder
builder.BuildTree(Me.TreeView1, ds.Tables(0), "Text", "ID", "ParentID")

Bob

How would I save it to the Database?

With a slight adjustment here, and a tweak there (eh voila):

Public Class TreeBuilder

  Private Shared _hashTree As New Hashtable

  Public Shared Sub BuildTree(ByVal tree As TreeView, ByVal table As DataTable, _
   ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)

    For Each row As DataRow In table.Rows

      Dim relationValue As String = row(relationColumn).ToString
      Dim keyValue As String = row(keyColumn).ToString
      Dim node As TreeNode = New TreeNode(row(displayColumn).ToString)

      ' Store the parentID.nodeID values.
      node.Tag = relationValue & "." & keyValue

      ' Find the parent.
      Dim findParent As TreeNode = CType(_hashTree(relationValue), TreeNode)
      If Not (findParent Is Nothing) Then

        ' Add the node to the parent.
        findParent.Nodes.Add(node)
      Else

        ' Is this a root node?
        If relationValue = "0" OrElse keyValue = relationValue Then
          tree.Nodes.Add(node)
        Else
          ' If not, then raise an exception.
          Throw New ArgumentException("Parent node not found: " + relationValue)
        End If
      End If

      ' Add the node to the lookup table, by key value.
      _hashTree.Add(keyValue, node)

    Next row

  End Sub  'BuildTree


  Public Shared Sub SaveTree(ByVal table As DataTable, _
    ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)

    table.Rows.Clear()
    For Each node As TreeNode In _hashTree.Values

      Dim newRow As DataRow = table.NewRow()

      Dim keys As String = node.Tag.ToString()
      Dim keyValues() As String = keys.Split("."c)

      If keyValues.Length < 2 Then
        Throw New ArgumentException("Invalid value")
      End If

      newRow(relationColumn) = keyValues(0)
      newRow(keyColumn) = keyValues(1)
      newRow(displayColumn) = node.Text

      table.Rows.Add(newRow)

    Next node

  End Sub  'SaveTree

End Class

Those methods are shared now, so the syntax is like this:

'Build
TreeBuilder.BuildTree(Me.TreeView1, ds.Tables(0), "Text", "ID", "ParentID")

'Save
TreeBuilder.SaveTree(ds.Tables(0), "Text", "ID", "ParentID")

Bob

So using standard SQLCLient calls can be used? INSERT, SELECT?

Also, when I pasted the second Sub, I got this error:

"Cannot refer to an instance member of a class from within a shared method or shared member initializer without an explicit instance of the class."

Take the entire class, and overwrite the existing one.

Are you using an SqlDataAdapter?  Does it have InsertCommand, UpdateCommand, and DeleteCommand set?

Bob

I figured I would use a InsertCommand and or the UpdateCommand.

Ideas on either of those?

If you already have the SelectCommand initialized, then you can use a CommandBuilder to build the rest, as long as the SelectCommand includes the primary key column.

Bob

Will this update a blank or empty table? And if so, how would I append the string with the values above?

Here is my update string: (EXAMPLE)

Dim con as New SqlClient.SqlConnection("Server=sqlserver;initial catalog=DB;uid=sa;pwd=password123;Connect Timeout=30")
Dim cmdUpdate as New SqlClient.SqlCommand
cmdUpdate.CommandText= "Update YourTable SET [CurrentItems]=" & (FirstValue - 1) & " WHERE ItemID=" & ItemID
cmdUpdate.Connection = con
con.Open()
cmdUpdate.ExecuteNonQuery()
con.Close()

Bob,

Thanks for your help on this but I just cannot get it to work. I get different errors in different places.

What would be the best way to get it to save the values to the database?

It is a single table that can be defined how ever I want. But I need to be able to do it programmitcally since the user will be able to change the IP address of the SQL server so using the standard design time control for SQL is out.

(I am fairly new to the Database stuff for VB.NET 2005.)

I am use the ASP way.

Any help would be greatly appreciated.

Here was my attempt:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Save Function
        TreeBuilder.SaveTree(myDataSet.Tables("Items"), "ItemDesc", "CategoryKey", "ParentID")
End Sub

-----------------------------------------------------------------
TreeBuilder Code Now
-----------------------------------------------------------------
Public Shared Sub SaveTree(ByVal table As DataTable, _
      ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)
        Dim myDataSet As New DataSet
        Dim myCommand As New SqlClient.SqlCommand
        Dim myConnection As New SqlClient.SqlConnection
        Dim strSQLString = "Server=sqlserver;initial catalog=DB;uid=sa;pwd=password123;Connect Timeout=30"
        myConnection.ConnectionString = strSQLString
        myCommand.Connection = myConnection
        table = myDataSet.Tables("Items")
        'table.Rows.Clear() <-----GOT AN ERROR HERE SO I MARKED IT
        For Each node As TreeNode In _hashTree.Values
            Dim newRow As DataRow = table.NewRow()
            Dim keys As String = node.Tag.ToString()
            Dim keyValues() As String = keys.Split("."c)
            If keyValues.Length < 2 Then
                Throw New ArgumentException("Invalid value")
            End If
            newRow(relationColumn) = keyValues(0)
            newRow(keyColumn) = keyValues(1)
            newRow(displayColumn) = node.Text
            table.Rows.Add(newRow)
        Next node
    End Sub

'table.Rows.Clear() <-----GOT AN ERROR HERE SO I MARKED IT

What was the exception?

Bob

"Object reference not set to an instance of an object."

Try this:

Imports System.Data.SqlClient        <-- put this line at the top of the module

...

        Dim table As New DataTable
        Dim connectString As String = "Server=sqlserver;initial catalog=DB;uid=sa;pwd=password123;Connect Timeout=30"
        Dim commandText As String = "Select * From Table1"  <-- this is an example (replace with your SQL
        Dim adapter As New SqlDataAdapter(commandText, connectString)
        adapter.Fill(table)

If you don't have an existing table with data, then you don't need the table.Rows.Clear() call.

Bob

Ok, here is my updated code. but nothing happens now. No errors and the DB is not updated.

----------------------------------------------------------------------------------------------------------------
Public Shared Sub SaveTree(ByVal table As DataTable, _
      ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)
        Dim myCommand As New SqlClient.SqlCommand
        Dim myConnection As New SqlClient.SqlConnection
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myDataSet As New DataSet
        Dim strSQLString = "Server=sqlserver;initial catalog=DB;uid=sa;pwd=password123;Connect Timeout=30"
        myConnection.ConnectionString = strSQLString
        myCommand.Connection = myConnection
        Dim myQuery As String
        myQuery = "SELECT * FROM Items"
        myCommand.CommandText = myQuery
        myAdapter.SelectCommand = myCommand
        myAdapter.Fill(myDataSet, "Items")
        table = myDataSet.Tables("Items")

        table.Rows.Clear()
        For Each node As TreeNode In _hashTree.Values

            Dim newRow As DataRow = table.NewRow()

            Dim keys As String = node.Tag.ToString()
            Dim keyValues() As String = keys.Split("."c)

            If keyValues.Length < 2 Then
                Throw New ArgumentException("Invalid value")
            End If
            newRow(relationColumn) = keyValues(0)
            newRow(keyColumn) = keyValues(1)
            newRow(displayColumn) = node.Text
            table.Rows.Add(newRow)
        Next node
    End Sub  'SaveTree

If you insist on using a DataSet, then I have no control over that, even though you only need a DataTable.

After adding rows to the DataTable, then you need to update the database:

   adapter.Update(myDataSet)

Bob

Modified code with the following issues:
Plus the Call:

TreeBuilder.SaveTree(myDataSet.Tables("ItemsTbl"), "ItemDesc", "CategoryKey", "ParentID")

Is erroring as well.


------------------------------------------------------------------------
Public Shared Sub SaveTree(ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)
        Dim table As New DataTable
        Dim myConnection As String = "Server=10.1.1.102;initial catalog=CATALOG;uid=user;pwd=password;Connect Timeout=30"
        Dim myQuery As String = "SELECT * FROM ItemsTbl"
        Dim myAdapter As New SqlDataAdapter(myQuery, myConnection)

        myAdapter.Fill("ItemsTbl") <----ERROR

    'Overload resolution failed because no accessible 'Fill' can be called with these arguments:
    'Public Function Fill(dataTable As System.Data.DataTable) As Integer': Value of type 'String' cannot be converted to 'System.Data.DataTable'.
    'Public Overrides Function Fill(dataSet As System.Data.DataSet) As Integer': Value of type 'String' cannot be converted to 'System.Data.DataSet'


        table.Rows.Clear()
        For Each node As TreeNode In _hashTree.Values

            Dim newRow As DataRow = table.NewRow()

            Dim keys As String = node.Tag.ToString()
            Dim keyValues() As String = keys.Split("."c)

            If keyValues.Length < 2 Then
                Throw New ArgumentException("Invalid value")
            End If

            newRow(relationColumn) = keyValues(0)
            newRow(keyColumn) = keyValues(1)
            newRow(displayColumn) = node.Text

            table.Rows.Add(newRow)

        Next node

    End Sub  'SaveTree
-----------------------------------------------------------------------------

Can you mark out the password and stirng info in the last post? Thanks.

Public Shared Sub SaveTree(ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)
        Dim table As New DataTable
        Dim myConnection As String = "Server=10.1.1.102;initial catalog=CATALOG;uid=user;pwd=password;Connect Timeout=30"
        Dim myQuery As String = "SELECT * FROM ItemsTbl"
        Dim myAdapter As New SqlDataAdapter(myQuery, myConnection)

        myAdapter.Fill(table)

        table.Rows.Clear()
        For Each node As TreeNode In _hashTree.Values

            Dim newRow As DataRow = table.NewRow()

            Dim keys As String = node.Tag.ToString()
            Dim keyValues() As String = keys.Split("."c)

            If keyValues.Length < 2 Then
                Throw New ArgumentException("Invalid value")
            End If

            newRow(relationColumn) = keyValues(0)
            newRow(keyColumn) = keyValues(1)
            newRow(displayColumn) = node.Text

            table.Rows.Add(newRow)

        Next node

    End Sub  'SaveTree

Bob

What do I declare the ds as?

When I tried to declare it as a dataset, it errored on me.

Here is the call:

TreeBuilder.SaveTree(ds.Tables("ItemsTbl"), "ItemDesc", "CategoryKey", "ParentID")

What about the database? I do not have the connection string defined anywhere but that particular call.

This is what I call the "high flail" factor.  It is me guessing what the heck you mean.  You tried to tell me that you were going to pass in the dataset table, so I made the assumption that you already had the dataset created, and that you just needed to update it.  So, I modified the code to do just that.  But, now you say that you don't have the dataset anywhere else, so now I am having to scramble (flailing) to figure out just what you mean now.

Bob

I really do apologize. Let me be a bit clearer then.

On the app I have a TreeView control. I give the user the ability to add parent nodes and child nodes to that treeview using a standard textbox and a button. (The treeview is programmed in a way to only allow one level of child nodes.) Now I know I am missing the Database connection info below but where ever I place it, I get an error about Overload Resolution Failing.

Database connection info: (I do not have this placed anywhere yet due to the error I stated above)
_____________________________________________________________
Dim table As New DataTable
        Dim myConnection As String = "Server=10.1.1.102;initial catalog=CATALOG;uid=user;pwd=password;Connect Timeout=30"
        Dim myQuery As String = "SELECT * FROM ItemsTbl"
        Dim myAdapter As New SqlClient.SqlDataAdapter(myQuery, myConnection)
        myAdapter.Fill("ItemsTbl")
____________________________________________________________

Then, I have single button programmed as "save". This is where I am making the call to save it to the DB (Shown below). So here is how it stands.

Within the main app, I have declared this at the top of the app:
_____________________________________________________
Dim myDataSet As New DataSet
_____________________________________________________

Within the Button's code, I have this:
_____________________________________________________
Try
            TreeBuilder.SaveTree(myDataSet.Tables("ItemsTbl"), "ItemDesc", "CategoryKey", "ParentID")
        Catch ex As Exception
            MsgBox("Error with the DB Save. Error:" & vbCrLf & Err.Description, MsgBoxStyle.Exclamation, "Error")
        End Try
_____________________________________________________

I created a class called TreeBuilder which looks like this in its current form:

_____________________________________________________
Public Class TreeBuilder
    Private Shared _hashTree As New Hashtable

    Public Shared Sub BuildTree(ByVal tree As TreeView, ByVal table As DataTable, _
     ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)
                For Each row As DataRow In table.Rows
            Dim relationValue As String = row(relationColumn).ToString
            Dim keyValue As String = row(keyColumn).ToString
            Dim node As TreeNode = New TreeNode(row(displayColumn).ToString)
            ' Store the parentID.nodeID values.
            node.Tag = relationValue & "." & keyValue
            ' Find the parent.
            Dim findParent As TreeNode = CType(_hashTree(relationValue), TreeNode)
            If Not (findParent Is Nothing) Then
                ' Add the node to the parent.
                findParent.Nodes.Add(node)
            Else
                ' Is this a root node?
                If relationValue = "0" OrElse keyValue = relationValue Then
                    tree.Nodes.Add(node)
                Else
                    ' If not, then raise an exception.
                    Throw New ArgumentException("Parent node not found: " + relationValue)
                End If
            End If
            ' Add the node to the lookup table, by key value.
            _hashTree.Add(keyValue, node)
        Next row
    End Sub  'BuildTree

    Public Shared Sub SaveTree(ByVal table As DataTable, ByVal displayColumn As String, ByVal keyColumn As String, ByVal relationColumn As String)
        table.Rows.Clear()
        For Each node As TreeNode In _hashTree.Values
            Dim newRow As DataRow = table.NewRow()
            Dim keys As String = node.Tag.ToString()
            Dim keyValues() As String = keys.Split("."c)
            If keyValues.Length < 2 Then
                Throw New ArgumentException("Invalid value")
            End If
            newRow(relationColumn) = keyValues(0)
            newRow(keyColumn) = keyValues(1)
            newRow(displayColumn) = node.Text
            table.Rows.Add(newRow)
        Next node
    End Sub
End Class
___________________________________________________________________________________________

Probing questions:

1) .NET version?  2002, 2003 or 2005?
2) Application type?  WinForms?  Console?  Web Site?
3) Does the application have a main form?

Bob

2005

WinForms

Yes.


Thanks.

Define the DataSet on the main form, and with 2005, you can reference the DataSet in other classes without having to have an instance of the main form.

Example:

TreeBuilder.SaveTree(formMain.myDataSet.Tables("ItemsTbl"), "ItemDesc", "CategoryKey", "ParentID")

Bob

I did that already. But do I need to do it in the TreeBuilder class?

No, you should only have 1 DataSet on the main form, and reference it when you make calls to the TreeBuilder class.

Bob

Which I currently do based on my code above. Do I need to define the connection in the class or at the top of my form? See my current connection:

Database connection info: (I do not have this placed anywhere yet due to the error I stated above)
_____________________________________________________________
Dim table As New DataTable
        Dim myConnection As String = "Server=10.1.1.102;initial catalog=CATALOG;uid=user;pwd=password;Connect Timeout=30"
        Dim myQuery As String = "SELECT * FROM ItemsTbl"
        Dim myAdapter As New SqlClient.SqlDataAdapter(myQuery, myConnection)
        myAdapter.Fill("ItemsTbl")
____________________________________________________________

Define the connection, the query string, the data adapter, and the dataset, on the main form, and only reference the dataset where you are calling TreeBuilder.SaveTree.

Bob

What about the myAdapter.Fill ?

Where will that go?

In the Form_Load event for the main form.

Bob

I get an Overload Resolution Failed error when I place the following code into the Form_Load:

myAdapter.Fill("ItemsTbl")

myAdapter.Fill(myDataset)

Bob

I get this error when the App is ran:

Invalid Exception: "The ConnectionString property has not been initialized."

Here is what I have defined in the Main form.

Public Class Form1

#Region " PRIVATE/PUBLIC DIMS "
    Dim myDataSet As New DataSet
    Dim myConnection As String = "Server=10.1.1.102;initial catalog=CATALOG;uid=user;pwd=password;Connect Timeout=30"
    Dim myQuery As String = "SELECT * FROM ItemsTbl"
    Dim myAdapter As New SqlClient.SqlDataAdapter(myQuery, myConnection)
#End Region

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     myAdapter.Fill(myDataSet)
End Sub

I haven't a clue as to why you are getting that.

Test code:

Dim testString As String = myAdapter.SelectCommand.Connection.ConnectionString

Tell me whether it returns a valid string value or not.

Bob