|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Filling tableadapaters in master/child datasetI 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 Flomo,
Just fill them both seperately with the related ID fields in the where clause in the same dataset Cor Show quote "Flomo Togba Kwele" <flomo@community.nospam> schreef in bericht news:MPG.20566ea41ef507c6989697@news.covad.net... >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 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 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 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? 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 Show quote "Flomo Togba Kwele" <flomo@community.nospam> schreef in bericht news:MPG.20576fcde4a972c5989699@news.covad.net... > 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? 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? 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. 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  |
|||||||||||||||||||||||
Other interesting topics