4

I'm currently developing an application in ASP.NET using SQL as the backend database. In my header, I have a status bar that displays the current status of the user and a timer that shows how long they've been labeled as that status.

Using an update panel with a timer to refresh the data straight from the database (thus making 1 query/update each second) doesn't seem like the best way to go about this. I would assume you'd want to query the user's "start time" for their status and use a javascript timer to keep track from there, but my problem is this; how would I integrate this method while also making sure that the status itself is updated when it's changed in the database?

Improve this question

1 Answer 1

Reset to default
1

SQL Server has a feature called query notification. The application submits a query and some parameters. This is know as a subscription. If data changes so the result of this query would be different with the new data, the notification fires.

From BoL:

Query notification subscriptions must be created from a database access interface. Transact-SQL does not provide a mechanism for creating query notification subscriptions.

That link has several options for creating a notification. There are restrictions on what the query may look like.

Query Notifications are processed through Service Broker (SB), so that has to be configured on the instance. When the notification fires a message is inserted into the stated queue. These queues are no different than other SB queues so all the ACID and authorisation rules apply.

The application listens on this queue and reacts when the message arrives. The application can poll or have a thread sitting blocked, waiting for a message. When the notification is sent the subscription is dropped. The application must submit a new subscription to receive further notification. This may cause race conditions, or allow intervening data changes to be missed.

Be careful, if you have many active clients and frequently changing data as it can add a significant load to the server.

Improve this answer
1
  • Could you explain a bit more about how this would work? Commented Jun 9, 2015 at 3:16

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.