[RESOLVED] DataRows and DataSet.Relations

[dummy_pmgr]

Hello Guys,

Here I am again .......

I am trying to do some parent and child stuff. It involves with just a single table.(idea behind is I dont want to create lots of table to do this job). Well my table has got ParentID, Name, ChildID. It is not nesscessary that each parent has child ids, some parents have childs and some have not. I have these data retun as a data set. I want to display these data as follows........


I want to display in my aspx page....

1 Parent 1
2 Parent 2
3 Parent 3
3.1 child 1
3.2 child 2
3.3 child 3
4. parent 4
4.1 child1
4.2 child2


Having said above I have written the code like as follows

private void Page_Load(object sender, System.EventArgs e)
{
DataTierDB dataTier = new DataTierDB();
DataSet dataSet = dataTier.GetParentAndChild()

//Get parent rows
DataRow[] parentRows = dataSet.Tables[0].Select("ParentID=ChildID ");

//Realationship between the parent and child
DataRelation parentChild = new DataRelation("ParentChild", dataSet.Tables[0].Columns["ParentID"], dataSet.Tables[0].Columns["ChildID"]);

dataSet.Relations.Add(parentChild);
DataRow[] childRows = parentRow.GetChildRows(parentChild);

//Get childs of each parent row
foreach(DataRow parentRow in parentRows)
{
Response.Write(parentRow["ParentID"] + ": " + parentRow["Name"] + "<br>");
foreach(DataRow child in parentRow.GetChildRows("parentChild"))
{
if (parentRow["QuestionID"] != parentRow["ParentID"])
{
Response.Write("......." + child["ParentID"] + ": " + child["Name"] + "<br>");
}
}
}
}



I am nearly there but it displays

Parent 1
Parent 1
Parent 2
Parent 2
Parent 3
Parent 3
child 1
child 2
child 3
parent 4
Parent4
child 1
child 2


I can guss what is the problem there, but I could not really get it worked.

I would really appreciate if someone help me out this.

Cheers,

[pvb]

It doesn't look like you have two tables, that's what the DataRelation is used for, relating two tables to each other: DataRelation.

Assuming you did have the two tables you would use something like this:

DataSet everyone = getEveryOne();
DataColumn parentColumn = everyone.Tables["parents"].Columns["ParentID"];
DataColumn childColumn = everyone.Tables["children"].Columns["ParentID"];
everyone.Relations.Add("ParentsChildren", parentColumn, childColumn);
foreach(DataRow parentRow in everyone.Tables["parents"].Rows)
{
    Response.Write("<b>Parent Name: " + parentRow["Name"].ToString() + "</b><br />");
    foreach(DataRow childRow in parentRow.GetChildRows("ParentsChildren"))
    {
        Response.Write("<li>Child Name: " + childRow["Name"].ToString() + "</li><br />");
    }
} 

If you need to use only one table then i suggest using DataViews instead, something like this:

DataSet everyone = getEveryOne();
DataView parentRows = new DataView(everyone.Tables[0]);
parentRows.RowFilter = "ParentID=ChildID";
foreach(DataRowView parentRow in parentRows)
{
    Response.Write("<b>Parent Name: " + parentRow["Name"].ToString() + "</b><br />");
    DataView childRows = new DataView(everyone.Tables[0]);
    childRows.RowFilter = "ParentID <> ChildID and ParentID=" + parentRow["ParentID"].ToString();
    foreach(DataRowView childRow in childRows)
    {
        Response.Write("<li>Child Name: " + childRow["Name"].ToString() + "</li><br />");
    }
} 

[dummy_pmgr]

Thanks very much pvb. Your code works great. I did not know that I can do this with dataviews. It saved me a lot of work and confusion.


Cheers.