Making SQL Server table observable in Rx and C#

Question

I am researching on potential of Rx.NET to stream an sql server data table. Basically I would like to get the data as closer when its inserted as possible without resorting to polling.

I made some POC using Rx/DataReader but I cannot subscribe when new rows are inserted. The stream stopped when there are no more rows to pick (of course this is expected).

Constraints:

• No configuration changes on the SQL Server and databases
• Possibly no polling

I thought of these solutions but those requires license or server changes:

• StreamInsight
• SQLDependency
• Triggers

Is there something like SQLBrite in .NET?

Answer 1

The effort and pain necessary to use Service Broker is huge (Message types, Contracts, Queues, etc), if you're not a SQL Server guy don't even consider.

There's an open source implementation that seems to help. You can check it out: https://tabledependency.codeplex.com - it might be more than you expect to solve your problem.

Simpler than that you can use/play with SqlDependency - https://msdn.microsoft.com/en-us/library/62xk7953(v=vs.110).aspx In a few lines of code you can watch table changes (with some limitations), just watch out for the app, and database performance.

None of them provide an IObservable<T> out of the box. But once you have a DataChanged, OnDependencyChange event, or something similar. You can abstract that with Rx, converting .net events to observables - https://msdn.microsoft.com/en-us/library/hh229241(v=vs.103).aspx