Filling tableadapaters in master/child dataset

[Flomo Togba Kwele]

I have defined a strongly-typed dataset with two tableadapters in a
master/child relationship. On the master tableadapter, there is a parameter
which restricts the fill of the tableadapter to less than the entire contents
of master table.

After filling the master table, do I simply fill the child tableadapter with a
single call and it magically "knows" to fill the child based on the
relationship, or do I have to walk the master table rows and fill the child
multiple times based on the rows of the master?

Sorry for this ignorant question, but I am lost here.

Thanks, Flomo

 

[Cor Ligthert [MVP]]

Flomo,

Just fill them both seperately with the related ID fields in the where
clause in the same dataset

Cor

 

[WenYuan Wang]

Hi Flomo,

After filling the master table, ADO.net will not magically know to fill
only child based on the
Relationship. All the rows in child table will be filled into dataset if
there is no constraint in Dataset, otherwise, you will received an
exception such as "Failed to enable constraints. One or more rows contain
values violating non-null, unique, or foreign-key constraints."

About how to resolve this issue, as Cor said, you can use separately query
with the related ID fields in the where clause to fill the data both in
Master table and Detailed table.

Please feel free to let me know if there is anything unclear.

Have a great day.
Sincerely,
Wen Yuan

 

[Flomo Togba Kwele]

Thanks for both your replies. I want to make sure I understand your
explanations.

The master table has a parameter, the child does not.

Master Child
-------------------
ColParam ColX
ColA_ID(PK) ColY(FK)
ColB ColZ

I have to pass the Fill method of the Master Table the value of ColParam, which
gives me a collection of Master rows. But I only want the children of those
selected master rows where ColParam=@ColParam. I think you are saying that the
fill of the child tableadapter must parameterize the foreign key from the
master table.

taMaster.Fill(dsMaster.MasterTable, ParmValue)

taChild.ClearBeforeFill = False
For Each row As dsMaster.MasterRow In dsMaster.MasterTable.Rows
taChild.Fill(dsChild.ChildTable, row.MasterID)
Next

Is this correct?

 

[Cor Ligthert [MVP]]

Flomo,
Two SP's like this, or just text SQL commands.

dbo.uspGetMyMaster
---------------------------------------
int ColParam

Select * from Master Where ColParam=@ColParam

dbo.uspGetMyChild
---------------------------------------
int ColParam

Select * from Child Where ColParam=@ColParam

Cor

 

[WenYuan Wang]

Hi Flomo,

Walking through each row of the master table and filling the child table
according to the related ID is a method to achieve this. Another
suggestion, for some performance issues, you may modify the SQL query
command of Child table only to return all rows by calling fill method once.
Such as:
select Child.ColX, Child.ColY, Child.ColZ from Master, Child where
Master.ColA_ID= Child.ColY and Master. ColParam = @Param

If there is anything unclear, please feel free to let me know. We are glad
to work with you.
Have a great day,
Sincerely,
Wen Yuan

 

[Flomo Togba Kwele]

Thanks to both of you for your replies.

I setup the query in the child table to do a join to the master table and then
was able to supply the query with the parameter used for the master table.

I am using strongly-typed datasets. When I altered the query by accessing the
master table via the join, the wizard said it could not generate Insert, Update
and Delete methods because "Dynamic SQL generation is not supported against
multiple base tables".

How can I work around this?

 

[Flomo Togba Kwele]

Sorry, I figured it out.

You have to setup the Select, Insert, Update and Delete using the standard
query, and afterwards Add another query accessing other tables.

Thanks to all for your help again.

 

[WenYuan Wang]

Hi Flomo,

That is, as you see, adding a new fill2() method and using standard Select,
Update and Delete command can resolve this issue.
So glad to hear you have resolved the issue. You are welcome. Please feel
free to let us know if you meet any further issues. We are glad to assist
you.

Have a great day,
Sincerely,
Wen Yuan