Synchronize C# Object Array with Database Table

Question

I am writing a Scheduling application, and each row in my Database Table is a different task to run at a certain time. In my application, I pull this information, and store each task in a separate object, and keep them stored in a List.

As of right now, when I run my function to refresh this, there are no checks and it just adds them all again. I need to figure out the best way to see if an object already exists for the particular job, and only create a new Object for it if it doesn't.

Would the best way to do this be to check the ID of the row, and then search through each current task Object to see if it exists? My main concern is I do not know if the Auto Increment ID values replace missing ones. As in, if I have row's with ID 1,2,3,4,5 and at some point the row with ID 3 is removed, will the next row added be 3, or will it be 6?

Answer 1

Usually, auto incrementing IDs increase, but never recreate (the database will always +1, but doesn't go back and see if old IDs have freed up). You should confirm this by reviewing you SQL server documentation or test it to be certain.

Your best bet is to check your collection for the object. I would do as you stated and ensure an ID value isn't present in the collection prior to adding the value.

Answer 2

You could store the objects in a Dictionary where Key is int and Value is the type of your object (e.g. Task). You could then pull the task out from the Dictionary using the key:

Dictionary<int, Task> dict = new Dictionary<int, Task>();

// Code to do first-time initialise of the dictionary
....

// Subsequent refresh
Task task;
if (!dict.TryGet(id, out task))
{
    task = new Task();
    task.Id = id;
    ...
    dict.Add(id, task);
}

As for the auto incrementing of ids, it is likely that the RDBMS sets the value for the new rows as last value assigned + 1.