I have a scenario where a webpage starts by display up to 1000 records from an SQL server with about 20 relatively small field sizes and where I want to then maintain that view in sync with the database.
I am using .NET and my approach has been to select the appropriate data and then periodically requery with a WHERE clause against the datetime fields such that only data modified since the last known update date is retrieved. After an update the last update date is updated to match the highest datetime value encountered so far and so on and so fourth. This has worked well smaller datasets but I'm curious as to what the best way is to manage the integration of these updates into the in memory datatable/class or other type of data object.
The data does have a unique a primary key, new rows may appear in update checks with their modified date being the date they were created but rows do not get deleted which simplifies the requirements.
I have read about using selects against the datatable to find the row, merges and other approaches but am not sure which method typically gives best performance.
I would expect only 1-2 rows to be updated at any time with most initial datasets typically being under 100 rows.
There is no need to save back to the datasource so this doesn't need to be considered in the suggestions.
The datasource is SQL 2000 which I understand is outdated although I am guessing that this is largely irrelevant for the purpose of managing it efficiently at the webserver end.
Any advice appreciated.