CODEWITHSUNDEEP
c#databaseado.netodp.net
Theo Lenndorff
Theo Lenndorff

Reputation: 4592

Some data changes in the database. How can I trigger some C# code doing some work upon these changes?

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

The basic problem is: Some data changes in the database. How can I trigger some C# code doing some work upon these changes?

To give some stimulus for answers, I mention some approaches, which I am currently considering:

  1. Make application B polling for changes in the tables of interest. Advantage: Simple approach. Disadvantage: Lots of traffic, especially when many tables are involved.
  2. Introduce triggers, which will fire on certain events. When they fire they should write some entry into an “event table”. Application B only needs to poll that “event table”. Advantage: Less traffic. Disadvantage: Logic is placed into the database in the form of triggers. (It’s not a question of the “evilness” of triggers. It’s a design question, which makes it a disadvantage.)
  3. Get rid of the polling approach and use SqlDependency class to get notified for changes. Advantage: (Maybe?) Less traffic than polling approach. Disadvantage: Not database independent. (I am aware of OracleDependency in ODP.NET, but what about the other databases?)

What approach is more favorable? Maybe I have missed some major (dis)advantage in the mentioned approaches? Maybe there are some other approaches I haven’t think of?

Edit 1: Database independency is a factor for the ... let's call them ... "sales people". I can use SqlDependency or OracleDependency. For DB2 or other databases I can fall back to the polling approach. It's just a question of cost and benefit, which I want to at least to think about so I can discuss it.

Upvotes: 6

Views: 7620

Answers (6)

Răzvan Flavius Panda
Răzvan Flavius Panda

Reputation: 22106

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

Or, on trigger inside the SQL CLR Database Project you could make a request from the SQL CLR Database Project to the project you actually want to act on the trigger.

Upvotes: 0

Avram
Avram

Reputation: 4259

You can make an MySqlDependency class, and implement SqlDependency or SqlDependencyForOracle (pooling)

Upvotes: 0

Allain Lalonde
Allain Lalonde

Reputation: 93328

I'd go with #1. It's not actually as much traffic as you might think. If your data doesn't change frequently, you can be pessimistic about it and only fetch something that gives you a yay or nay about table changes.

If you design your schema with polling in mind you may not really incur that much of a hit per poll.

  • If you're only adding records, not changing them, then checking the highest id might be enough on a particular table.

  • If you're updating them all then you can store a timestamp column and index it, then look for the maximum timestamp.

  • And you can send an ubber query that polls multiple talbes (efficiently) and returns the list of changed tables.

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

Upvotes: 1

glennkentwell
glennkentwell

Reputation: 479

I think you have covered the approaches I have thought of, there's no absolute "best" way to do it, what matters are your requirements and priorities.

Personally I like the elegance of the SqlDependency class; and what does database independence matter in the real world for most applications anyway? But if it's a priority for you to have database independence then you can't use that.

Polling is my second favourite because it keeps the database clean from triggers and application logic; it really isn't a bad option anyway because as you say it's simple. If application B can wait a few minutes at a time before "noticing" database changes, it would be a good option.

So my answer is: it depends. :)

Good luck!

Upvotes: 1

matt b
matt b

Reputation: 139921

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

Upvotes: 1

Jonathan Allen
Jonathan Allen

Reputation: 70307

  1. Do you really care about database independence?
  2. Would it really be that hard to create a difference mechanism for each database type that all have the same public interface?

I am aware of OracleDependency in ODP.NET, but what about the other databases?

SQL Server has something like that, but I've never used it.

Upvotes: 0

Related Questions