Fundamental Multi-table DataSet population

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm looking for some guidance on filling multiple DataSet DataTables based on
a "complex" query.

Given 3 tables: Parent, ChildOne, and ChildTwo, where ChildOne.ParentID
and ChildTwo.ParentID relate to Parent.ParentID.

I would like to populate the Parent table based on a complex query across
several source database tables. Then, I would like to populate the ChildOne
and ChildTwo tables with only those rows that relate to Parent rows returned
by the main query.

Is there a mechanism in the DataSet to tell it to "Fill ChildOne based on a
list of Parent.ParentID values"?

One SQL concept is as follows:

SELECT * FROM ChildOne WHERE ChildOne.ParentID IN ( @Parent_Id_List );

Another post indicated that the DataAdapter Parameter cannot be used for IN
and CONTAINS. I've found that ParentID IN (1) works, but ParentID IN
(1,2,3) fails because "1,2,3" is interpreted as a String and ParentID is a
Decimal.

There has to be a fundamentally simple approach to populating multiple
DataTables based on nested relationships. Please help!

- CWP
 
It's TSQL that balks at permitting you to pass a string into the IN clause
in a parameter. There are several work-arounds published on the web. One
interesting approach is to pass the string to a TSQL function that returns a
Table variable that the IN clause can use. Another approach (which I don't
endorse) involves building the SELECT with the IN string in VB using
concatenation.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill,

Is the general approach of "hand-coding" the population of related table
rows via the use of foreign key queries the standard and expected approach?
(I've only previously worked with single table query responses, so this may
seem like an elementary question, but one that I need confirmed). It almost
seems like this would be a frequently used operation that a DataSet would
automate, given the knowledge contained in Relations.

Also, if you have a link to a supporting article that demonstrates your
TSQL workaround, that would be helpful. I've been hunting for a while,
resorting to this post after hitting several dead ends.

Thanks,

CW Proefrock
 
Bill - I've found the post listed below. It works, except for the fact that
the 'helpful' DataAdapter Configuration wizard changes the formatting of the
embedded literal strings, so I had to go into the code and clean-up the
literals.

I'm still interested in hearing your opinion regarding the general approach
to populating tables with related rows from a database.

- CWP

This post contains usefull information:

http://msdn.microsoft.com/newsgroup...adecc8-a0fb-494f-9424-c2f6624d6441&sloc=en-us
 
I discuss this in my book quite a bit--you might try picking up a copy. It's
a rather long discussion but it boils down to the fact that the automated
drag-and-drop tools and wizards are designed for fairly simple data access
paradigms--single table operations.
Sure, you can setup more complex SELECT queries and ADO.NET can handle them,
but the UI tools are not capable of dealing with the myriad of options one
could choose to build a rowset.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks Bill. I've reviewed your web site and express ordered your book
(ADO.NET ... C#). I've got a prototype delivery due by early next week.
Yikes!

I'll post back here after I've "educated" myself ...

- CWP
 
Back
Top