Selecting Data

  • Thread starter Thread starter eric
  • Start date Start date
E

eric

I am currently struggling working with data in multiple tables. Is it
better to have a single join based query versus seperate queries? I am
trying to fill a typed dataset with two tables with a one to many
relationship which is similiar to Order-Order Details relationship. When I
pass my dataset to be filled in my data access layer using a single joing
based query it only loads the data in the parent table in the dataset and
not the order details table (child). When I display it in a datagrid it
shows the data in the order details table however only with the order table
not in the order details table. What am I doing wrong?

Thanks
 
Does your Select Statement have the word JOIN in it? If so, this is the
problem. The dataset is rejecting what doesn't fit. You need to do two
separate queries filling the parent table with its adapater and the child
with its adapter. If you have a typed dataset with the Relation defined,
then that should be it b/c the DataRelation is already defined.

In general, avoid SQL Statements with the word JOIN in them in ADO.NET
unless you are positive you want to do otherwise.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
William,

No I am not using "join" in my query. My query is similar to this:

CREATE PROCEDURE GetOrders

@OrderID varchar(10)



AS

SELECT *

FROM Orders O, OrderDetails Od

WHERE O.OrderID = @OrderID AND Od.OrderID = O.OrderID

GO



I am trying to load it into a typed dataset with tables: Order and Order
Details with a data relation using OrderID. I thought that the data
relation would take care of the mapping of data. So, do I need to do two
seperate queries to fill each table or can I use one query like above to
load data into both tables?



Thanks
 
That's actually a Join, just doesn't use the Join Syntax. Yep, you'll need
to do two seperate queries. Remember that the dataset doesn't care where it
got its data from. One table could be a .csv file another an excel file and
possibly another child table that didn't come from anywhere, that you built
programmatically. As such, it's only concerned with Local data, the data in
the Datatables. The adapter's job is to move data around for you in between
places.. so you could have one adapter for instance move your data from a
Oracle DB to a DataSet, then another move that data from the dataset to an
excel sheet, SQL Server db or back to the oracle db. The Adapter moves
everything. So you'll need another proc or select statement.

Did you build the adapter yourself? Normally if you use the Configuration
wizard, it will warn you of these types of issues..nothing that does
autogeneration can populate and update if a Join is in effect - it can
populate but not update. in this instance, the other table in the join is
being rejected. As such you need two adapters, or you'll need to change
your adapters select command each time. Too much work to be worth it. You
can roll your own logic that might handle updates but it's not worth it.
Moreoever, you won't pull over redundant data if you use a relation like you
would if you used the join. Perfomance wise the two selects will probably
be faster all around.

HTH,

Bill

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
Back
Top