A few points.
You have to do child deletes before parent deletes.
You have to do parent inserts before child inserts.
You can streamline that by
a) Child Deletes
b) All parent updates/inserts/deletes - simple dataadapter update
c) Child Inserts, Child Updats
The problem is further aggravated in that for example, in step c, you could
have a transactional issue that requires a roll back. You also need to
rollback the datasets - because when you do an update, it sets all the roll
flags to being updated. So what I usually do is copy the changes (using
getchanges) into temporary datatables - and pass those to the dataadapter -
and then when the Commit succeeds, I do a DataSet.AcceptChanges() on all
datasets being update.
Barry Gervin
----- Original Message -----
From: "Chris Anderson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 16, 2002 10:53 AM
Subject: Re: [DOTNET] ADO.NET multiple table updates
> Thanks
>
> I am using three adapters, and an updating the parent first
> (I have to update the parent first, to ensure integrity on the database,
and
> for new records, the foreign key(FK) isn't known until the parent table
has
> been written)
>
> The child tables do get updated with the FK once I call Update on the
parent
> table's adapter, but they are immediately flagged as updated, so when it
> comes to using their respective adapters to do their updates...nothing
> occurs (the DataTable thinks it has already been updated)
>
> It occurs because I have the FKConstraints setup in the DataSet, ie
> Dim fk As ForeignKeyConstraint
> fk = New ForeignKeyConstraint("TypesFK",
> dtSession.Columns("sessionid"), _
> dtTypes.Columns("sessionid"))
> fk.UpdateRule = Rule.Cascade
> fk.AcceptRejectRule = AcceptRejectRule.Cascade
> dtInstitutionTypes.Constraints.Add(fk)
> fk = Nothing
>
> If I remove them just before the parent table update, the child tables are
> not flagged as written..but then they also lose the automatic FK update
when
> the parent table's ID is set at the database
>
> I'm using SQL Server 2k, yes
> I was thinking about using the GetXML, and passing that, then using SQLXML
> to parse it server-side, but the diffgram could potentially be large, so
> passing it via a SP parameter may cause problems
> (Unless you are thinking of a different idea there)
>
> Merak
>
> > -----Original Message-----
> > From: Shawn Wildermuth [mailto:[EMAIL PROTECTED]]
> > Sent: 16 May 2002 15:21
> > To: [EMAIL PROTECTED]
> > Subject: Re: [DOTNET] ADO.NET multiple table updates
> >
> >
> > I have found that a separate DataAdapter for each table is
> > necessary and calling them in the right order is crucial in
> > many instances. It matters how your schema in the database
> > (not the DataSet) is expecting the data as to how you would
> > call the updates. Are you using one or three data adapters?
> > If you are using SQL Server (2k?), maybe diffgrams are the
> > better way to go if you can use SQLXML 3.0.
> >
> > Have I muddied the waters or clarified them?
> >
> > Thanks,
> >
> > Shawn Wildermuth
> > [EMAIL PROTECTED]
>
> You can read messages from the DOTNET archive, unsubscribe from DOTNET, or
> subscribe to other DevelopMentor lists at http://discuss.develop.com.
>
You can read messages from the DOTNET archive, unsubscribe from DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
