Fill a tree view with a stored procedure

1 Mar 2006 6:13 PM

john wright

I have a stored procedure that returns a dataset. I want this dataset to
fill a tree view.

Here is the Stored Procedure Query

SELECT     Product.Product_#, Product.Part_Number, Status.Status,
Traveler_Step.Step, Station.Station_Name
FROM         Product INNER JOIN
                      Traveler_Step ON Product.Traveler_Step_# =
Traveler_Step.Traveler_Step_# INNER JOIN
                      Station ON Traveler_Step.Station_# = Station.Station_#
INNER JOIN
                      Status ON Product.Status_# = Status.Status_#
WHERE     (Station.Station_# = 8)

Here is a typical result

Product_#   Part_Number          Status               Step
Station_Name
----------- -------------------- -------------------- ----------- --------------------
48          123789               Active               40          Hot Mill
231         U06107LG             Active               20          Hot Mill

I would like the tree view master node to be Station Name, the first child
node to be the Part Number with an icon next to it indicating the status,
next child node under part_number the Step and Product_#.

So this is what I am after

HotMill

|

-----(Active icon) 123789

|

------40

|

------48

I understand I need hierarchical data, but the stored procedure only returns
one dataset. How can I do this?

Thanks.

John

1 Mar 2006 7:38 PM

Aboulfazl Hadi

Hi John
Suppose you populate your dataTable and you are ready to fill your tree
using FillTree :

   private void FillTree(DataTable table)
        {
            foreach (DataRow row in table.Rows)
                AddRow(row);
        }

        private void AddRow(DataRow row)
        {
            TreeNode stationNode =
FindStationNode(row["Station_Name"].ToString());
            if (stationNode == null)
                AddStationNode(row["Station_Name"]);

            AddToStation(stationNode, row);
        }

        private void AddToStation(TreeNode stationNode, DataRow row)
        {
            TreeNode node = new TreeNode();
            node.Text = row["Part_Number"].ToString();
            if (row["Status"].ToString() == "Active")
                node.ImageIndex = 0;
            else
                node.ImageIndex = 1;
            node.Nodes.Add(row["Step"].ToString());
            node.Nodes.Add(row["Product_#"].ToString());

            stationNode.Nodes.Add(node);
        }

        private void AddStationNode(string name)
        {
            treeView1.Nodes.Add(name);
        }

        private TreeNode FindStationNode(string p)
        {
            foreach (TreeNode node in treeView1.Nodes)
                if (node.Text == p)
                    return node;
            return null;
        }

Best Regards,
A.Hadi

1 Mar 2006 7:45 PM

Aboulfazl Hadi

I'm sorry.I thought I am in C# group. (VB version) :
Private Sub FillTree(ByVal table As DataTable)

        For Each row As DataRow In table.Rows
            AddRow(row)
        Next
    End Sub

    Private Sub AddRow(ByVal row As DataRow)

        Dim stationNode As TreeNode =
FindStationNode(row("Station_Name").ToString())
        If stationNode Is Nothing Then
            AddStationNode(row("Station_Name"))
        End If
        AddToStation(stationNode, row)
    End Sub

    Private Sub AddToStation(ByVal stationNode As TreeNode, ByVal row
As DataRow)

        Dim node As TreeNode = New TreeNode()
        node.Text = row("Part_Number").ToString()
        If (row("Status").ToString() = "Active") Then
            node.ImageIndex = 0
        Else
            node.ImageIndex = 1
            node.Nodes.Add(row("Step").ToString())
            node.Nodes.Add(row("Product_#").ToString())
        End If

        stationNode.Nodes.Add(node)
    End Sub

    Private Sub AddStationNode(ByVal name As String)

        TreeView1.Nodes.Add(name)
    End Sub

    Private Function FindStationNode(ByVal name As String) As TreeNode

        For Each node As treenode In treeView1.Nodes
            If node.Text = name Then
                Return node
            End If
        Next
        Return Nothing
    End Function

Thanks