Sorting by child relationship in a dataset

  • Thread starter Thread starter lovemyalpacas
  • Start date Start date
L

lovemyalpacas

Hi,

I have a dataset with a parent and child table, as follows:

Parent Table: 'Asset'
- AssetID
- Name

Child Table: 'Attribute'
- AssetID
- Name
- Value


I need to sort the items in the parent table by a certain value in the
child 'Attribute' table i.e Sort Asset by Attribute.Value where
Attribute name = 'Date'

Hopefully this makes sense - and hopefully some one can give me a clue
as to how to achieve this. Despite large amounts of googling i'm yet to
find an example!

Thanks in advance.
 
The most I can come up with is that a DataView sort property only takes a
list of columns, not an expression so by adding an expression
column to the parent table and sorting on that, it would work.

A C# example of this would be

DataSet set = new DataSet("MyDataSet");

DataTable t1 = set.Tables.Add("T1");
DataColumn parent = t1.Columns.Add("AssetID", typeof(int));
t1.Columns.Add("Name", typeof(string));
t1.PrimaryKey = new DataColumn[] { parent};

DataTable t2 = set.Tables.Add("T2");
DataColumn child = t2.Columns.Add("AssetID", typeof(int));
t2.Columns.Add("Value", typeof(int));

DataRelation r = new DataRelation("R1", parent, child);
set.Relations.Add(r);

// since there can be a many to 1 relation between child & parent
t1.Columns.Add(new DataColumn("sortval", typeof(int),
"Max(child(R1).Value)"));

t1.Rows.Add(1, "D");
t1.Rows.Add(2, "A");
t1.Rows.Add(3, "B");

t2.Rows.Add(1, 3);
t2.Rows.Add(2, 2);
t2.Rows.Add(3, 1);

DataView view = new DataView(t1, "", "sortval", DataViewRowState.Added);
foreach (DataRowView row in view) {
Console.WriteLine(row["Name"]);
}

If you had a "" for the view sort, the output would be DAB, with the sort
filter it is BAD
 
Thanks for that Mark - this is pretty much the temporary approach I had
come up with whilst trying to find a better solution - but I'm now
thinking this is the best way!

Cheers
Mark Kelly
 
You can add a calculated column with an Expression of:

child(AssetAttributes).Value

.... and sort on that.

I've assumed that you have an existing DataRelation between the two
tables, called "AssetAttributes".

I, for one, would *love* if MS would add the ability to specify an
expression as the 'sort' parameter to DataTable.Select. It would solve a
few problems for me.
 
D'oh! Ignore this. I was thinking of Parent(), rather than Child(). The
idea below won't work at all.

Why is it always just after I press the "Send" button that I realise
I've just written a totally rubbish response? :-)
 
Hi,

A normal parent childrelation in AdoNet goes over the description of the
datarelation which describes the relations between rows. The first step you
can take is to get the parentrows.

http://msdn.microsoft.com/library/d...fsystemdatadatarowclassgetparentrowstopic.asp

Your problem is than that you cannot sort the result what is a collection of
datarows.

However, you can "clone" your parent table.

http://msdn.microsoft.com/library/d...l/frlrfSystemDataDataTableClassCloneTopic.asp

Add to that the gotten rowcollection.

You have to use for that the datatable.rows.add(dr) row by row in a loop

Set to that datatable the sort parameter of the defaultview as you want and
process than the table using the defaultview.

http://msdn.microsoft.com/library/d...fsystemdatadatatableclassdefaultviewtopic.asp

I hope this helps,

Cor
 
LOL ;-) been there done that :-P

SM


mabster said:
D'oh! Ignore this. I was thinking of Parent(), rather than Child(). The
idea below won't work at all.

Why is it always just after I press the "Send" button that I realise I've
just written a totally rubbish response? :-)
Click to expand...
 
Back
Top