Folks I have a table with Parent Child and grand child relationship
How can I retrieve ONE parent and all descendants and/or TWO parents
with their
respective descendants(1 and/or 2)
Below is my C# code to achieve this
****************************************************************************************
queryXml is BELOW
<query><TIDS><TID><ID>200</ID></TID><TID><ID>221</ID></
TID><TID><ID>398</ID></TID></TIDS></query>
public XmlDocument GetTemplatesXmlByID(string queryXml)
{
XmlDocument xmlReturnDoc = new XmlDocument();
XmlDocument xmlDocument = new XmlDocument();
StringBuilder sb = new StringBuilder();
XmlElement TemplatesNode =
xmlReturnDoc.CreateElement("Templates");
xmlDocument.LoadXml(queryXml);
try
{
sb.Append("SELECT p.TemplateId, p.TemplateLevel,
p.TemplateOrder, c.TemplateName ");
sb.Append("FROM DOCUMENTS_TEMPLATES p INNER JOIN
DOCUMENTS_TEMPLATES c ");
sb.Append("ON p.TemplateId = c.TemplateParent ");
XmlNodeList TIDNodes = xmlDocument.SelectNodes("query/TIDS/
TID");
for (int i = 0; i < TIDNodes.Count; i++)
{
if (TIDNodes.Count > 1)
{
XmlNode TNode = TIDNodes[i];
int TemplateId =
Int32.Parse(TNode.SelectSingleNode("ID").InnerText);
if (i == TIDNodes.Count)
{
sb.Append(" WHERE p.TemplateId = ");
sb.Append(TemplateId);
}
else
{
sb.Append(" WHERE p.TemplateId = ");
sb.Append(TemplateId);
sb.Append(" And ");
}
}
else
{
XmlNode TNode = TIDNodes[i];
int TemplateId =
Int32.Parse(TNode.SelectSingleNode("ID").InnerText);
sb.Append(" WHERE p.TemplateId = ");
sb.Append(TemplateId);
}
}
sb.Append("ORDER BY p.TemplateOrder ");
AcDataAccess.Selecter selecter = new
AcDataAccess.Selecter();
using (IDataReader dr = selecter.select(sb.ToString()))
{
while (dr.Read())
{
XmlElement TemplateDoc =
xmlReturnDoc.CreateElement("Template");
TemplatesNode.AppendChild(TemplateDoc);
XmlElement TemplateIDDoc =
xmlReturnDoc.CreateElement("TemplateID");
TemplateIDDoc.InnerXml =
dr["TemplateID"].ToString();
TemplateDoc.AppendChild(TemplateIDDoc);
XmlElement TemplateLevelDoc =
xmlReturnDoc.CreateElement("TemplateLevel");
TemplateLevelDoc.InnerXml =
dr["TemplateLevel"].ToString();
TemplateDoc.AppendChild(TemplateLevelDoc);
XmlElement TemplateOrderDoc =
xmlReturnDoc.CreateElement("TemplateOrder");
TemplateOrderDoc.InnerXml =
dr["TemplateOrder"].ToString();
TemplateDoc.AppendChild(TemplateOrderDoc);
}
}
}
catch (Exception ex)
{
AcXmlClasses.AddLogEntry("Error when calling
GetTemplatesXmlByID: " + ex.ToString(), 5);
return AcException.GetXmlException(ex);
}
return xmlReturnDoc;
}
****************************************************************************************
