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

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vasanth16y ago
In sql server we are using Common Table Expression (CTE) and using ORDERBY clause to bring the hierarchy level as like you mentioned.

Please check the CTE sample and follow in oracle.

Please mark as anser , if it helps you.
0