Receiving DB update events in .NET from SQLite

Question

I've recently discovered the awesomeness of SQLite, specifically the .NET wrapper for SQLite at http://sqlite.phxsoftware.com/.

Now, suppose I'm developing software that will be running on multiple machines on the same network. Nothing crazy, probably only 5 or 6 machines. And each of these instances of the software will be accessing an SQLite database stored in a file in a shared directory (is this a bad idea? If so, tell me!).

Is there a way for each instance of the app to be notifiied if one instance updates the database file? One obvious way would be to use the FileSystemWatcher class, read the entire database into a DataSet, and then ... you know ... enumerate through the entire thing to see what's new ... but yeah, that seems pretty idiotic, actually. Is there such a thing as a provider of SQLite updates?

Does this even make sense as a question? I'm also pretty much a newbie when it comes to ADO.NET, so I might be approaching the problem from the entirely wrong angle.

Answer 1

Using SQLite across a network isn't a good idea. Check out SQLite's own recommendations on this here.

A client-server database will be far more reliable and may also solve your notification problem. For instance, PostgreSQL has an inter-client signalling mechanism via the NOTIFY and LISTEN statements, which can be used directly from a client or from inside a function, sproc or trigger.

Even if you decide to go for SQLite, do not use file-watching APIs. They are completely broken on Windows due to a race-condition deep in the bowels of the filesystem. From the MSDN entry for FileSystemWatcher:

Note that a FileSystemWatcher does not raise an Error event when an event is missed or when the buffer size is exceeded, due to dependencies with the Windows operating system.

It offers recommendations to alleviate this, but none of them provides any relibility guarantees.