populate a treeview from a db

[Kam]

I'm trying to populate a treeview from a SQL tables in C#. i've
written the SQL procedures (i.e. database wise is sorted). However, I
would like to see an example of how to retrieve the data from the
tables (recrusive method).

I finally worked out how to retrieve the data. it was just a matter of
understanding the database class I am using. However, one problem I
have at the moment is that I could display the parent nodes and the
first node subnodes which is not even what I want to display (i need
to modify the procedure).

the following is the method I created to display the data from the
table. any idea how I could display subnodes (i.e. code logic!)

//------------------------------------------------
private void buildTreeView()
{
// the connection string and command string
// "upDisplaySites" is
the SQL Procedure name .....
string connstring =
ConfigurationSettings.AppSettings["ConnectionString"];
DataTable dt =
TMG.Data.DataAccess.GetResultTable("upDisplaySites", connstring);
DataTable dt1 =
TMG.Data.DataAccess.GetResultTable("upDisplayGroups", connstring);
DataTable dt2 =
TMG.Data.DataAccess.GetResultTable("upDisplayUsers", connstring);

treeView1.Nodes.Clear();

try
{
TreeNode parentNode = new TreeNode();
foreach(DataRow dr in dt.Rows)
{
treeView1.Nodes.Add(dr["Name"].ToString());
//TreeNode tNode = new TreeNode();
parentNode = treeView1.Nodes[0];
}
// this needs to be changed!
foreach(DataRow dr1 in dt1.Rows)
{
TreeNode subNode = new TreeNode();
parentNode.Nodes.Add(dr1["Name"].ToString());
subNode = treeView1.Nodes[0];

}

}
catch (Exception ex){ MessageBox.Show("Err");}
}
}
//--------------------------------------------------------------------------------------

the above displayed the Sites as follows:
==============
-a
-1
-2
-3
-b
-c
-d
-f
==============

The idea is to display the sites then the groups within the sites and
then the users in each group. (i.e. 3 levels of nodes)

Any idea of I could logically work this out. (note: the table are as
follows: site, group, user and others involved. i.e. multiple table
are used)

Thank you in advance.
Kam

 

[Michael Lang]

(e-mail address removed) (Kam) wrote in

I'm trying to populate a treeview from a SQL tables in C#. i've
written the SQL procedures (i.e. database wise is sorted). However, I
would like to see an example of how to retrieve the data from the
tables (recrusive method).

I finally worked out how to retrieve the data. it was just a matter of
understanding the database class I am using. However, one problem I
have at the moment is that I could display the parent nodes and the
first node subnodes which is not even what I want to display (i need
to modify the procedure).

the following is the method I created to display the data from the
table. any idea how I could display subnodes (i.e. code logic!)

//------------------------------------------------
private void buildTreeView()
{
// the connection string and command string
// "upDisplaySites" is
the SQL Procedure name .....
string connstring =
ConfigurationSettings.AppSettings["ConnectionString"];
DataTable dt =
TMG.Data.DataAccess.GetResultTable("upDisplaySites", connstring);
DataTable dt1 =
TMG.Data.DataAccess.GetResultTable("upDisplayGroups", connstring);
DataTable dt2 =
TMG.Data.DataAccess.GetResultTable("upDisplayUsers", connstring);

treeView1.Nodes.Clear();

try
{
TreeNode parentNode = new TreeNode();
foreach(DataRow dr in dt.Rows)
{
treeView1.Nodes.Add(dr["Name"].ToString());
//TreeNode tNode = new TreeNode();
parentNode = treeView1.Nodes[0];
}
// this needs to be changed!
foreach(DataRow dr1 in dt1.Rows)
{
TreeNode subNode = new TreeNode();
parentNode.Nodes.Add(dr1["Name"].ToString());
subNode = treeView1.Nodes[0];

}

}
catch (Exception ex){ MessageBox.Show("Err");}
}
}
//---------------------------------------------------------------------
-----------------

the above displayed the Sites as follows:
==============
-a
-1
-2
-3
-b
-c
-d
-f
==============

The idea is to display the sites then the groups within the sites and
then the users in each group. (i.e. 3 levels of nodes)

Any idea of I could logically work this out. (note: the table are as
follows: site, group, user and others involved. i.e. multiple table
are used)

Thank you in advance.
Kam

Create multiple methods, one for each logical level of detail. If the
tree was all of one type of object then create a single recursive method.
The key is to pass in the "TreeNodeCollection" where a given set of items
should start populating. For each item add a single node, and also call
the recursive or other related method for adding it's children.

You should also try to refresh the tree instead of a total regen.
Otherwise the user loses the collapsed/expanded state of all of their
nodes and they lose their position.

If you would like a recursive example just ask. I've done it on a
current project of mine.

Example (non-recursive):
Make a class for each logical object that can be filled by a DataRow.
Then in the form a fill method for each level...

public class WebSite
{
public Guid Identity{get;} // this should be based on PK of record.
// designed for use with tables containing a GUID pk, but should
// work with other pk types. Just change all the Guid references
// to the correct type.
public sting Address{get;set;}
public string Name{get;set;}
public void Fill(DataRow dr){...}
}
public class Group{/*same structure as WebSite*/}
public class User{/*same structure as WebSite*/}

public class Form1
{
private DataTable dtSites = ...
private DataTable dtGroups = ...
private DataTable dtUsers = ...

public void RegenTree()
{
myTree.Nodes.Clear(); RefreshTree();
}
public void RefreshTree()
{
this.FillSites(myTree.Nodes, dtSites.Rows);
}
private void FillSites(TreeNodeCollection nodes)
{
for (int i=0; i < dtSites.Rows.Count; i++)
{
WebSite nxtSite = new WebSite();
nxtSite.Fill(dtSites.Rows);
TreeNode exst = FindNode(nodes, nxtSite.Identity);
if (exst == null)
{
exst = new TreeNode();
exst.Text = nxtSite.Name;
exst.Tag = nxtSite.Identity;
}
// update any other desired node properties
FillGroups(esxt.Nodes, nxtSite);
}
}
private void FillGroups(TreeNodeCollection nodes, WebSite parent)
{
for (int i=0; i < dtGroups.Rows.Count; i++)
{
Group nxtGrp = new Group();
nxtGrp.Fill(dtGroups.Rows);
if (nxtGrp.Parent == parent){continue;}
TreeNode exst = FindNode(nodes, nxtGrp.Identity);
if (exst == null)
{
exst = new TreeNode();
exst.Text = nxtGrp.Name;
exst.Tag = nxtGrp.Identity;
}
// update any other desired node properties
FillUsers(exst.Nodes, nxtGrp);
}
}
private void FillUsers(TreeNodeCollection nodes, Group parent)
{
// about the same as FillGroups
}
private TreeNode FindNode(TreeNodeCollection nodes, Guid iden)
{
for (int i=0; i < nodes.Count; i++)
{
TreeNode nxt = nodes;
Guid tg = nxt.Tag as Guid;
if (tg == iden){return nxt;}
}
return null;
}
}

 

[Kam]

thanks for yr reply. it was very good and it helped me alot. but i've
got a problem with get; and set; as they keep generating errors
(property or indexer must have at least one accessor) &
(TMG.Sites.Name.get) must declare a body because it is not marked
abstract or extern) ...etc...

any idea? Iam very new to C# and keen to learn it. finishing this task
is urgent asI need to hand in the task by the end of today!..

Thank you very much. i really appreciate it. i've not checked the
links u forwarded me however, i will later, it must be good.

 

[Kam]

Hello Michael,

i've implemented your code as follows but I keep getting some errors.

==================================================================
using System;
using System.Drawing;
using System.Configuration;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace TMG
{
public class Sites
{
private Guid identity;
public Guid Identity
{
get{return this.identity;}
}

private string address;
public string Address
{
get{return this.address;}
set{this.address = value;}
}

private string name;
public string Name
{
get{return this.name;}
set{this.name = value;}
}

public void Fill(DataRow dr){}
}

public class Groups
{
private Guid identity;
public Guid Identity
{
get{return this.identity;}
}

private string address;
public string Address
{
get{return this.address;}
set{this.address = value;}
}

private string name;
public string Name
{
get{return this.name;}
set{this.name = value;}
}

public void Fill(DataRow dr){}
}

public class Users
{
private Guid identity;
public Guid Identity
{
get{return this.identity;}
}

private string address;
public string Address
{
get{return this.address;}
set{this.address = value;}
}

private string name;
public string Name
{
get{return this.name;}
set{this.name = value;}
}

public void Fill(DataRow dr){}
}

public class Editor : System.Windows.Forms.Form
{
private System.Windows.Forms.TreeView treeView1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.TextBox textBox1;
private System.ComponentModel.Container components = null;

private DataTable dt;
private DataTable dt1;
private DataTable dt2;

public Editor()
{
InitializeComponent();

string connstring =
ConfigurationSettings.AppSettings["ConnectionString"];
this.dt =
TMG.Data.DataAccess.GetResultTable("upDisplaySites", connstring);
this.dt1 =
TMG.Data.DataAccess.GetResultTable("upDisplayGroups", connstring);
this.dt2 =
TMG.Data.DataAccess.GetResultTable("upDisplayUsers", connstring);

FillSites();
}

protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}

#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.treeView1 = new System.Windows.Forms.TreeView();
this.button1 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.SuspendLayout();
//
// treeView1
//
this.treeView1.ImageIndex = -1;
this.treeView1.Location = new System.Drawing.Point(32,
8);
this.treeView1.Name = "treeView1";
this.treeView1.SelectedImageIndex = -1;
this.treeView1.Size = new System.Drawing.Size(288, 368);
this.treeView1.TabIndex = 1;
//
// button1
//
this.button1.Location = new System.Drawing.Point(112,
384);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(96, 24);
this.button1.TabIndex = 2;
this.button1.Text = "button1";
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(336,
16);
this.textBox1.Multiline = true;
this.textBox1.Name = "textBox1";
this.textBox1.ReadOnly = true;
this.textBox1.Size = new System.Drawing.Size(272, 360);
this.textBox1.TabIndex = 3;
this.textBox1.Text = "textBox1";
//
// Editor
//
this.AutoScaleBaseSize = new System.Drawing.Size(5,
13);
this.ClientSize = new System.Drawing.Size(632, 413);
this.Controls.AddRange(new
System.Windows.Forms.Control[] {

this.textBox1,

this.button1,

this.treeView1});
this.Name = "Editor";
this.Text = "Editor";
this.ResumeLayout(false);

}
#endregion

public void RegenTree()
{
treeView1.Nodes.Clear();
RefreshTree();
}

public void RefreshTree()
{
this.FillSites(treeView1.Nodes, dt.Rows);
}

private void FillSites(TreeNodeCollection nodes)
{
for (int i=0; i < dt.Rows.Count; i++)
{
Sites nxtst = new Sites();
nxtst.Fill(dt.Rows);
TreeNode exst = FindNode(nodes, nxtst.Identity);
if (exst == null)
{
exst = new TreeNode();
exst.Text = nxtst.Name;
exst.Tag = nxtst.Identity;
}

FillGroups(exst.Nodes, nxtst);
}
}

private void FillGroups(TreeNodeCollection nodes, Sites
parent)
{
DataTable dt1 =
TMG.Data.DataAccess.GetResultTable("upDisplayGroups", connstring);

for (int i=0; i < dt1.Rows.Count; i++)
{
Groups nxtGrp = new Groups();
nxtGrp.Fill(dt1.Rows);
if (nxtGrp.Parent == parent){continue;}
TreeNode exst = FindNode(nodes, nxtGrp.Identity);
if (exst == null)
{
exst = new TreeNode();
exst.Text = nxtGrp.Name;
exst.Tag = nxtGrp.Identity;
}
// update any other desired node properties
FillUsers(exst.Nodes, nxtGrp);
}
}

private void FillUsers(TreeNodeCollection nodes, Groups
parent)
{
DataTable dt2 =
TMG.Data.DataAccess.GetResultTable("upDisplayUsers", connstring);
for (int i=0; i < dt2.Rows.Count; i++)
{
Users nxtusr = new Users();
nxtusr.Fill(dt2.Rows);
if (nxtusr.Parent == parent){continue;}
TreeNode exst = FindNode(nodes, nxtusr.Identity);
if (exst == null)
{
exst = new TreeNode();
exst.Text = nxtusr.Name;
exst.Tag = nxtusr.Identity;
}
}
}

private TreeNode FindNode(TreeNodeCollection nodes, Guid
iden)
{
for (int i=0; i < nodes.Count; i++)
{
TreeNode nxt = nodes;
Guid tg = nxt.Tag as Guid;
if (tg == iden){return nxt;}
}
return null;
}
}
}
=================================================================================

The errors are as follows:
if (nxtGrp.Parent == parent)
('TMG.Groups' does not contain a definition for 'Parent')


Guid tg = nxt.Tag as Guid;
(The as operator must be used with a reference type ('System.Guid' is
a value type))

and in addition, I don't know what parameters I should pass to the
method FillSites() when called from the Main and RefreshTree?!!! I
apologise but im new to C# and very keen to learn it.

Any help would be very much appreciated as I need to complete this
task by the end of the day or I am in a big trouble!!!

Thanks and looking forward to your reply.

Kam

 

[Michael Lang]

(e-mail address removed) (Kam) wrote in

Hello Michael,

i've implemented your code as follows but I keep getting some errors.

================================================================== ... omitted in reply ...
=======================================================================

The errors are as follows:
if (nxtGrp.Parent == parent)
('TMG.Groups' does not contain a definition for 'Parent')


Guid tg = nxt.Tag as Guid;
(The as operator must be used with a reference type ('System.Guid' is
a value type))

and in addition, I don't know what parameters I should pass to the
method FillSites() when called from the Main and RefreshTree?!!! I
apologise but im new to C# and very keen to learn it.

Any help would be very much appreciated as I need to complete this
task by the end of the day or I am in a big trouble!!!

Thanks and looking forward to your reply.

Kam

All the code I entered in the message was typed on the fly in my
newsreader. I didn't test ANY of it beforehand. If it only had two error
then I'd call that pretty good! I really only meant it to be a guide
to cover the tree population logic, not to be the exact code you type in.
It doesn't show everything.

I forgot about the as operator on only reference types. That's easy to
fix. Put the nxt.Tag value into a "object" type variable and try to
convert it to Guid. If it does not convert then follow the code path
shown for when "tg" is null.

FillSites() should only be called from RefreshTree() or RegenTree().
Call one of those methods instead.

Did you implement the Fill method on each of those classes. In my
example I didn't show how, I just assumed you would know that part. All
you do is read each datarow column value and assign it to the appropriate
property in the class. Also for every field in the DataRow/DataTable you
should have a property in the class. Since I didn't know your schema, I
only showed an example. You are showing all three classes containing the
same properties? each should have properties to reflect what is in those
tables. Is there really a "Name" and "Address" field on a groups or
users table?

I forgot to add the Parent property to the class examples. Basically
Parent is refering to the item it is contained within. A "group" will
have a Parent property that refers to the Web site is a member of, and a
"user" will have a Parent property that refers to the group it is a
member of.

First define a "ParentID" property which is the type of the (PK of the
parent table | FK of the child table) which returns the identity of the
object it is contained within. In the example I just assumed Guid
(uniqueIdentifier).

Optionally you can have a "Parent" property which is of the type that the
object is a member of. ("Group"'s Parent will be of type "Site", and
"User"'s Parent will be of type "Group"). The tree population code really
just needs the ParentID field, so the Parent property would be for other
uses. So I don't know if you need it at all?

Again, I don't know anything about your database schema. If you reply
for any more help, post the schema also. I assume you have a primary key
of some kind on each table? Either "int"(Sql or Access) or
"uniqueIdentifier" (sql) or "ReplicationID" (Access). That PK for each
table is what Identity on the related class should return. Also I assume
you have a foreign key on each of the child tables that specifies what
parent it has. That FK value should be the same as the PK value on the
parent table. So when you populate that child class with the DataRow,
the ParentID property should be set the same as the FK field value.

Note, The sample method "FindNode()" will only work if all the PK types
for each table are the same datatype (any one of integral, string, or
guid types). If they are not the same type then each level of the tree
will need it's own FindNode(). If that is the case you may as well just
incorporate that code directly in the Fill<LevelName>() method for that
level.

Newsgroups are for pointing people in the right direction, not writing
all their code for free. I am open for a day or week contract consulting
if you want. If you are interested in immediate consulting, email me at
the following address and we can exchange phone numbers.

(e-mail address removed)
(remove all capital letters in address)