Updating item collection if found in persisted collection

Question

public class Item
{
      public int ID {get;set;}
      public string SKU {get;set;
      public int Quantity {get;set;}
}

I pull items from my database like:

List<Items> savedItems = Db.GetItems();

I then have this method that takes in a collection of items:

public void Update(List<Items> items)
{
           // update database
}

The items passed into the Update method items that may or may not be already in the database. Uniqueness has to be done using the SKU property.

So say I have in the database:

ID  SKU   qty
1   a      1
2   b      1
3   c      1

Now if I pass a items collection into the Update method, that has:

ID=?, SKU=d, qty=1

Then it will just insert a new item.

If I pass in a item that looks like:

ID=?, SKU=b, qty=1

then I won't insert a new item, but I will update the item with ID=2.

How can I do this? Should I just put all the SKU values into a hash, if it exists, then somehow find that item and update it?

I will I could find the Item w/o doing a database lookup since I already pulled the items from the database already.

If the item is already in the database, I have to update the quantity. If it is not in the db, it just gets inserted.

Answer 1

Basically, you want to do a check. First, you would query the database for the existence of the item with the SKU value.

If the value exists in the database, then you would perform your update.

If it doesn't exist in the database, you would perform an insert.

You can be a little slick and assume the update case, like so:

update items set ID = ..., qty = ... where SKU = ...

You would then check the return value for the ExecuteNonQuery call you do on the command. If it returns 1, then you know that you affected one record and the insert succeeded. If it returns 0, then the item doesn't exist in the database and you need to add it.

A separate issue here is the issue of concurrency. You should have a timestamp of some kind on the table to check against to see if other people have made changes against the item while you were holding onto the value on the client side.

Another thing to make sure you do is to wrap this in a transaction so that you can guarantee atomic operations when performing the update/insert.

Finally, the question that comes up is why aren't you using something like LINQ-to-SQL, LINQ-to-Entities, NHibernate, or some other ORM-ish type solution which will handle all of this for you?