What is the best practice for inserting or updating multiple tables at once

If I need to update or insert into multiple tables with one "action", example a call to save the information, where there are multiple tables that incorporate 'Information'.

For arguments sake, lets say we have the following tables:

Name Address Car Job

and with each call to save information, each one of those tables is being inserted into.

Which is better:

1. Get the data that must be written to Name table. Call InsertOnSubmit and call SubmitChanges
2. Get the data that must be written to Address table. Call InsertOnSubmit and call SubmitChanges
3. Get the data that must be written to Car table. Call InsertOnSubmit and call SubmitChanges
4. Get the data that must be written to Job table. Call InsertOnSubmit and call SubmitChanges

or

开发者_如何学JAVA
1. Get the data that must be written to Name table.
2. Get the data that must be written to Address table.
3. Get the data that must be written to Car table.
4. Get the data that must be written to Job table.
5. Call InsertOnSubmit, then call SubmitChanges.

or

1. Get the data that must be written to Name table. Call InsertOnSubmit
2. Get the data that must be written to Address table. Call InsertOnSubmit
3. Get the data that must be written to Car table. Call InsertOnSubmit
4. Get the data that must be written to Job table. Call InsertOnSubmit
5. Call SubmitChanges

I have implemented the first option given and I am sure this is not the best way to go. I don't know if I have problems with my network connection or whatever, but sometimes some of the tables are written to while others are not and this makes me sure I am not doing it the best way possible.

In some of the posts that are similar to this question, there are comments to do with Views and other options. I have no experience with this, so I need some guidance as to the best way to do this and which direction I should focus my attention on.

---

Prepare the data for the first table and execute an InsertOnSubmit for the table

then the same for the 2nd, 3rd, 4th table.

then execute the SubmitChanges.

In the database all rows wil be inserted (or updated or deleted if you include those kind of operations). If something goes wrong during the updates of the database ALL of the modifications will be removed (rolled back). So the integrety of the data is secure!

The data in the variables in your program will not reflect the database, if a roll back took place, you will need to access the data again.

The second variant does not always work well because it will only include the last table in the roll back. if insert of table 1 goes well, no 2 fails, nr 3 and 4 go well, then you will NOT notice the 2nd failure.

---

First of all, you should only commit/submit the changes once everything is in place, especially if the changes all pertain to a single "action". Otherwise it's possible that (as you are apparently experiencing) some tables may be updated while others are not, if for example an error occurs before a subsequent submit, or an abort happens.

I would recommend either the second or third option, myself, depending on how complex the "get the data" operation is, and how much memory it uses. If it's relatively simple, I would go with the second option: get all the data up front, then add it all and submit it in one fell swoop. However to be honest I doubt it really matters, so long as you only submit when the whole operation is finished and correct.