6

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?
vard
  • 4,057
  • 2
  • 26
  • 46
mtsuggs
  • 91
  • 5

2 Answers2

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!

REngber
  • 11
  • 3
0

SqlDependency leaves trash in the database you track and doesn't cleanup after itself. Avoid using it! Use an Open-Source realization instead - SqlDependencyEx. It is pretty 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 which you can get from the event args of the event handler. Hope this helps.

Community
  • 1
  • 1
dyatchenko
  • 2,283
  • 3
  • 22
  • 32
  • This does not resolve my issue as this type of code/logic should not be at the business level. The business level should not aware of anything involved with the schema and should only work on the DTO's. – mtsuggs Jan 11 '16 at 14:02
  • To what end? So you can sit back and admire that you maintained the 3 tier concept, at the expense of a solution? We use these tools to solve problems. Not every problem is going to fit the latest idea of design patterns we all like this decade. You have a solution, so you blur your controller and business layer a bit. Big deal. Just keep the view or end user from direct data access and you're still golden. – Jason Jan 12 '16 at 12:56