• Check out world news on HeadLinesList.com
  • Forum registration is now open for public.
Hello There, Guest! Login Register


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Continously get fresh info from a mysql database (c#)?
#1
How can I efficiently get current information from a mysql database in my c# program?

The program runs on several systems at once and each system needs to get fresh information in case the other systems have made changes.

The program, in addition to normal operations that update the database based upon user activity, needs to draw out updated information from up to 5 tables simultaneously and display them to the user in a listbox.
Reply

Ads

#2
If you were using MS-SQL or Oracle, you could take advantage of query notification services from C#. This is a really slick, and very efficient way to be notified when tables you are interested in have been modified by another process.

With MySQL, I think you will have to resort to polling. The key to polling is to be able to identify that something has changed, and/or what has changed with a very fast, efficient query. You certainly don't want to bring back the entire dataset on each poll.

One approach is to have a lastmodified timestamp column in each of your tables. On an insert or update, you set this column to the current time. (You want that done using the database servers clock, not each individual client machines clock). Something like this should work:
ALTER TABLE mytable
ADD lastmodified TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;

You will also want to create an index on the lastmodified column.

Now on the C# client you can do 1 of 2 things: You can just look to see if the last modified timestamp on any of your tables has changed since the last time you looked, and if it has, refresh your local copy of the data. Getting the timestamps could be done with:

select
(select max(lastmodified) from table1) as lastTable1Time,
(select max(lastmodified) from table2) as lastTable2Time,
etc.

Now just compare the times with what you got last time you asked. If something changed, refresh your entire dataset.

A more efficient way would be to ask for only those rows that have changed since the last time you looked. Save the last modified time for all your tables. If you see in a poll that table 1 has changed, then go get all the rows modified since then:
select <stuff you want>
from table1
where table1.lastmodified > <cached last modified time for table1>

Now you have to merge the rows you got back with your existing dataset, which is more work, but also more efficient.

One problem this doesn't address is deletions. If other machines are deleting rows, the other clients won't see it. That requires additional work if you need to solve it.

Another possible problem is race conditions. Client 1 and 2 might both update a row within the same CURRENT_TIMESTAMP period, and your poll might just happen to run in between those 2 updates. In that case, you could miss the update from client 2. One solution would be to return CURRENT_TIMESTAMP as part of your poll, and if any of the lastmodified times have the that value, repeat the poll (maybe after a short delay).
Reply

Ads

  


Forum Jump:


Browsing: 1 Guest(s)