Binding Hierarchical Data from SQL Server In ASP.net TreeView Control

Hi Expert, we have two tables in sql server database as below,

1- Groups Table
2-Vehicle Table

Groups table having following columns
ID,GroupName,UpID

Vehicle Table having following columns
ID,VehicleName,UpID

Now we need to bind hierarchical data in Treeview webform control , it should be display
group wise data.

Please provide code in vb.net.  with asp.net


Thanks in advance.
Asrar AzmiWeb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Asrar AzmiWeb DeveloperAuthor Commented:
Dear Expert,

I Know these two article i have seen it before , but this is not fulfill my requirement.
0
Rose BabuSenior Team ManagerCommented:
can you post some of sample data for those two tables?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here is the relevant #C code I have set up, but still testing some things ...

dr is a SqlDataReader in my case
treeData is the treeview control

                        
//dataset to hold the data, set up relations etc ...
DataSet ds = new DataSet();

// load the data from a normal datareader (see below for the query)
// note the "x" here is used later in the xml XPath !
ds.Load(dr, LoadOption.OverwriteChanges, "x");

// we will refer to 1 single data table, the first one
DataTable dt = ds.Tables[0];

// we want to set up the hierarchical relation (see in query
DataRelation relation = new DataRelation("parentchild", dt.Columns["id"], dt.Columns["pid"], true);

// to make sure we could have several levels of hierarchy
relation.Nested = true;

// add and apply that relation to the data set
ds.Relations.Add(relation);
ds.AcceptChanges();

// this small detail is required to get the XML formatted as needed for the treeview bindings
foreach (DataColumn column in dt.Columns)
{
  column.ColumnMapping = MappingType.Attribute;
}

// now, extract the XML from the data source
XmlDataSource xml = new XmlDataSource();

// for asp.net, we need to assign ID values to the XML, otherwise you get some errors
xml.ID = "XML_" + pk;
treeData.ID = xml.ID;

xml.Data = ds.GetXml();
// specify what the Xpath should be, the "x" coming from the dataset.Load function call above
xml.XPath = "/NewDataSet/x";

// set up a binding rule
                        TreeNodeBinding b = new TreeNodeBinding();
                        b.TextField = "data";
                        b.ValueField = "id";
                         /* the rest can be added, fields to be added to the query
                        b.ImageUrlField = "icon";
                        b.ImageToolTipField = "tooltip";
                        b.NavigateUrlField = "url";
                        b.ToolTipField = "tooltip";
                        b.TargetField = "target";
                         */
                        b.PopulateOnDemand = false;

                /// add and apply the binding and xml datasource to the treeview
                        treeData.DataBindings.Add(b);
                    
                        treeData.DataSource = xml;
                        treeData.DataBind();

Open in new window


the query, for your table for above needs to return (at least) the 3 following columns:
id
pid
data

this select suggestion assumes that UpID  is groups points to the parent "group", and UpID  in vehicles points to the "group" the vehicle belongs to.
select 'G' + cast(g.ID as varchar(100)) id 
, g.GroupName data 
, 'G' + cast(g.UpID as varchar(100)) pid
  from groups
UNION ALL
select 'V' + cast(v.id as varchar(100)) id
 , v.VehicleName data
 , 'G' + cast(v.UpID as varchar(100)) pid
 from Vehicle v

Open in new window


hope this helps, sorry for the long delay
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.