GUI Programming. Database and Tables syncronization

Question

I've worked on several desktop applications that used tables to group information from a database table. But keeping this information updated has made me hack different solutions like a working thread that updates a table executing a query every X seconds, or simply a button to refresh the view. I have even seen an app that would refresh a table using mouse movement events. So my question is, which is your preferred method. Is there a "perfect" way of achieving this that I sleep over at college? Thanks for the responses people.

Answer 1

Perfect is specific to your needs, but many web sites simply update the table whenever you've done an update to a cell. This generates a lot of traffic. Maybe you could queue the updates by using, e.g., a transaction, and then commit when the user closes the given table, presses save or a timeout expires ?

I think it all depends on your particular application.

Answer 2

That's cool for a web app. But we're talking about a desktop application that even when idle, should keep updating it's information from a database. This is more a design question, than a programming one.

Answer 3

What are you trying to accomplish?
Is it some sort of real time monitoring application like stock trading, plant monitoring software or do you have grid in some CRUD application that you want automatically update?

If this is a crud application, then first thing I would do is to check requirements:

• Is it really necessary for your user to have updated information automatically all the time,
• Does your user looks at monitor all the time?
• Why? Maybe you can implement some sort of alert system that can be checked less often?

If you really really need approach you described, then you did fine - you can use either timer or some other event(button, mouse move, ...) to refresh data. Of course, constant requerying will become performance problem as number of users raise.

If it is real time monitoring software with lots of data than you should not put database in center of system. You should have central service that your clients connect to over TCP (or some similar protocol). Central service should keep latest state of system (plus maybe some history). Clients can attach to service and service should notify clients when there is new data, so that you avoid constant polling. Database should be used as log that application uses for analysis of past events, but not for real time display.

Edit :

Regarding Andrej's answer: I am not sure if this resolves your problem. Observer pattern is usually implemented with subscribe/notify mechanism, so that data source can notify all observers when data changes. Problem is that RDBMS's usually do not have mechanism that can notify you about data change. Even if they have, it is not practical because there can be large number of changes in short period of time. Think of tens or hundreds of updates in second - how often RDBMS should notify clients about change? What if there are hundreds of clients with slow network? When you have more than one user, things become complicated.

Databinding works in process - you cannot bind your grid directly to table in database. You first need to get data to your application into some object that can be bound to GUI (datasets, lists, ...)

Answer 4

The easiest way would be to have some configurable timer thread that updates the query (provided that the timeouts don't occure every second). You could also show the timestamp of the latest data gathering and provide Refresh button for users who want current data.

Answer 5

Some programming languages/frameworks have build in support for databinding. Databinding will synchronize the view (table component) and the data (database tables).

Flex, for example, has binding build in, javafx also, for regular java you can use jgoodies bindings, or oracle's adf data controls and data binding.

If your language doesn't have a standard binding solution, it might be useful to have a look at how an existing one is implemented, for example jgoodies bindings (it's opensource).

Btw, most of the bindings frameworks are based on some default design patterns like the observer pattern: The view component needs to subscribe itself to be informed of updates on the data.

Edit:

Good point about database changes. That's a bit harder. Technically it should be possible to use triggers to inform the client. In Oracle for example, a trigger could possible post a data change event message on a message queue using advanced queuing, which interested client could listen for. I haven't seen this in practice though. You usually don't want to put knowledge about gui clients into your database.

Better would be to have a data collection which is regularly updated using polling. This data collection would be bound to a view component using bindings.

Answer 6

Very helpful Andrej. Databindings seem like the easy way to go when you've got the chance of using them.

Oh yeah, the observer pattern:

software design pattern in which an object maintains a list of its dependents and notifies them automatically of any state changes, usually by calling one of their methods. It is mainly used to implement distributed event handling systems.

I find it very handy the way C# declares new events using delegates.

Thanks for your very good answers.