is it possible to have a datagridview automatically update when the database it is bound to gets updated?

Question

I have a C# winforms application that has a single datagridview to display the contents of a table in a mySQL database. Is it possible to have the datagridview reflect changes to the database in real time? i.e. when i make a change to the table directly, it will show that change in the datagridview

Answer 1

It is possible, yes, currently advisable, probably no. The technology you're looking for is called "comet" and is a server-push type of architecture. In a web-app this goes against what is currently supported, reliable and readily maintainable. Options include running a webserver from the browser and doing callbacks and persistent xmpp conversations but none that are really attractive unless you absolutely need this behavior now.

HTML 5 is coming with web sockets that should make this desirable behavior more accessible but it will probably be a couple of years before widespread support comes about.

Consider polling a webservice using ajax and doing selective, partial page refreshes rather than implementing a true http push architecture.

Answer 2

The short answer is no. You'll have to implement some sort of polling mechanism.

If this feature was of significant importance to your application, SQL Server 2008 R2 has a technology called StreamInsight that allows you, at the database level, to watch the stream of data (before it hits the disk even) and trigger events and in theory it may be possible to use a mechanism around that to trigger the updates in your application. I'm fairly certain that MySQL doesn't contain a comparable technology though.

These would also be only "near real-time". I'm guessing what you mean is that you want the datagrid to update whenever there is new data, which would not be real-time.

Answer 3

It is theoretically possible, but is very awkward and does not feel very native to working with databases. Generally speaking, DBs make a very source of real-time data. If you want real-time data, you should be working with a real-time push data source ( there is a whole bunch of them, mainly geared towards financial data).

I have implemented a system (in SQL server, not MySQL, but perhaps you can do the same) using update triggers and native stored procedures. A native stored procedure is simply a non-managed code DLL that implements a certain interface and one that can be called from an SQL trigger.

The sequence of events is roughly as follows:

1. Managed code opens an agreed file on the filesystem (lets call it "watch" file) with FileSystemWatcher and sets a callback when the file is changed.
2. Update trigger fires and calls native stored procedure.
3. Native stored procedure "touches" the watch file, updating its data modified field.
4. FileSystemWatcher fires off a callback, after which we know that data has been changed, so we reload it from the database.

Answer 4

You cannot do that in a simple way. However there is a SqlDependency Class (link) which you could use but not with mySQL. If anyone is interesting in, (here) is another link to SQL Server documentation.

Anyway, you should really consider if this functionality is necessary because of huge impact on DB efficiency and network traffic.

Answer 5

Take a look at BindingSource and INotifyPropertyChanged (for your objects).

MSDN: How to: Raise Change Notifications Using a BindingSource and the INotifyPropertyChanged Interface