Here I will post problems I and my colleagues met and solutions we found.

Thursday, February 24, 2005

Insert into dependent parents I

In previous Master/Detail topic I described how to use relations to populate details with values from masters. However, what if master has reference to details too? For example:
contacts table has field contact_id (PK) and primary_address_id (FK), which references to contact_addresses table with primary key (contact_id, contact_address_id).

If we have new records in table contacts and table contact_addresses, how would we post them using DataAdapter components?

Before starting some notes. DataSet was set to have all id values generated on client side to be negative. This way there will no be conflicts with server generated value and I can always tell where this value was generated.

My first approach was this one:
Insert or Update statements for contacts looked like:

if @primary_address_id < 0
begin
@old_primary_address_id = @primary_address_id
insert into into contacts (...) // except field primary_address_id;
set @contact_id = @@identity;
insert into contact_addresses (...) // for primary_
set @primary_address_id = @@identity;
end;
update contacs set primary_address_id = @primary_address_id;

So, besically I tried to insert fake record into contact_addresses table.

Later in RowUpdated event for contacts table I got old_primary_address_id and primary_address_id values and corrected recotd in contact_addresses table;

After that for contact_addresses table insert looked like
if @primary_addres_id < 0)
insert into contact_addresses(...)
else
update contact_addresses ...

So, I could easily use
contactsDataAdapter.Update(contactTable);
addressesDataAdapter.Update(addressesTable);

But later I decided to try different approach and I liked it better...

No comments: