Best Approach on this in C#

I have a collection of id from checkboxlist (checked) let say: 1, 2, 3, 4, 5

In my database I have let say: 3, 4, 6

Now, the old approach that I have is that:

1. Delete: 3, 4, 6 on the database
2. Add: 1, 2, 3, 4, 5

Now this is ok if it's small records.

The best approach will be seperating between:

1. A new record: 1, 2
2. Current record: 3, 4
3. deleted record: 6

Therefore:

(1) Insert a new record on teh databsae
(2) Leave 开发者_运维技巧it
(3) Delete the record

Is this the best approach?

Thanks

How many records are we talking here? Your first approach is probably the easiest and fastest even up to a pretty big recordset.

Another option would be to pull down the entire recordset with one query. Use code to find out which ones you need to insert, update, or remove, and then commit the changes in a transaction.

I assume you are worried about the amount of data you have to transfer?

Your simple approach requires a 'delete all' which is a constant amount of data transfer (you don't need to know exactly which rows are present already), followed by sending the total length of the new list.

Your more complex approach first requires you to either fetch all records from the database, or to send all records to the database and get the database to tell you the difference. Then after that you need three separate commands to update the database. This is more data transfer in total than the simple method.

On the other hand, if your rows have foreign key constraints, deleting them and creating new rows might give them new IDs which could break some constraints and cause other problems.

The first method is definitely simpler. Use that unless you want to avoid changing the row IDs.

It might not be the most pretty solution but I think you could stay with your old solution. The reasoning behind this is that your page should never have a lot of checkboxes (hundreds).

delete all previous and add 1, 2, 3, 4, 5 easy and fail-safe ;)