Checking if an item exists before saving

I've a SQL DB with various tables that save info about a product (it's for an online shop) and I'm coding in C#. There are options associated with a given product and as mentioned the info recorded about these options is spread across a few tables when saved.

Now when I come to edit this product in the CMS I see a list of the existing product options and I can add to that list or delete from it, as you'd expect.

When I save the product I need to check if the record already exists and if so update it, if not then save a new record. I'm trying to find an efficient way of doing this. It's very important that I maintain the ID's associated with the product options so clearing them all out each time and re-saving them isn't viable unfortunately.

To describe again, possibly more clearly: Imagine I have a collection of options when I load the product, this is loaded into memory and added to / deleted from depending on what the user chooses. When they click 'Save' I need 开发者_C百科to check what options are updates and what ones are new to the list.

Any suggestions of an efficient way of doing this?

Thanks.

If the efficiency you are looking to achieve is in relation to the number of round trips to the database then you could write a stored procedure to do the update or insert for you.

In most cases however it's not really necessary to avoid the SELECT first, provided you have appropriate primary keys or unique indices on your tables this should be very quick.

If the efficiency is in terms of elegant or reduced code on the server side then I would look at using some sort of ORM, for example Entity Framework 4.0. With a proper ORM architecture you can almost stop thinking in terms of the database records and INSERT/UPDATE and just work with a collection of objects in memory.

I usually do this by performing the following:

1. For each item, execute an update query that will update the item if it exists.
2. After each update, check how many rows were updated (using @@ROWCOUNT in SQL Server). If zero rows were updated, execute an insert to create the row.

Alternatively, you can do the opposite, if you create a unique constraint that prevents duplicate rows:

1. For each item, try to insert it.
2. If the insert fails because of theconstraint (check the error code), perform the update instead.

Run a select query checking for the ID. If it exists then you need to update. If it does not exist then you need to insert.

Without more details I'm not really sure what else to tell you. This is fairly standard.