SQL Dependency and SignalR in a 3 Tiered Architecture

Question

We have a web application that consists of a a 3 layered back end (Controller/Biz/Data) with a UI. Our data layer is solely responsible for pulling the data out of the database instance (SQL), the business layer messages the data and creates derived properties, the controller is responsible for sending those changes to the UI.

We have a need to have real-time updates in our application that MUST be tracked at the database level (not the controller level).

We opted to use SQL Dependency and SignalR as our solution.

Everything I have researched about SignalR and SQL Dependency is at the database level, where SQL Dependency will identify the change and broadcast, all within the data layer. For obvious reasons this methodology would bypass the derived properties created in the business layer and give us a different looking object.

The only solution I can think of is to use SQL Dependency to track the changes, dump them into some table/object, then use polling to fetch those from the controller, to the biz layer, to the data layer, and back up.

• Question #1: Is there a better solution?
• Question #2: What is the best solution to include the business layer logic, but still be able to track the changes at the data layer?
• Question #3: Is this possible without polling?

Answer 1

Your data layer is catching events raised by the database. Have it map the data into the appropriate DTO, then raise an event to be caught by the Business Layer. The Business Layer can then raise an event to the View layer, which can do the SignalR Broadcast(). Bubble up!