CODEWITHSUNDEEP
c#.netmysql
Brian
Brian

Reputation: 1459

Monitor MySQL table for changes within a C# program?

Is it possible to monitor a mysql table for changes within a c# application? I basically want an event to be raised when data is inserted into the table. The only thing I can think of now is to query the table every 100ms or so.

Upvotes: 9

Views: 5388

Answers (3)

Ruebe
Ruebe

Reputation: 53

You can enable the binary log and watch it for changes.

And for those doing this in python, here's a python library, which can work blocking, like an listener for events, which also can be filtered for event type, table etc.

Upvotes: 0

SupermanKelly
SupermanKelly

Reputation: 157

Perhaps add Insert, Update, Delete triggers to the table to log row in a new table with two columns..

  1. uid of row changed
  2. type of change insert/update/delete

Then you access this table and it tells you the specifically which rows were inserted, updated, deleted.

Upvotes: 2

Nathan Taylor
Nathan Taylor

Reputation: 24606

If both the application and database server are on the same machine you might be able to set up a trigger in MySQL which writes out to a log file AFTER INSERT, UPDATE and then create a FileSystemWatcher to watch that log file. FileSystemWatcher will fire events when the file is changed that your application can react to.

The trigger might look something like this:

create trigger MyTable_Monitor
after insert, update on MyTable
for each row
begin
select * from new into outfile "path/to/table.log"
end

One problem I see with the above code is that the outfile cannot be appended to (best I can tell) so you might have problems if there are multiple queries executed in one call (or even multiple queries executed simultaneously by different clients). Any suggestions for improvement are welcome.

Upvotes: 3

Related Questions