Transmit data to client on specific page, based on SQL Server column or row update

Question

I want to achieve something specific using ASP.NET and SQL Server. Let's for example that I have several pages, each one with each own identification (ie. id=1, id=5). Furthermore, let's assume that for each one of those id I have a row in the database:

So in short, what I want to achieve is: Pushing database changes in-directly to specific clients on specific pages while taking advantage of web sockets (persistent connection).

for example:

Row 1:

id = 1

name = myname1

Row 2:

id = 2

name = myname2

What I want to do is that when the specific row or even a specific value in a column changes, it will trigger an event that can send a specific data to ONLY those clients that are visiting the page with a specific id that was changed.

for example: if row 1 column name changed from 'name1' to 'name2', and the ID of the primary key is 5, I want all those who visit the page with id=5 to recieve an event in the client side.

I want to prevent myself for developing a client code that will contentiously send requests to a webservice and query that specific row by id to see if it was update or a specific column value was changed.

One solution that I thought about is to keep the key/value in memory (ie. memcache) like the key represents the id and the value will be the datetime lst updated. Then I can query the memory and if, for example, [5, 05/11/2012 12:03:45] I can know if they data was last updated by saving the last time I queries the memory in the client side, and compare the dates. If the client datetime value is older than the one in the key/value in the memory, then I would query the database again.

However, it's still a passive approach.

Let me draw it how it should work:

1. Client and Server have persistent connection [can be done using ASP.NET 4.5 socket protocols]

2. Server knows to differentiate between a connection that comes from different pages, those with different query strings for example, id=1, id=2, etc. One option I thought about is to create an array in memory that stores the connection Ids for each connection string id value. For example: {1: 2346,6767,87878, 2:876,8765,3455}. 1 and 2 are the page's identification (ie. id=1, id=2), and the other values are the connection ids of the persistent connection that I get using ASP.net 4.5

3. A column value in a row with primary key value id=5 has its column 'count' updated from value '1' to '2'.

4. A trigger calls a function and pass the id (let's assume value X) of the changed row. I prefer being able to also send specific columns' value ( some column of my choice) [this is done using CLR triggers]

5. The function has a list of connections, for the clients who are visiting the page with id with value X (a number)

6. The Server sends the client the column values, or if it's not possible, just send true or false, notifying the client that a change to that row has been taken place.

Solved until now:

1] Can be done using ASP.NET 4.5 socket protocols

4] Using CLR triggers I can have a function that gets to have the columns data and id of a specific row that was altered.

I am developing my app using ASP.NET 4.5.

Thanks

Answer 1

Sql Server Service Broker can accomplish a good portion of your requirements.

Service Broker allows for async messaging in sql server. Since it's asynchronous let's split up the functionality into 2 parts.

The first part is a trigger on the table that writes a message the the service broker queue. This is just straight T-SQL, and fairly straight forward. The payload of the message is anything you can convert to varbinary(max). it could be xml, a varchar(100) that contains comma seperated values, or some other representation.

The second part is the handling of the message. You issue a transact-sql RECEIVE statement to get the next message from the queue. This statement blocks until something arrives. A queue can have multiple conversations, so each client gets their own notifications.

Conceptually, it could work like this (Client is asp.net code):

1. Client opens Service Broker conversation .
2. Client sends a message which says "I'm Interested in Page=3)
3. Client does a RECEIVE which blocks indefinitely
4. UPDATE changes data for page=3
5. Trigger on table sends message to every conversation that is interested in Page=3
6. Client receives the message, and sends updated data to web browser.

No CLR required, no periodic polling of the database.