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