2.0 Auto Id in typed datasets

  • Thread starter Thread starter s.bussing
  • Start date Start date
S

s.bussing

Hi, I'm still strugling with the following situation.

I've got two table which are related by a foreign key contraint. These
table reside in a typed dataset. I working on a webbased app, so a
disconnected environment. Now, when I add a row to the parent table and
add rows to the childtable (think of customer and orders). This means
that I have to use the autogen. id of the parenttable and use this Id
as de FK for the childtable.

Then when updating the database, I have to retrieve the new Id from de
customer table from de db and use this id to update the FK in the
childtable before I can add the rows to the db.

In 1.1 for this I used the RowUpdating and RowUpdated event. The above
example is very basic, in my situation I had to update multiple
depending tables. This forced me to use the RowUpdating and RowUpdated
methods. I als used the dataadapter.update to update the typed dataset.

I can not rely on the Id generated by the typed dataset, because an
other person can also add a customer and orders at the same time. To
uniquely create an Id, I set the prim.key in the parent to a seed of -1
starting from -1. The neg. numbers are replaced with the new db Id as
described in the example above.

Now, do I have to use the same construction in 2.0 or is it better to
use tableadapters instead? If yes, how can I use them in such a way the
same functionality is there as in my 1.1 app?
 
Hi s.bussing,

I got around this problem by using the auto-id as you are in the dataset and
an Identity value in the parent table in the DB. The trick here was to make
the relationship in the dataset between the parent and child "Both a
Relation and Foreign Key Constraint" in the dataset designer. This allows
you to Cascade update/delete the child rows. Now when you insert the parent
row into the DB and read it back, the dataset automatically sets the FK on
the child row(s) to the new parent id.

Hope this helps.

PGC
 
Hi PGC,

let me get this right, in the DB you have a parent table containing a
prim.key.(this is the autoId field) and a child table containing the
foreign key. There is also a relation between the two tables

In the typed dataset these two tables are present and also a releation
(FK relation), between them.

This excatly the same situation I have, simplified.

Read it back, you mean the new Id from the DB? (I think so). And by
defining "Both a relation and Foreign Key Constraint" this will
automatically. That's great. I'm going to test this.

Does this also work when there is a relation in de DB. In my experience
when there is such a situation, deleting a record from the parenttable
will, in that case, throws an exception like "Concurrency voilation,
DeleteCommand effected 0 rows" (something like that). In 1.1 I would
set the ContinueUpdateOnError of the dataadapter.

Any experience with this situation?



PGC schreef:
 
Hi s.bussing,

'Read it back' in my case this means that my "Insert" stored procedure (SQL
2000) returns the "inserted" row back to the dataset. e.g.

CREATE PROCEDURE sp_InsertDockets
@DCK_Number varchar(20) = NULL, -- Docket Number
etc etc
AS
BEGIN
-- New docket row so insert the data
INSERT INTO Dockets
(
Dockets.DCK_Number,
etc
)
VALUES
(
@DCK_Number,
etc
)
-- Retrieve the row just inserted to get the IDENTITY value of DCK_ID. This
will in
-- turn update the Docket_Lines rows in the dataset through a cascading
-- update.
SELECT DCK_ID, DCK_Number, etc
FROM Dockets
WHERE (DCK_ID = SCOPE_IDENTITY())
END

After calling sproc my datatable row in the dataset has the new identity
value and this in turn cascade updates the child rows in Docket_Lines (not
shown). Then I can simply call the insert sproc for the child table and know
that the relationship in the DB will be correct.

As regards doing the same in the DB with a cascade update/delete
relationship - I am not using cascade update/delete just the relationship. I
explicitly delete child rows and then the parent row as this is "visible"
code (my preference) but I don't see any reason why it shouldn't work.

Hope this helps.

PGC
 
Back
Top