Storing data in sync with mysql database

Question

I am experimenting with data binding in WPF

I have this datagrid related to mysql view, it will be read-only, but I would like it to automatically update when changes are made to the database.
I couldn’t find anything useful on Google, and I don’t know where to start.

This is the dataprovider class.

class ConDataProvider { private MySqlDataAdapter adapter; private Data data; private DataTable table; public ConDataProvider(string query) { data = new Data(); table = new DataTable("con_FullGrid"); adapter = data.getAdapter(query); adapter.Fill(table); } public DataView GetDView() { dv = new DataView(); dv.Table = table; return dv; } } 

And XAML for the window

 <Window x:Class="HelioWPF_Alpha01.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:local="clr-namespace:HelioWPF_Alpha01" xmlns:sys="clr-namespace:System;assembly=mscorlib" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="MainWindow" Height="676" Width="924" MinHeight="300" MinWidth="463" Background="{x:Null}"> <Window.Resources> <ObjectDataProvider x:Key="ConDataProvider" ObjectType="{x:Type local:ConDataProvider}"> <ObjectDataProvider.ConstructorParameters> <sys:String>SELECT * FROM con_FullGrid</sys:String> </ObjectDataProvider.ConstructorParameters> </ObjectDataProvider> <ObjectDataProvider x:Key="btable" ObjectInstance="{StaticResource ConDataProvider}" MethodName="GetDView"/> </Window.Resources> <Grid Name="mainGrid" DataContext="{Binding Source={StaticResource btable}" KeyDown="mainGrid_KeyDown" FlowDirection="LeftToRight" > <DataGrid AutoGenerateColumns="True" ItemsSource="{Binding}" Name="dataGrid1" FrozenColumnCount="20" RowHeight="25" Grid.ColumnSpan="4" ColumnWidth="*" MouseDoubleClick="dataGrid1_MouseDoubleClick" SelectionMode="Single" Grid.Row="2" Grid.Column="1" TabIndex="1" GotKeyboardFocus="dataGrid1_GotKeyboardFocus"> <DataGrid.AlternatingRowBackground> <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5"> <GradientStop Color="Black" Offset="0" /> <GradientStop Color="#16436DF8" Offset="0" /> </LinearGradientBrush> </DataGrid.AlternatingRowBackground></DataGrid> </Grid> 

What I'm doing is faking it by reinitializing the ConDataProvider class for the code and re-binding the datagrid to various events. But this is an ugly hack.
I would not mind redoing all this if necessary.

The Data class simply contains a connection string and some methods for interacting with the database. You can see it here: http://pastebin.com/m9HLfwEQ

Thanks in advance. Hope to get some good tips.

Answer 1

How about connecting any reactive extensions .

What you can do is create an observable that checks your database every X seconds. Then connect the Observer to the observable. In the OnNext Observer event, you can add data to your grid or rename anything you like.

Essentially, you have to create something that sits between your database and your application that listens for changes and pushes those changes towards you.

Answer 2

MySQL adds information to the log when data changes in the database. Perhaps this feature should be enabled, but not sure if it is enabled by default.

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

With this, you can start a server-side service that will listen for changes to MySQL tables and send an event to the client if the tables are updated.

Clients will need to implement a code to update the grid when receiving such events from the server.

The bin-log utility can be used to scan bin logs for changes.

http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html

I would use this approach instead of scanning the table to modify the data if the amount of data or the number of transactions is large. Otherwise, the approach to polling the database should work fine.