Some data changes in the database. How can I run some C # code that works on these changes?

Question

Suppose I have an application A with a database. Now I want to add another application B, which should track the changes in the database of application A. Application B should do some calculations when the data has been changed. There is no direct connection between both applications. Both can only see the database.

The main problem: some data changes in the database. How can I run some C # code to make these changes?

---

To give some incentive for answers, I mention some approaches that I am currently considering:

• Make a request B to poll changes in interest tables. Advantage: simple approach. Disadvantage: a lot of traffic, especially when many tables are involved.
• Enter triggers that will fire on specific events. When they shoot they should write some entry in the "event table". Only Appendix B needs to poll this "event table". Advantage: less traffic. Disadvantage: the logic is placed in the database as triggers. (This is not a question of the “viciousness” of triggers. Its design is a question that makes it a Flaw.)
• Get rid of the polling approach and use the SqlDependency class to receive notification of changes. Advantage: (Maybe?) Less traffic than a polling approach. Disadvantage: no database independent. (I know OracleDependency in ODP.NET, but what about other databases?)

Which approach is more favorable? Maybe I missed an advantage in these approaches? Perhaps there are some other approaches that I don't think about?

---

Edit 1: Database independence is a factor for ... call them ... "sales people." I can use SqlDependency or OracleDependency. For DB2 or other databases, I can return to the polling method. It is simply a matter of value and benefit, which I want to at least think about, so I can discuss it.

Answer 1

I would go with number 1. In fact, this is not as much traffic as you think. If your data does not change often, you can be pessimistic about it and get only something that gives you yay or nay about table changes.

If you design your design based on the survey, you may not have to hit most of the hit for the survey.

• If you add only records, and not change them, then checking the highest identifier may be sufficient for a particular table.

• If you update them all, you can save the timestamp column and index it, and then look for the maximum timestamp.

• And you can send an ubber request that polls several talbes (efficiently) and returns a list of changed tables.

Nothing in this answer is particularly smart, I'm just trying to show that # 1 may not be as bad as it seems at first glance.

Answer 2

I would go with solution # 1 (polling), because avoiding dependencies and direct connections between separate applications can help reduce complexity and problems.

Answer 3

I think you looked at the approaches that I was thinking about, there is no absolute “best” way to do this, which is important for your requirements and priorities.

Personally, I like the elegance of the SqlDependency class; and what is the independence of the database in the real world for most applications? But if your priority is database independence, you cannot use this.

Polling is my second favorite because it keeps the database clean from triggers and application logic; this is really not a bad option, since, as you say, it is simple. If application B can wait several minutes at a time before "noticing" database changes, this would be a good option.

So my answer is: it depends. :)

Good luck

Answer 4

• Do you really care about database independence?
• Would it be difficult to create a difference mechanism for each type of database, all of which have the same open interface?

I know OracleDependency in ODP.NET, but what about other databases?

SQL Server has something similar, but I have never used it.

Answer 5

You can create the MySqlDependency class and implement SqlDependency or SqlDependencyForOracle (union)

Answer 6

You can use the SQL trigger inside the SQL CLR Database Project and run your code in this project, see https://msdn.microsoft.com/en-us/library/938d9dz2.aspx

Or, when you run inside the SQL CLR Database Project you can query from the SQL CLR Database Project to the project that you really want to activate with the trigger.