G
Guest
I have a dataset that contains two tables - Parent and Child. The key of the
W
William \(Bill\) Vaughn
We have discussed this many times so first search the archives for
suggestions. David Sceppa wrote a whitepaper on how to handle these issues
and included details in his book. I also wrote a whitepaper on handling
@@Identity issues that might be helpful. See www.betav.com\articles.htm.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________
suggestions. David Sceppa wrote a whitepaper on how to handle these issues
and included details in his book. I also wrote a whitepaper on handling
@@Identity issues that might be helpful. See www.betav.com\articles.htm.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________
S
Sahil Malik [MVP]
Stephen,
The MSDN Documentation says
DataAdapter.Update(DataSet) -- Will call commands on all rows in the
dataset.
That is Incorrect. (Yes the MSDN docs are incorrect on this, sorry - but hey
look at it this way, 99.999% of the times the docs ar right .. hehe
)
So what do you do?
First the truly accurate behavior of DataAdapter.Update(dataset) <--- This
will call the I,U,D commands on the rows of the table identified by "Table"
<-- usually the first table in the dataset unless you have specified
TableMappings, and it will ignore others.
Your approach of specifying cascade on the FK is correct, so you are on the
right path. However, persisting relational data is a pain in the booty. This
is a frequently asked question, so I wrote a long blog post about it
sometime back - the rest of your answer is right here -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/06/62893.aspx
BTW - I know you will cry out loud about the fact that DataAdapter.Update
should have taken care of it all for you? The truth however is, that it just
cannot. Given that there are an infinte number of relation possibilities,
and the exact I,U,D logic depends on the relation structure, it is
impossible to write an object and have it perform well that will take care
of all circumstances.
BTW, This is covered in my upcoming book - Chapter #10 & 11.
- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
The MSDN Documentation says
DataAdapter.Update(DataSet) -- Will call commands on all rows in the
dataset.
That is Incorrect. (Yes the MSDN docs are incorrect on this, sorry - but hey
look at it this way, 99.999% of the times the docs ar right .. hehe
)So what do you do?
First the truly accurate behavior of DataAdapter.Update(dataset) <--- This
will call the I,U,D commands on the rows of the table identified by "Table"
<-- usually the first table in the dataset unless you have specified
TableMappings, and it will ignore others.
Your approach of specifying cascade on the FK is correct, so you are on the
right path. However, persisting relational data is a pain in the booty. This
is a frequently asked question, so I wrote a long blog post about it
sometime back - the rest of your answer is right here -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/06/62893.aspx
BTW - I know you will cry out loud about the fact that DataAdapter.Update
should have taken care of it all for you? The truth however is, that it just
cannot. Given that there are an infinte number of relation possibilities,
and the exact I,U,D logic depends on the relation structure, it is
impossible to write an object and have it perform well that will take care
of all circumstances.
BTW, This is covered in my upcoming book - Chapter #10 & 11.
- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
G
Guest
Thanks for the replies but managed to crack it..... fairly simple really but
isn't it always when you get the answer !
The Edit Relation dialog within the dataset designer can't be set to
Cascade, but needs to be set to Default (?!?), and the Insert command to the
Parent table must be a store procedure which does the insert then a SELECT
@NewID = SCOPE_IDENTITY() command, with @NewID being defined as an output
parameter both in the store procedure and the parameter collection of the
insert command.
This returns the actual value of the identity column in the database and
because the relationship is set within the dataset designer the change
automatically cascaded !!!!
....oh yes, one thing that no documentation seems to state but the insert
command object must have .UpdatedRowSource = UpdateRowSource.Both set even
though this is supposed to be the default.
That's one hurdle, on to the next now...
isn't it always when you get the answer !
The Edit Relation dialog within the dataset designer can't be set to
Cascade, but needs to be set to Default (?!?), and the Insert command to the
Parent table must be a store procedure which does the insert then a SELECT
@NewID = SCOPE_IDENTITY() command, with @NewID being defined as an output
parameter both in the store procedure and the parameter collection of the
insert command.
This returns the actual value of the identity column in the database and
because the relationship is set within the dataset designer the change
automatically cascaded !!!!
....oh yes, one thing that no documentation seems to state but the insert
command object must have .UpdatedRowSource = UpdateRowSource.Both set even
though this is supposed to be the default.
That's one hurdle, on to the next now...