Can NHibernate detect when another program writes new data to a shared SQLite database?

Question

I have two C # programs that use the same SQLite database file.

One program (let Writer call it) writes new data to the database. Another program (Reader) is reading it. Both programs use Fluent NHibernate and System.Data.SQLite to access the database.

Currently, I just got the Refresh button in my Reader program, and the user must click it to get the new data that has been written to the database since the last update. It works, but hardly elegantly.

Is there a way to get NHibernate to fire an event in Reader when the database is updated by Writer, so I can automatically provide new data to the user? (I looked at NH docs for interceptors and events, but it was unclear whether they would do what I want)

Or is there a good way to poll for updates using NH?

NH solution failing, can I do something similar with System.Data.SQLite or with another lower level mechanism?

Answer 1

You can put System.IO.FileWatcher in a shared file. This will tell you when it will be updated.

Answer 2

I think that an interceptor or event on the side of the writer would be the right solution for this. In our current project, we did something similar, and it works great.
Using this approach, you retained the ability to switch the database below or-match and could develop your update event from a simple TableUpdate trigger, which, it seems to me, should be done within a few hours for a specific update event containing all the changed data.

EDIT:

First of all, I would not only track OnSave, but also AfterTransactionCompleted to ensure the transaction. It also allows you to collect all tx objects. The easiest way to do this is to override the EmptyInterceptor object as follows:

 public class EntityInterceptor : EmptyInterceptor { private IList<object> entities = new List<object>(); public override bool OnSave(object entity, object id, object[] state, string[] propertyNames, NHibernate.Type.IType[] types) { entities.Add(entity); return base.OnSave(entity, id, state, propertyNames, types); } public override void AfterTransactionCompletion(ITransaction tx) { if (tx.WasCommitted) { Console.WriteLine("Data has been inserted. Notify the reader here."); } entities = new List<object>(); } } 

The most complex approach for exchanging information between IMO processes would be .Net-remote. There are several examples of how to do this. Here can be found here . Or, if you want to try WCF, see here . Or look for SO, there are many good examples here.

Answer 3

Here is sample code that uses FileSystemWatcher, as John Reiner suggested ...

  // Watches for writes to database file private static FileSystemWatcher _dbFileWatcher; /// <summary> /// Set up an event handler that is called when SQLite database is written to. /// </summary> /// <param name="onChangedHandler"></param> public static void SetupSqliteDatabaseWatcher(FileSystemEventHandler onChangedHandler) { // Create a new FileSystemWatcher and set its properties. _dbFileWatcher = new FileSystemWatcher { Path = "<directory containing DB file>", Filter = "<DB file>.db", NotifyFilter = NotifyFilters.LastWrite }; // Add the event handler _dbFileWatcher.Changed += onChangedHandler; // Begin watching. _dbFileWatcher.EnableRaisingEvents = true; } // Event handler private static void OnDbWritten(object source, FileSystemEventArgs e) { Debug.Print("DB written"); } // Set up the event handler SetupSqliteDatabaseWatcher(OnDbWritten);