many to many relationship

[Manjree Garg]

Hi
I am developing a Sql Server 2005 database and using ADO.Net to access
it in VC++ 2005. Is there some way of defining many-to-many relationship
between two tables(e.g. Experiment and SampPrep as one experiment can have
many samplePrep amd many experiments can be performed on one samplePrep).

Any suggestions will be appreciated.


Manj.

 

[W.G. Ryan]

Manj:

Sure, but if you are trying to databding to it, I've had some trouble. Are
you simply asking if it's doable or are you looking for a binding example?

 

[Manjree Garg]

Hi Ryan

Thanks for the reply. I need to do it so I am looking for the example.
I've got two tables SampPrep and Experiment for many to many relationship and
the ExpData that contains data for each experiment on each sampPrep.

Manj.

 

[WenYuan Wang [MSFT]]

Hello Manj,

According to your description, you have tree tables: SampPrep, Experimement
and ExpData. There is many to many relationship between SampPrep and
Experiment. ExpData hold the reference to each of the table and store
many-to-many relationship. If I misunderstood anything, please correct me.

Many to many relationship is not native supported in ADO.net so far. If we
want to define this type of relationship, we should have to add some
additional relationship between them. (Such as: SampPrep-ExpData and
Experiment-ExpData).

System.Data.DataRelation dr1 = new DataRelation("SampPrep-ExpData",
SampPrep.columns[SampPrepID],ExpData.Columns[SampPrepID]);
System.Data.DataRelation dr2 = new DataRelation("Experiment-ExpData",
Experiment.columns[ExperimentID],ExpData.Columns[ExperimentID]);

Hope this helps. Feel free to update here again, if there is anything
unclear. We are glad to assist you.

Have a great day,
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Manjree Garg]

Hi Wen

Thanks for your reply.
How would I define mant to many relationship between SampPrep and Experiment?

regards,
Manj.

 

[WenYuan Wang [MSFT]]

Hello Manj,
Thanks for your reply.

As we discussed, we can add a third table which hold the reference to each
of the table and store many-to-many relationship. Then, in ADO.Net, we
define two relationships between these tables.

Do you face any further issue on this? I'm confused. Could you please let
me know what 's your main concern so far?

The following document introduced how to define many-to-many relationship
in SQL server. If you are not sure about how to define many-to-many
relationship in SQL server, I believe this article will help.
http://msdn2.microsoft.com/en-us/library/aa176955(SQL.80).aspx
[How to create a many-to-many relationship between tables (Enterprise
Manager)]

Please feel free to update here again, if there is anything unclear. We are
glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Manjree Garg]

Hi Wen

Thanks for your reply. I was planning to add many to many relationship into
my database earlier but then I rearrange it without it.

Any way your reply helped me to know how to do it.

Cheers.

Manj.

 

[WenYuan Wang [MSFT]]

Hello Manj,

Thanks for your reply.

Have a great day,
best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.