1

I have a Windows Service that is supposed to wait for database changes.

The database table it looks at looks like this :

Id [Guid]

Name [text]

MatchId [Guid] => this is a foreign key

MatchDateTime [DateTime]

I would like my service to detect whenever someone 'cleared' the 'MatchId' field (assigned NULL to it),

and then for all the rows that have 'NULL' in their 'MatchId' field - perform some algorithm that finds a match for them and assigns the 'MatchId' field.

Problem is - if I use the 'SqlDependency' class, and wait for it to trigger on the 'MatchId' field - I will only get notified when new rows are affected, but any existing rows with 'MatchId = NULL' - I will not know about.

This means that if the Windows Service loads, and there are already 20 rows in the table with 'MatchId = NULL', and for the next 5 hours no one will 'clear' the 'MatchId' row - this means that I will not know about the existing 20 rows and will not handle them.

One solution might be that before I setup the 'SqlDependency' - I should perform a quick 'Select' to see if there are any existing rows with 'MatchId = NULL', and if so - handle them, and only then setup the dependency.

The problem is - in the short interval between finishing to handle those rows, and setting up the SqlDependency - there might occur an event where someone changed a row to 'MatchId = NULL', and then again - I wouldn't catch it in the SqlDependency.

Any idea how this can be solved ?

(If the worst comes to the worst, I will just not use SqlDependency, and I will just have to poll the database every XXX minutes ...)

Community
  • 1
  • 1
John Miner
  • 893
  • 1
  • 15
  • 32
  • What's wrong with polling the database? It's much more simple. – Bryan Crosby Jul 05 '12 at 15:16
  • Why don't you lock the table, run your check, do your stuff in case of MatchID == NULL and then unlock the table and set up the service? – Sergio Rosas Jul 05 '12 at 15:16
  • Have you thought about using a database trigger? That's basically what you're describing here. – Richthofen Jul 05 '12 at 15:26
  • @Sunrias - with your suggestion if I lock the table, run my check, do my stuff and then unlock the table and setup the service - in the time between the unlocking of the table, and the setting up of the service - there might be a change to the database. So that's not good, right ? – John Miner Jul 05 '12 at 15:47
  • @Richthofen - how can I use a database trigger in my C# code ? and the database trigger doesn't help me with 'existing' rows that need to be handled, right ? – John Miner Jul 05 '12 at 15:48
  • @BryanCrosby - I just thought that polling will hit the database performance, no ? – John Miner Jul 05 '12 at 15:48
  • @JohnMiner: How big are we talking about here? Do you have more than one service and server reading the table? Perhaps you can provide this detail so we can help you get a better answer. – Bryan Crosby Jul 05 '12 at 16:20
  • A trigger can definitely help you handle existing rows; you can essentially execute arbitrary SQL in your trigger to update any rows. And while triggers are typically SQL statements, you can write them in C#as well. http://msdn.microsoft.com/en-us/library/938d9dz2(v=vs.80).aspx – Richthofen Jul 05 '12 at 16:48

1 Answers1

1

I understand, that I am late with this answer, but you didn't update SO, which approach you choose :)

You can start SQLDependency first, and then run select query to check existing records. In other words, you will poll for null records each time after SQLDependency started( initially or after change)

The same approach is suggested in the answer to the question How to figure out which SQLDependency triggered change function?

First and foremost: the handler has to be set up before the command is executed. Otherwise you have a window when the notification may be lost and your callback never invoked

.

Community
  • 1
  • 1
Michael Freidgeim
  • 26,542
  • 16
  • 152
  • 170