C#: Treeview from Database ( 3 tables) window application

Question

Hi,

I am stuck with creating a treeview in VIsual Studio C# that should show hierarchical data from 3 tables in Oracle. the tables are like this:

Table1 - Hierarchy
HROW_ID --pk
HIER_NAME

Table2 - LOCATION
LROW_ID --pk
LOC_NAME
ROOT_ID --fk - HROW_ID

Table3 - EQUIPMENT
EROW_ID --pk
EQUIP_NAME
LOC_ID --fk -LROW_ID

i want to treeview which should have hierarchy like below:

H1
L1
e1
e2
L2
e1
e5
L3
e6
e7
H2
L4
L5

I wrote the below code but I am not able to get it right. The output is vague. it does not work for 3rd level. Please help me.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OracleClient;

namespace HierarchicalTreeView1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private System.Windows.Forms.TreeView tree;
System.Data.OracleClient.OracleConnection con;
System.Data.OracleClient.OracleCommand com;
System.Data.OracleClient.OracleDataAdapter da;
System.Data.OracleClient.OracleCommand com1;
System.Data.OracleClient.OracleDataAdapter da1;
System.Data.OracleClient.OracleCommand com2;
System.Data.OracleClient.OracleDataAdapter da2;


System.Data.DataSet ds;

string connectionString = "Data Source=ORCL;User ID=scott;Password=tiger;Unicode=True";
string HierSQL = "SELECT * FROM Hierarchy";
string LocSQL = "SELECT l.* FROM Location l where root_id in (select hrow_id from hierarchy) order by lrow_id";//+ " Where root_id = @row_id";

string equipSQL = "SELECT e.* FROM Equipment e where loc_id in (select lrow_id from location) order by erow_id";

private void Form1_Load(object sender, EventArgs e)
{
con = new System.Data.OracleClient.OracleConnection(connectionString);
com = new System.Data.OracleClient.OracleCommand(HierSQL, con);
da = new System.Data.OracleClient.OracleDataAdapter(com);
ds = new System.Data.DataSet();


con.Open();
da.Fill(ds, "Hierarchy");
da.SelectCommand.CommandText = LocSQL;
da.Fill(ds, "Location");
da.SelectCommand.CommandText = equipSQL;
da.Fill(ds, "Equipment");
con.Close();

TreeNode nodechild = new TreeNode();

DataColumn RootCol = ds.Tables["Hierarchy"].Columns["HROW_ID"];
DataColumn childCol = ds.Tables["Location"].Columns["ROOT_ID"];
DataColumn ParentCol = ds.Tables["Location"].Columns["LROW_ID"];
DataColumn childCol1 = ds.Tables["Equipment"].Columns["LOC_ID"];
DataRelation relation = new DataRelation("Hier_Loc", RootCol, childCol);
DataRelation relation1 = new DataRelation("Loc_Equip", ParentCol, childCol1);
ds.Relations.Add(relation);
ds.Relations.Add(relation1);

foreach (DataRow root in ds.Tables["Hierarchy"].Rows)
{
TreeNode nodeRoot = tree.Nodes.Add(root["Hier_Name"].ToString());
foreach (DataRow child in root.GetChildRows(relation))
{
nodechild = nodeRoot.Nodes.Add(child["Loc_Name"].ToString());
foreach (DataRow pchild in ds.Tables["Location"].Rows)
{
foreach (DataRow subchild in pchild.GetChildRows(relation1))
{
nodechild.Nodes.Add(subchild["Equip_Name"].ToString());
}
}
}
}
}
}
}

Please help.
Thanks