Updating a collection to db

Question

Hi, This may be a little naive but I just want check my way of doing this is correct. I receive a collection of objects from the UI. I then wish to check those objects against the records in the db.

This is what I am doing to Create Update and Delete the received objects.

1. Loop trough received objects - if (id == 0 ) create new record.
2. Retrieve existing records from db;
3. Loop existing records - Where (existing record id == recieved object id) Update record.
4. If the record exists in the existing records but not in the received objects - Delete.

This seems the most logical way to do this. I am using NHibernate and was kind of wondering whether there was another way I should be looking into. Any help much appreciated.

Answer 1

Take my answer for what it's worth; I say that your logic is solid.

Answer 2

If you do use SQL Server 2008 (or could opt to use it), there's a new "MERGE" statement that basically does all that.

In that case, you could write all your data into a temporary table (in memory or on disk), and then update and insert your data into the target table with a single MERGE statement.

Here are some info posts on SQL Server 2008's MERGE:

• SQL Server 2008 - The Power of Merge (requires free registration)
• SQL Server 2008 MERGE
• Using SQL Server 2008's MERGE statement
• SQL Server 2008 Merge Statement
• SQL Server 2008 - Intro to MERGE

Cheers, Marc