1

I query a MySQL table in my c# console app. Thousands of updates(row) are coming in all the times via another program and I want to propagate them, let's say for an easy way Console.WriteLine, in the fastest possible way (almost instantly). See diagram.

MYSQL to C# updates

Where do I do it in the most efficient/fastest way? On the MySQL Server? Or in my c# script?

If in my c# sript, do I use lists to compare old vs new row, or arrays? Any other way?

Any help is highly appreciated.

Slarti42
  • 11
  • 6
  • Do you have any DateTime based column in your table which modifies in each update? – MKR Feb 15 '20 at 10:54
  • You might want to look at TRIGGERS ... https://dev.mysql.com/doc/refman/8.0/en/triggers.html I would definitely detect the changes in SQL and only propagate the changes to your C#. – Rob Feb 15 '20 at 11:27
  • @Rob ok, I had a look into TRIGGERS. From what I have understood, it tells inside the MySQL server that something has been done and then executes a certain command. So how can I receive the updated rows in my C# program? I failed to find that answer. – Slarti42 Feb 15 '20 at 12:22

1 Answers1

1

Use triggers to write audit tables

There are some pretty good guides and questions on this like this: Creating Triggers to add the data into Audit Table

It does not contain timestamps which would be required for your case and it would also need a lot of space in your database to store all these changes

Athanasios Kataras
  • 25,191
  • 4
  • 32
  • 61
  • thanks. So basically, I can make a new table and call it to queue and each time I pull that table, I delete the content of it? Is there a way to kinda lock this at that moment, so if new updates are coming in, they won't get lost? – Slarti42 Feb 15 '20 at 14:04
  • You can add a timestamp for each entry, and delete up to the processed timestamp. – Athanasios Kataras Feb 15 '20 at 15:14
  • right, thanks. I think this is the best way forward for me. – Slarti42 Feb 15 '20 at 16:11