Working with many to many data

[Brian Henry]

Before i explain this because of a strange situation we're haveing with data
we import from a 3rd party this Many to many relationship has to exist, so
it can not be replaced.

With that said, we have two tables, with a junction table in the middle (a
many to a many relationship) these names are just for example of course. The
first table called Carriers and the second table called coverages. with a
junction table called CarriersJunCoverages. Here's an example DDL really
qucik

Carriers
======
CarrierID PK Number Identity
Name

Coverages
========
CoverageID PK Number Identity
Name

CarrierJunCovearges
================
CarrierID PK (Composite PK)
CoverageID PK (Composite PK)


Now, If I have a Carrier, how do I go about retrieveing all the coverage
rows related to it? I set up relations of course on the dataset between them
in the correct directions with coverages and carriers as the parent table
and carrierjuncoverages as the child table for both..

Say I say give me all the coverages for the carrier with CarrierID = 150,
how would I retrieve those in ADO.NET? a dataview seemed good up to the
point you cant join in dataviews to my understanding.. how would you guys
deal with this? thanks

 

[Kevin Yu [MSFT]]

Hi Brian,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get coverages according to
CarrierID in an many to many relation. If there is any misunderstanding,
please feel free to let me know.

Based on my experience, this can be achieved in many ways. Generally, since
you know the CarrierID is 150, you can get all the rows with CarrierID=150
using dataset.CarrierJunCoverages.Select("CarrierID=150");. The Select
method returns an array of DataRows.

Then for each DataRow in the array, we pick up the CoverageID and find the
corresponding row in Coverages table with
dataset.Coverages.FindByCoverageID() method.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Brian Henry]

I need to data bind the results though... thats kind of my problem... so
every coverage for the carrier needs to show up in a listbox, when the user
selects a coverage from a combo box.

 

[Kevin Yu [MSFT]]

Hi Brian,

Since the our last step returns an array of DataRows, we can set the array
as ListBox's DataSource directly. Here are the code. Assume that all the
DataRow references are in the array dr.

this.listBox1.DataSource = dr;
this.listBox1.DisplayMember = "Name";
this.listBox1.ValueMember = "CoverageID";

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Brian Henry]

I figured it out, thanks!