Database checking for updates

Question

Using SQLite in a WPF application that schedules events for people. The application will be used by 2-4 users concurrently. I'm wondering how to ensure each user has the most current database information.

What I came up with is:

1. Start a separate thread responsible for checking for database updates.
2. Use time interval setting (e.g. 30s, 1 min, 2 min, 5 min, etc.) to SELECT the data.
3. Pull all data and create a new in-memory collection for the domain.

Once I have that working then I can use things like LastModifiedDataAndTime column to keep track of when the database was modified so I'm not querying the same data. It seems awfully inefficient to create (recreate) a new collection every 30s, 1 min, 5 min, etc. Am I going about this the correct way using a separate thread to SELECT the schedule data?

Answer 1

This depends heavily on the UI and the way it shall present the data to your users. If the UI only presents some small portions of the data which are updated whenever a user opens a new window or changes a view, it could be sufficient to check for db changes only in context of an update operation, to detect potential collisions by two users trying to change the same record concurrently.

If, however, the data is displayed in the UI like some kind of "newsticker", which needs to be updated at the screen from time to time in the background, having some separate thread to query the database in regular time intervals is a sensible solution.

Having some timestamp stored in the database where you can check quickly if there might be some changes in the db (or no changes at all) is a standard optimization, but only when modifications happen typically less frequently than the time intervals planned for the update checks.