SQL Server: wait for new row

Question

How can I wait for a new row appearing in a SQL Server table from a .NET client application?

Background: I'd like to build a message queue based on a table. I don't want to use Service Broker because I want messages to be structured and strongly typed. Using a normal table I can have relations with other tables, too.

Answer 1

The normal way is to use polling, but if you really need immediate events you can use the SqlDependency class to get an event when the result of a query changes.

Answer 2

You need to either poll or get notified.

Polling would require the client to query the table periodically, either using a predicate or for count(*). This is probably not a great solution.

It could be a cheap but useful notification system to put a trigger in the table and send a message to a listener in the client.