>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. .....
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 .
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.
>.... 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.
>Is the normal technique to do several round trips to the DB? (I.e. update
>the parent, retrieve the new key, update the DataSet to reflect the chosen
>key, then update the child table.) Is that workable?
No - the standard procedure is more like treating the dataTable as a
seperate DB and synchronizing with the DB when it needs to . I use 1 trip to
do an insert using the stored procedure below,
>Also, what does the SELECT statement that retrieves the key that the DB
>chose look like in practice? If the DB has invented a new primary key, how
>on earth do I tell it which record I'm looking for? Don't I need to know
>what the new primary key was before I can ask for the record? Or are you
>using the magic @@IDENTITY value?
yep using magic @@INDENTITY
/* Create procedure/function spCompanyInsertCommand;1.
*/
ALTER PROCEDURE dbo.spCompanyInsertCommand
(
@CompanyName nvarchar(100),
@AliasName nvarchar(100),
@Capital money,
@Employees int,
@AddressID int,
@ShippingAddressID int,
@IndustryID int,
@PhoneNumber nchar(30),
@FaxNumber nchar(30),
@WebSite nchar(30)
)
AS
SET NOCOUNT OFF;
INSERT INTO Company(CompanyName, AliasName, Capital, Employees, AddressID,
ShippingAddressID, IndustryID, PhoneNumber, FaxNumber, WebSite) VALUES
(@CompanyName, @AliasName, @Capital, @Employees, @AddressID,
@ShippingAddressID, @IndustryID, @PhoneNumber, @FaxNumber, @WebSite);
SELECT CompanyID, CompanyName, AliasName, Capital, Employees, AddressID,
ShippingAddressID, IndustryID, PhoneNumber, FaxNumber, WebSite FROM Company
WHERE (CompanyID = @@IDENTITY)
>(And how are you pushing it back to the
>DataSet? Manually? Or is there a way of automating this with a
>DataAdapter?)
The DataAdapter generated the above code ( make sure you select the option
to do a select after update) but I think had to add the CompanyID to the
Select on the stored procedure. Because of the select option the adapter
treats it as a Insert / Update instruction and repopulates the record in the
dataTable. Constraints/relationships then pass the new ID value
(autoincrement ) to all parent and child records. This is often difficult to
do in a DB (changing identity values) but is fine for datatables.
This is where I got into trouble a few times . As you have to ensure you are
following the proper order on updates
eg
do all deletes first
then for each parent child
do child inserts
do child updates
do parent inserts and updates.
You will note this is a tree structure. If you have a looped relationship eg
parent linking to child which links back to the parent you may need to use 2
tables to represent the same DB table or use smaller datasets. To get
smaller DataSets I no longer put things like country , State , Postcode etc
tables in the dataset. I bind the country Combo Box to the Country DataTable
( which has no Dataset ) and with the onchanged event I update the
Company-Address Dataset.
Try it and let me know if you have any issues it worked for me sofar.
Ben
-----Original Message-----
From: Moderated discussion of advanced .NET topics.
[mailto:[EMAIL PROTECTED]]On Behalf Of Ian Griffiths
Sent: Sunday, 7 July 2002 10:54 PM
To: [EMAIL PROTECTED]
Subject: Re: [ADVANCED-DOTNET] ADO.NET: Parent-child relationship and
auto-increment columns
Ben Kloosterman <[EMAIL PROTECTED]> wrote:
>
> The Microsoft recommendation is to autogenerate a -1 number
> with a -1 increment . (search on Autoincrement identity) .
> When you pass the data do NOT pass the indentity let the DB
> generate a new identiy during inserts. The insert shoucl do a
> select at the end and re-populate the record for any changes
> and the Constraints will update all foreiign keys. Unlike DB's
> you can easily change autoincrement identity collumsn in datasets.
Is the normal technique to do several round trips to the DB? (I.e. update
the parent, retrieve the new key, update the DataSet to reflect the chosen
key, then update the child table.) Is that workable?
--
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.
