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

Question

I have two C# programs that share a single SQLite database file.

One program (let's call it Writer) writes new data to the database. The other program (Reader) reads it. Both programs use Fluent NHibernate and System.Data.SQLite to access the DB.

Currently, I've just got a big Refresh button on my Reader program, and the user has to click it to get any new data that was written to the DB since the last refresh. This works, but is hardly elegant.

Is there any way to get NHibernate to fire an event in the Reader program when the DB is updated by Writer, so I can automatically present new data to the user? (I looked at the NH docs for Interceptors and Events, but it was not clear if these would do what I want)

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

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

Answer 1

I think an Interceptor or Event on the writer side would be the right solution for this. In our current project we have done something similar and it works great.
Using this approach you would keep the ability to switch the database below or-mapping and could elaborate your update-event from a simple 'TableUpdate'-trigger which i think should be done within a few hours to a concrete Update-Event containing all changed data.

EDIT:

First of all i would not only track the OnSave but also AfterTransactionCompleted to ensure the Transaction is comitted. This also enables you to collect all objects of the tx. The easiest way to do this is overriding the EmptyInterceptor object like this:

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 straigthforward approach for the inter-process communication IMO would be .net remoting. There are several examples how to do it. A simple one can be found here. Or if you want to try WCF see here. Or search SO, lots of good examples can be found here.

Answer 2

You can put a System.IO.FileWatcher onto the shared file. That will tell you when it has been updated.

Answer 3

Here's some sample code that uses FileSystemWatcher, as suggested by John Rayner...

     // 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);