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?

  • What's the cost of having out-dated data? How bad is it if you data is time lagged by 1 min? 5 min? 1 hour? 1 day? Figure out what is acceptable, then work from there. – Becuzz Jan 22 at 13:41
  • @Becuzz it's not life or death, but it would cause the other 1-3 users to be making decisions based on outdated data. The time interval setting that the user sets (e.g. 30s or 5 mins) is sufficient for our use. – keelerjr12 Jan 22 at 13:42
up vote 0 down vote accepted

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.