B
Björn Bengtsson
Hello!
I have an urgent problem concerning ASP.NET, ADO.NET, SQL Server, XML and
the TreeView control.
I have two tables; one describing the products and one describing their
relationships. A product may consist of multiple other products. So far
everything is great. But one product may exist as a subproduct to multiple
parent products.
My tables:
Products(prod_id, nam)
ProductRelation(prod_id, parent_id)
My relation:
Product 1 consists of Product 2 and Product 3.
Product 4 consists of Product 5 AND Product 3.
My data in the table ProductRelation:
product 2, product 1
product 3, product 1
product 5, product 4
product 3, product 4 // This is what causes the error; product 3.
My intentions:
Retrieving the data from a MS SQL Server 7 and transforming it into XML, a
TreeView control is to be used to display the hierarchical data. The problem
is that I don't know how to make this work. Storing it and retrieving it is
of no problem, but the fact that product 3 is nested in two different places
makes it a bit hard.
Think of it as a manufacturer who makes different products as tables and
chairs and nails. Nails are used in both tables and chairs...
My code:
//DataSet
DataSet ds = new DataSet("COMPONENTDS");
//Fill DataSet
da.Fill(ds, "COMPONENT");
// Create a recursive data relation.
DataColumn col1 = ds.Tables[0].Columns["prod_id"];
DataColumn col2 = ds.Tables[0].Columns["parent_id"];
DataRelation drel = new DataRelation("PRODUCTS_RECURSIVE", col1, col2,
true);
drel.Nested = true;
ds.Relations.Add(drel);
//XmlDocument to hold XML generated from DataSet
XmlDocument objDoc = new XmlDocument();
//Load XML
objDoc.LoadXml(ds.GetXml());
//Create XslTransform object
XslTransform objXSL = new XslTransform();
//Load XSLT stylesheet
objXSL.Load(Server.MapPath("hierarchy.xslt"));
//StringWriter to temporarily hold result of the transformation
StringWriter writer = new StringWriter();
//Apply transformation with no arguments and dump results to StringWriter.
objXSL.Transform(objDoc.CreateNavigator(),null,writer);
//Set TreeView's TreeNodeSrc property to get XML from StringWriter.
// tvTest2 is the TreeView control.
tvTest2.TreeNodeSrc = writer.ToString();
//Bind TreeView
tvTest2.DataBind();
//Close StringWriter
writer.Close();
Some notes:
This code works fine if I don't insist on having a product as a sub product
to multiple products. The drel.Nested = true makes it so easy.
However,
this won't work like I want it. I've tried setting ds.EnforceConstraints =
false, but that only throws an error when trying the ds.GetXml().
I found the methodology at this URL:
http://aspalliance.com/joteke/treeviewarticle/article.aspx
Can anyone help me with this problem?
How do I EASILY get this kind of multidimensional parent-child relationship
converted to hierarchical XML so that the TreeView control can display it?
- Björn
I have an urgent problem concerning ASP.NET, ADO.NET, SQL Server, XML and
the TreeView control.
I have two tables; one describing the products and one describing their
relationships. A product may consist of multiple other products. So far
everything is great. But one product may exist as a subproduct to multiple
parent products.
My tables:
Products(prod_id, nam)
ProductRelation(prod_id, parent_id)
My relation:
Product 1 consists of Product 2 and Product 3.
Product 4 consists of Product 5 AND Product 3.
My data in the table ProductRelation:
product 2, product 1
product 3, product 1
product 5, product 4
product 3, product 4 // This is what causes the error; product 3.
My intentions:
Retrieving the data from a MS SQL Server 7 and transforming it into XML, a
TreeView control is to be used to display the hierarchical data. The problem
is that I don't know how to make this work. Storing it and retrieving it is
of no problem, but the fact that product 3 is nested in two different places
makes it a bit hard.
Think of it as a manufacturer who makes different products as tables and
chairs and nails. Nails are used in both tables and chairs...
My code:
//DataSet
DataSet ds = new DataSet("COMPONENTDS");
//Fill DataSet
da.Fill(ds, "COMPONENT");
// Create a recursive data relation.
DataColumn col1 = ds.Tables[0].Columns["prod_id"];
DataColumn col2 = ds.Tables[0].Columns["parent_id"];
DataRelation drel = new DataRelation("PRODUCTS_RECURSIVE", col1, col2,
true);
drel.Nested = true;
ds.Relations.Add(drel);
//XmlDocument to hold XML generated from DataSet
XmlDocument objDoc = new XmlDocument();
//Load XML
objDoc.LoadXml(ds.GetXml());
//Create XslTransform object
XslTransform objXSL = new XslTransform();
//Load XSLT stylesheet
objXSL.Load(Server.MapPath("hierarchy.xslt"));
//StringWriter to temporarily hold result of the transformation
StringWriter writer = new StringWriter();
//Apply transformation with no arguments and dump results to StringWriter.
objXSL.Transform(objDoc.CreateNavigator(),null,writer);
//Set TreeView's TreeNodeSrc property to get XML from StringWriter.
// tvTest2 is the TreeView control.
tvTest2.TreeNodeSrc = writer.ToString();
//Bind TreeView
tvTest2.DataBind();
//Close StringWriter
writer.Close();
Some notes:
This code works fine if I don't insist on having a product as a sub product
to multiple products. The drel.Nested = true makes it so easy.
However,this won't work like I want it. I've tried setting ds.EnforceConstraints =
false, but that only throws an error when trying the ds.GetXml().
I found the methodology at this URL:
http://aspalliance.com/joteke/treeviewarticle/article.aspx
Can anyone help me with this problem?
How do I EASILY get this kind of multidimensional parent-child relationship
converted to hierarchical XML so that the TreeView control can display it?
- Björn