SQL and SignalR dependency in a three-tier architecture

Question

We have a web application consisting of a three-layer rear end (Controller / Biz / Data) with a user interface. Our data layer is fully responsible for extracting data from the database instance (SQL), the business layer transfers data and creates derived properties, the controller is responsible for sending these changes to the user interface.

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

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

Everything I’ve explored about SignalR and SQL Dependency is at the database level, where SQL Dependency will detect the change and broadcast, all within the data layer. For obvious reasons, this methodology will dispense with derivative properties created at the business level and give us another promising object.

The only solution I can come up with is to use SQL Dependency to track changes, dump them into some table / object, and then use a poll to retrieve data from the controller, level layer, data layer and back up.

• Question # 1: Is there a better solution?
• Question # 2: What is the best solution that includes business-level logic, but can you still track changes at the data level?
• Question No. 3: Is this possible without a survey?

Answer 1

Your data layer captures events triggered by the database. Do you offer to map the data to the corresponding DTO, and then raise the event that should be caught by the business layer. Then, the Business Layer can raise the event to the View layer, which SignalR Broadcast () can do. Bubble!

Answer 2

SqlDependency leaves the trash in the database that you are tracking and does not empty it after itself. Avoid using! Instead, use the open source implementation of SqlDependencyEx . It is quite easy to configure and use:

 // See constructor optional parameters to configure it according to your needs var listener = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable"); // e.Data contains actual changed data in the XML format listener.TableChanged += (o, e) => Console.WriteLine("Your table was changed!"); // After you call the Start method you will receive table notifications with // the actual changed data in the XML format listener.Start(); // ... Your code is here // Don't forget to stop the listener somewhere! listener.Stop(); 

With the component mentioned above, you can even track the actual changed data that you can get from the event handler event arguments. Hope this helps.