How to query a database continuously with C#?

Question

I would like to write C# code which would help me to query a database continuously.

If there is an entry made (not sure when will the entry be made) to a database by some source, it should be processed by my code. (Simply, my code should always monitor the database for an entry).

This question is a bit broad, so am voting to close - it was probably acceptable at the time of writing but guidelines here have tightened up a bit since then. With regret I've downvoted, as there are plenty of good answers below, but you did not reply or accept. — halfer, Dec 16 '15 at 20:17

score 1 · Answer 1 · edited May 23 '17 at 11:44

You basically have two main options.

Firstly, you can add an extra column to your table that is a bit field, perhaps called "HasBeenProcessed". You can then schedule checking for any columns whose HasBeenProcessed is 0. Processed that, then update the column to be 1.

Alternatively, you can look at this StackoverFlow post that describes using the SqlDependency class to have an event raised when a resultset appears differently.

score 1 · Answer 2 · answered Jul 18 '12 at 04:28

If you are using SQL Server 2008 R2 or SQL Server 2012, you can use StreamInsight to setup events against data changes and patterns. If you are using a previous version > 2005, you can use Notification Services (although StreamInsight is far superior and essentially supercedes Notification Services).

Stefan Steiger · Answer 3 · 2012-07-18T08:54:50.180

1

Add a WebService to your code.
Add a insert & update trigger to your table.
Create a CLR function that you can call from the trigger.
In that CLR function, call the webservice with all necessary data.
In the webservice, do whatever you need to do based on the passed parameters.
Add a configuration table, and enter the webservice URL.
Pass the url as argument to your CLR function.
You should call the webservice asynchronously, so the delay is minimized.

Note:
I think it's also possible to call a web-service from pure SQL, so there's not necessarely a need for the CLR.

edited Jul 18 '12 at 08:54

answered Jul 18 '12 at 08:48

Stefan Steiger

78,642
66
377
442

This helps..Thanks a lot Stefan..Let me try this option – user1533486 Dec 17 '15 at 16:17

score 1 · Answer 4 · answered Jul 18 '12 at 13:18

What are you trying to accomplish? If it's keeping a relatively static but still possibly changing data set (e.g. list of vendors) up to date in a cache, Simon Whitehead's proposal of SqlDependency will serve you well. If it's processing something more akin to requests (e.g. orders), service broker with activation, either internal or external, should be a good fit. Either way, the point is don't have your code constantly going to the database saying "is there something for me?". Instead, have the db tell your application when there's something to do and act accordingly.

How to query a database continuously with C#?

4 Answers4