How can I get notified in C# WinForms of external MS Access database updates?

Question

I have a DataGridView that displays data from a MS Access database. I'm using a DataSet with a TableAdapter and a BindingSource to link the data to the DataGridView:

        tableAdapter = new AccountsTableAdapter();

        dataTable = new Accounts.AccountsDataTable();
        tableAdapter.Fill(dataTable);

        tableBindingSource = new BindingSource();
        tableBindingSource.DataSource = dataTable;

        dataGridView1.DataSource = tableBindingSource;

I want to know how can I detect or get notified when the database table gets modified from outside my application - row updates, inserts, deletes performed on the database from the Access interface or from a different app.

Also, upon this presumed notification, how can I update my DataSet so that only the affected rows should be updated -> receive only the newly inserted rows, the affected field values of the modified ones and the indexes of the deleted.

So, basically, what I'm trying to obtain is a way of synchronizing my database table with the DataGridView. I've already managed to save to the database the rows that I modify or insert in the DataGridView, now it would be nice to be able to perform the reciprocal side of this database - view binding.

Answer 1

The only way I know of is to poll the database. If the data has a LastModified field, you can make requests to the database to get updated rows and then merge the results into your DataSet.

For example, let's say you populate the form from the sql "SELECT * FROM Contact". Then every minute or so, run the query "SELECT * FROM Contact WHERE LastModified > @LastFetched" where @LastFetched is the time you last got updates. You'll want to get the value for @LastFetched from the database since the client machine and database server might not have their times sync'ed close enough to work properly.

Then you just need to update the DataSet with the updates. If the form is bound properly, it should automatically be updated.