Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Resources » Articles » .NET Framework »
Showing Hierarchical Data in C#
|
In Many situations of typical business programming it’s necessary to show and retrieve hierarchical data from a database. For example in a typical ERP package there should be a place to define Geography details (hierarchical) of a particular company. Normally Geography Levels are defined like – COUNTRY, STATE, CITY and after defining these linear geography levels a company has to define it’s actual, geography details where it has any business. Look at the following
INDIA
|______Maharashtra
| |__________Mumbai
|
|______West Bengal
|__________Calcutta
To replicate these in DBMS scenario ( geography level is assumed linear but geography is implicitly one parent-multiple child case ) following two tables are assumed to exist. I assume oracle 9i because later I will use some of its features – GEOGRAPHY_LEVEL and GEOGRAPHY_MST
CREATE TABLE GEOGRAPHY_LEVEL
{
LEVEL_NO NUMBER(3) PRIMARY KEY,
LEVEL_NAME NVARCHAR2(100) NOT NULL
}
CREATE TABLE GEOGRAPHY_MST
{
GEO_ID NUMBER(10) PRIMARY KEY,
GEO_PARENT_ID NUMBER(10) ,
GEO_LONG_NAME NVARCHAR2(100) NOT NULL,
LEVEL_NO NUMBER(3) NOT NULL,
CONSTRAINT GEO_FK1 FOREIGN KEY ( LEVEL_NO ) REFERENCES
GEOGRAPHY_LEVEL ( LEVEL_NO ),
CONSTRAINT GEO_FK2 FOREIGN KEY ( GEO_PARENT_ID ) REFERENCES GEOGRAPHY_MST ( GEO_ID )
}
Look carefully the last foreign key relation ship that establishes a parent-child relationship within the GEOGRAPHY_MST table. GEO_PARENT_ID is deliberately made NULL-able because for the first level of geography master definition parent will be NULL. Now this kind of self-referential table definition also exist in the Oracle supplied SCOTT schema table EMP. Every record in EMP table has a column called MGR which references to EMPNO ( pk of the table ) and basically tells who the manager of the current employee is. The challenge is to show this hierarchy in a client-side TreeView control and the language used is C#.
CONNECT BY – What to connect ?
Let’s find out what Oracle can offer us to solve the problem. Oracle 8i gives a new SQL clause called CONNECT BY especially for this kind of hierarchical data. Say you want to show the employees starting from the top boss ‘KING’ and who works under whose. The SQL query can be like below:-
SELECT LPAD(' ',5*(LEVEL-1)) || ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO
And the display will be like -
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
14 rows selected.
LEVEL is another ORACLE-specific SQL clause that is
used in conjunction with CONNECT. It simply tells what is the current level – depth of am employee .For example
KING’s level is 1 and SMITH’s level is 4, WARD’s level is 3 and so on. But this query can’t help us in any way to show this data inside a System.Windows.Forms.TreeView control. What’s best we can do to execute following SQL
without any display specific LPAD() function:-
SELECT LEVEL,ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;
Now we have to devise some kind of algorithm based on data
just like what we have got at below -
1 KING
2 JONES
3 SCOTT
4 ADAMS
3 FORD
4 SMITH
2 BLAKE
3 ALLEN
3 WARD
3 MARTIN
3 TURNER
3 JAMES
2 CLARK
3 MILLER
Let’s call the Level numbers Indexes[] array and corresponding names Names[] array
I also define a variable callede PB or PresentBossLevel. In
pseudo-language the algorithm can be like below:
1. PB = 1;
2. Loop
3. if ( Indexes[i] > Indexes[i-1] )
{
PB = Indexes[i];
Draw this node under node Names [i-1];
}
4. if ( Indexes[i] <= Indexes[i-1] )
{
PB = Indexes[i] – 1;
Find the last boss at PB;
Draw this node under last boss;
}
This algorithm is just a logical construct and may have lapses but on the way I will correct it. To make this algorithm successful I will assume the existence of a function which finds a treenode recursively based on unique names. For the time being I assume that ENAME field is UNIQUE. Such a function can be implemented as follows:-
private TreeNode FindNode(TreeNode tn, string name)
{
TreeNode tnNew = null;
if (tn.Text == name)
{
return tn;
}
else
{
foreach(TreeNode ChildNode in tn.Nodes)
{
tnNew = FindNode(ChildNode,name);
}
return tnNew;
}
}
System.Windows.Forms.TreeNode is a .NET implementation of a single Treenode under which several other nodes can be placed through its Nodes collection which itself is a collection of System.Windows.Forms.TreeNode objects.The first part is terminating condition for the recursive procedure. For each child nodes of the current node the procedure calls itself. Note down the for () loop inside a recursive procedure – means it’s very stack-intensive (processor intensive also) operation. Whenever search terminates it retrurns a reference to that TreeNode object otherwise it returns null. The actual function that builds tree is given below:-
private void DrawEmployeeTree(TreeNode tn)
{
int PresentBossLevel = 1;
string strLastBoss = "";
for(int i = 1; i < Indexes.Length; i++)
{
if (Indexes[i] > Indexes[i-1])
{
FindNode(tn,Names[i-1]).Nodes.Add(Names[i]);
PresentBossLevel = Indexes[i];
strLastBoss = Names[i];
}
else
{
PresentBossLevel = (Indexes[i] - 1);
//1 2 3 4 4 3 ;raw it at : ( 3 - 1 ) = 2
//find the node with name Name[2]
for(int m = 0; m < Indexes.Length ; m++)
{
if ((Indexes[m] == PresentBossLevel) &&
(m <= i))
{
strLastBoss = Names[m];
//break;
}
}
FindNode(tn,strLastBoss).Nodes.Add(Names[i]);
}
}
}
We will use OLEDB managed data provider for Oracle or in other words use the System.Data.OleDb namespace. But managed data provider for specifically Oracle is released just like SQL Server. In that case you have to use System.Data.OracleClient namespace. As part of Setup, the System.Data.OracleClient namespace is added to the global assembly cache. For additional information, you can visit the http://communities.microsoft.com/newsgroups. then choose.NET Development, Framework, dotnet.Oracle.net. Anyway at the class level following variables are used
public class CEmpTree : System.Windows.Forms.Form
{
private System.Data.OleDb.OleDbConnection Conn;
private System.Windows.Forms.TreeView Tree;
private int[] Indexes;
private string[] Names;
//other stuff . . .
}
At the constructor of the class creates the connection.
this.Conn.ConnectionString = "provider=MSDAORA;user
id=scott;data source=;password=tiger";
At the form load open the connection and also count the length of our tabular data of Level No – Employee Name pairs so that we can built them dynamically before OledbDataReader take over. For length counting I have used following function:-
private int GetCount(OleDbConnection conn)
{
string strSQL = "SELECT COUNT(*) " +
"FROM EMP " +
"START WITH MGR IS NULL "+
"CONNECT BY MGR = PRIOR EMPNO";
OleDbCommand cmd = new OleDbCommand(strSQL,conn);
object count = cmd.ExecuteScalar();
if (count != null)
return Convert.ToInt32( count.ToString() );
else
return 0;
}
Specifically for this kind of single row – single column query like MAX () , COUNT () , OleDbCommand provides a special function called ExecuteScaler () which returns a System.Object instance. As we know it’s an integer we can dynamically convert this to a System.Int32 type. Form the Form_Load we simply calls this function passing an already created connection object: Conn. Without any more fuss I give you the form_load code
Conn.Open();
try
{
int m = GetCount(Conn);
string strSQL = "SELECT LEVEL,ENAME " +
"FROM EMP " +
"START WITH MGR IS NULL " +
"CONNECT BY MGR = PRIOR EMPNO";
OleDbCommand cmd = new OleDbCommand(strSQL,Conn);
OleDbDataReader dr = cmd.ExecuteReader();
Indexes = new int[m];
Names = new string[m];
int k = 0;
while(dr.Read())
{
Indexes[k] =
Convert.ToInt32(dr.GetValue(0).ToString());
Names[k] =
dr.GetValue(1).ToString();
k++;
}
Tree.Nodes.Add(Names[0]);
DrawEmployeeTree(Tree.Nodes[0]);
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Source + ": "+ ex.Message);
}
finally
{
Conn.Close();
Conn.Dispose();
Conn = null;
}
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|