khorne Posted November 5, 2004 Posted November 5, 2004 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 :) Quote
kejpa Posted November 5, 2004 Posted November 5, 2004 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 Quote
Reidar Lange Posted November 5, 2004 Posted November 5, 2004 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. Quote
cpopham Posted November 9, 2004 Posted November 9, 2004 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 Quote ____________________________________________ http://www.pophamcafe.com I am starting a developers section, more tutorials than anything.
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.