Many-to-Many Datasets

[khorne]

Hi,

 

I'm relatively new with ADO .NET and am having some trouble setting up my tablets and relationships in ado, can't find find much info out there on many-to-many relationships.

 

I'm using the MySQL Connector/.NET to access my DB and have set up a connection, it's just an implementation of the .net DB objects, so it has all datatadapter objects etc etc.

 

My tables are as follows, suppliers_brands linking the two tables, ie. a supplier can have many brands, a brand can have many suppliers.

 

supplier

-------

supplier_id

supplier_name

 

suppliers_brands

----------------

supplier_id

brand_id

 

brand

-----------

brand_id

brand_name

 

 

I've been able to fill a dataset with the 3 tables; my question is how do do I define the relationships (in code) so that I can select all the brands (the actual brand names) associated with a given supplier_id or vice versa (suppliers associated with a brand_id).

 

Thanks for any tips :)

[kejpa]

  Quote

my question is how do do I define the relationships (in code) so that I can select all the brands (the actual brand names) associated with a given supplier_id or vice versa (suppliers associated with a brand_id).

Hey!

Use ordinary SQL...

 

All brands for SupplierID= 1:

SELECT b.* FROM Brands b INNER JOIN Supplier_Brands sb ON b.BrandID=sb.BrandID WHERE sb.SupplierID=1

 

All suppliers for BrandID=1

SELECT s.* FROM Suppliers s INNER JOIN Supplier_Brands sb ON s.SupplierID=sb.SupplierID WHERE sb.BrandID=1

 

 

hth

Kejpa

[Reidar Lange]

To add relations in a dataset you have twoo options:

1. You can use a typed dataset and add both tables ans relations in the dataset designer that comes with visual studio. (There is even a faste way to update typed datasets - that is if you have defined the adapters in the designer instead of by hand. Then you can right click on the adapter and select create dataset....

 

2. You can add realtions programatically. use the dataset.Relations.Add method and look up System.Data.DataRelation in the help docs.

 

Having set up the dataset, you can call DataRow.GetChildRows

 

Example from vs helpdocs:

private void GetChildRowsFromDataRelation(DataTable myTable ) {
   DataRow[] arrRows;  
   foreach(DataRelation myRelation in myTable.ChildRelations){
      foreach(DataRow myRow in myTable.Rows){
         arrRows = myRow.GetChildRows(myRelation, DataRowVersion.Proposed);
         // Print values of rows.
         for(int i = 0; i < arrRows.Length; i++){
            foreach(DataColumn myColumn in myTable.Columns){
               Console.WriteLine(arrRows[i][myColumn]);
            }
         }
      }
   }
}

 

 

The coding is even easier if you have a typed dataset. You realy should check that out. It's very neat.

 

Hope this helps. If you are stuck still. Pleas let me know and I'll dig up some example code for you.

[cpopham]

Will .net even let you do m-n relationships? You probably should create a bridge datatable to keep down the possible data anomolies associated with m-n relationships. The bridge table should contain as its primary key the foreign keys related to the other tables which originally composed the m-n relationship.

 

Chester