DataRelation w/ many-to-many, still have questions..

[sklett]

A Google Groups search on this topic returns many unanswered posts, so I
must not be the only one

I have a simple many-to-many situation;

[ tbl_Shots ]
pk_shotID

[ tbl_Assets ]
pk_assetID

[ tbl_shotAssets ]
fk_shotID
fk_assetID


Given that schema, I would like to get the asset records for a given shot
using DataRelation objects.
I have a sproc that will get all three tables as separate tables in a
DataSet. What I'm having a hard time putting together is how to get the
asset records from the DataSet?

Let's say I have a DataRow in the tbl_Shots that I want the Asset records
for.... how can I have a DataRelation that will return the asset records?
A simple many-to-many DataRelation example would help me sort this, I'm sure
of it.

Thanks for reading,
Steve

 

[Sahil Malik]

I just answered this question this morning (albeit in greater detail than
below) ---

You wanna do

tbl_shotsrow.GetParentRow ----> this would give you a row in tbl_shotAssets.
... lets call this row RowX

and then do RowX.GetChildRows() .. this obviously assuming you have the
proper relations setupin your dataset.

Bingo ..

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Sahil Malik]

Below is a much more detailed answer I had given this morning .. (William
you are right, this question does come up a lot).

For a lack of a better word in what I'd like to call classic ADO.NET - which
uses simple dataset with relations and maps an RDBMS functionality, I don't
think there is another way of doing a many to many relationship without
having a mapping table in the middle.

So, your many to many relation is between A and B, You'd have an AB, with a
1 to many between AB and A .. and between AB and B.

Then given an A row, you could do GetParentRow, and then GetChildRows from
the row you get from AB.

So much for that ... there is however, within the confines of ADO.NET
another way of doing Many to Many relationships - which indeed doesn't use a
mapping table. You can specify many to many relations in an XSD - viz it'd
look like something as follows -

<xsd:Schema>
<xsd:complexType name="A">
<xsd:sequence>
.......
<xsd:element name="BEntries" type="B"
minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>

<xsd:complexType name="B">
<xsd:sequence>
.......
<xsd:element name="AEntries" type="A"
minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:Schema>

Something like the above where "...." denotes the extra columns.

Ok good .. now that you have a good XSD representation for your object, you
could then use either SqlXML, or basically SqlXmlDataAdapter to pass in
"updategrams" into the database. That way, you could get around creating a
mapping table for Many to Many relations. Also to work with the above data,
using a rather simple xslt transform, you could very easily flip from A
based XML to a B based XML (know what i mean? .. well an XML that you can
browse using A rows, versus one that you can browse using B rows).

You can find more details on that in a book I have written (ISBN:1590594347)
through Apress in Chapter #12.

Regards,

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik

A Google Groups search on this topic returns many unanswered posts, so I
must not be the only one

I have a simple many-to-many situation;

[ tbl_Shots ]
pk_shotID

[ tbl_Assets ]
pk_assetID

[ tbl_shotAssets ]
fk_shotID
fk_assetID


Given that schema, I would like to get the asset records for a given shot
using DataRelation objects.
I have a sproc that will get all three tables as separate tables in a
DataSet. What I'm having a hard time putting together is how to get the
asset records from the DataSet?

Let's say I have a DataRow in the tbl_Shots that I want the Asset records
for.... how can I have a DataRelation that will return the asset records?
A simple many-to-many DataRelation example would help me sort this, I'm
sure
of it.

Thanks for reading,
Steve