However, I notice that Chris Anderson has answered my question - you do it
with two round trips. First, INSERT the new parent row, and you get back
the new identity value. If you have an appropriate foreign key constraint
set up on your DataSet, the act of updating the parent row's identity will
have the side effect of upating any references to this row in the child
tables. So when you come to update those, they will indeed have the correct
key.
No No No - only one trip is required to the stored proc ( though it
consists of an insert and a select) . I have a working app which does this
and I think this is what Chris is trying to say . If you mean 2 trips as in
the insert / update parent row and the insert / update child row then ignore
the rest of this email . This is always the case with .NET as the
DataAdapter.Update works on one row at the time. Normally when people talk
about 2 trips and inserts they mean get the new id to the client and then
fill the rest of the record - this is not the case with DataSets.
> Ian wrote
>Not really - you know the local ID you were using before pushing the data
>back to the DB. But the dicussion here surrounded the case where that
> column was really an autoincrement column on the DB side. This means
that
> our local ID will be meaningless to the DB since it will invent its own
> identity.
Our identity is meaningless to the DB but this is not relevant when you
update the child record. Constraints update all the parent records before
they are written. This is why the order is importent.
This may help- Note the DB is not involved at this POINT .
1. Generate a Dataset to reflect youre form .
Create tables
Create youre relationships
(tables in data sets have auto incrementing value -1 and will have a
negative identity. )
create your new row which will have an identiy of -1
pass this iID to appropriate parrent or record and create them if needed.
pass this to the appropriate form
User fills in the form - as an identiy is present this is no different to
using exisitng data.
2. Pass to middleTier phase
Call GetChanges on the dataset
pass to middletier
order requests and pass to DataAdapter
3. Datalayer - DB Involved at this point.
for each parent - child
DataAdapter.Update(childdataTable) passes child rows data to
storeProcedure
Constraints update identity of parents.
DataAdapter.Update(parentdataTable) passes parent records ( which have
the
database ID) ( if needed)
4. Client update
Pass changes back to client and update original dataset.
-----Original Message-----
From: Moderated discussion of advanced .NET topics.
[mailto:[EMAIL PROTECTED]]On Behalf Of Ian Griffiths
Sent: Tuesday, 9 July 2002 12:08 AM
To: [EMAIL PROTECTED]
Subject: Re: [ADVANCED-DOTNET] ADO.NET: Parent-child relationship and
auto-increment columns
Ian Griffiths wrote:
> >
> > I can see how that might work for inserting rows into a single
> > table, but I don't understand how it addresses the poster's
> > original quetion, which involved adding a *new* parent row,
> > and a *related* child row. .....
Ben Kloosterman replied:
> I asume the problem the poster is having adding a row is due
> to not having a key. By setting values of -1 which increment
> at -1 you know the value .
Not really - you know the local ID you were using before pushing the data
back to the DB. But the dicussion here surrounded the case where that
column was really an autoincrement column on the DB side. This means that
our local ID will be meaningless to the DB since it will invent its own
identity.
So the problem is how do I go about sending the second INSERT (for the
related child) in such a way that the correct foreign key (i.e. a key that
the DB itself will recognize) is sent?
> At this point it is not relevant what the ID is as long as it does
> not match an existing ID hence the use of negative numbers.
It matters a great deal if I want to send two INSERTs, one of which adds a
new row to the parent, and the second of which is in a child table and is
RELATED TO THE NEWLY-ADDED ROW.
That bit in capitals is crucial here. The DB will have invented a new ID
for the parent row. It is vital that when I add the child row, the foreign
key for the relation to the parent table contains the ID that the DB
generated, not the ID that the DataSet generated.
> >.... Since the DB will invent its own identity column,
> > how on earth do you indicate that the newly-added
> > child should be related to the newly added child.
>
> At this point the DB is not involved only after you finish
>adding all the rows and often getting a user to fill out the
> details in a form will you send it to the DB/ MidTier. I
> will get to this later.
Sure the DB is involved! How is the DB not involved? (Admittedly if the DB
is not involved it's much simpler, but then where do I store my data?...)
However, I notice that Chris Anderson has answered my question - you do it
with two round trips. First, INSERT the new parent row, and you get back
the new identity value. If you have an appropriate foreign key constraint
set up on your DataSet, the act of updating the parent row's identity will
have the side effect of upating any references to this row in the child
tables. So when you come to update those, they will indeed have the correct
key.
--
Ian Griffiths
DevelopMentor
You can read messages from the Advanced DOTNET archive, unsubscribe from
Advanced DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced
DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
