getting data from multiple tables

[cjard]

... i cant see a walkthrough (http://msdn2.microsoft.com/en-US/library/wzabh8c4(VS.80).aspxhttp://msdn2.microsoft.com/en-US/library/wzabh8c4(VS.80).aspx) for feching data into the app from 2 related tables..


I know i can create a tableadapter associated with one or another table, and this TA can have an associated query that performs a join but then its an issue of encapsulation for me...

suppose i have Customers and Orders tables and i want to show a customer and his orders on my form.. and my DataSet has a DataTable for each database table and a DataRelation object has been created between the two.. so the dataset knows of how to join the two tables..

where does the join go? Do i call GetData() on the customers with 123 as the customer id, then call GetChildRows() to pull the related orders? i.e. the database doesnt do the join - i get the customer details (and suppose he has customer ID 123) then make another db call (for the orders) and get the orders for customer ID 123. or do i call GetData() on the orders table adapter, again passing the 123 customer id..
what if there are multiple links to this table, what do i get back if i use get child rows?

Or suppose i perform a join in a query and have it added to one or the other tableadapters can call it GetCustomerAndAllOrders(byCustomerID)
which table adapter should contain it, customers or orders? why?

how do these both go with the notion of having a Form, and drag/dropping fields out of the data sources window and onto the form, binding them to the data and having binding navigators to scroll it. anyone have any good walkthroughs on this, for multiple tables?

there are walkthroughs for writing data back into multiple tables, but i havent seen anything for advice on how to read data out of multiple tables. my instinct tells me to create a query that does the join.. so i can create a view on the DB and treat it like a table, import it into my data set and bind the controls to it.. and for updates i can write custom Insert/Update queries attached to the tableadapters for the individual tables (also members of the data set) and call those to do the updates..
but is this the way it is done? am i making somethig too hard for myself here when i should maybe jsut be calling someDataSet.InsertNewRows() instead of calling ordersTableAdapter.myCustomInsertCommand(txtOrderItem.Text, txtQuantity.Text, txtBlah.Text)

matt

 

[cjard]

When getting data from multiple tables..

...where do we do the join?

For more info, i posted a complete question here in VB.NET Data Access forum:
http://www.vbdotnetforums.com/showthread.php?t=10071

if you could reply there i'd much appreciate it!

matt

 

[Jman82]

Joins, Relationships, ChildRows, DataGrid

I'm assuming you are using a DataGridView to view your data. A trick use to do some debugging is to use the old DataGrid instead (right click on your toolbox and click choose items...). The Nice thing about the dataGrid is that you can set the datasource to the dataset and it allows you to browse all the dataTables and shows the relationships as well. So you can see what data is returned when you getChildrows for customer 123.

the cheap and dirty way to do this is to use the dataSet wizard:
1) Create a connection to your database and select the tables you want to use
2) drag a table into your dataGrid from your dataSource window.
3) then go to desiner code (you will have to be able to see all files) and comment out the line of code that sets the dataMember of the table binding source.
4) Run your program you should see the DataGrid with a plus box in the corner, click the plus and you should see your tables
(Note: It will not contain your data unless youcall the tableAdapter.fill command for each table)

basically in your example if i wanted to get All Orders for customer 123 the code would be

ds.Tables("tblCustomers").Rows.Find("123").getChildRows("rlnCustomerOrders")

this will return an array of DataRow which you can merge into another dataSet or display.

Reading your question again it sounds like what you are trying to do is dispaly all the Customer info and Order info in the same Place. A join query would be easiest but then you will be displaying the Customer info over and over again for each of his orders.

Hope I'm somewhere in the ball park of answering your question.

 

[cjard]

i wrote a fairly long response, and then went and clicked a link in an email, which loaded another page and wiped my text.. drives me bonkers!


thanks for these tips.. im actually using details view, but i can still follow your sugestions..

after trying several methods (though i didnt try yours, and its a gem of advice i can use later) i settled on one microsoft dont condone..

if i have a form showing customer details, then i'll make a view in the db that returns a value for everything I want to put on the form, and use that to fetch the data. because im in details mode, it wont matter that some info is repeated because it will look static.. i'll just have to update all the datatable if the user edits a particular field in one of the repeated records (so it doesnt look like his edits are lost if the user navigates)


-

actually, scratch that, because I just had a bit of an epiphany and i got soemthing working as i expected.. heres how we show related data in an app (you wont believe it.. oh no! )

we have customers and orders tables
we drag them onto the dataset designer
we create a relationship between them
we flick to form design and open the datasources window
we find the parent table (customers) in the data sources window, switch it to details and drop it on the form (some nice controls appear for us)
we drop the Orders table (grid mode) from the hierarchy (underneath customers) in the data sources window, onto the form
more things appear, notably a bindingsource ofr orders that is bound to the relationship between the 2 tables

at this point i was starting my app and wondering why it never filled the table from the database, based on the relationship..
well, its not supposed to.. seems what you then have to do is (par example)

in code, fill the orders datatable with EVERYTHING out of the database.. all million orders if they are there.. now the relationship will find the rows for you automatically!


so i'm needing a way to maybe arrange this fill (which i will be doing manually) more automatically.. does anyone know?

cause basically i cant see the point of making a relationship between 2 tables if my code is going to look like this, to avoid downloading all the database:

Me.DataSet1.CustoemrsDataTable.FillByID(123)
Me.DataSet1.OrdersDataTable.FillByCustomerID(123)