CODEWITHSUNDEEP
c#.netsqlsql-server-2008
ff8mania
ff8mania

Reputation: 1760

Notification from the db instead of polling. Is it possible?

Hy guys,

to monitor a specified table on db I'm polling this one every few milliseconds (10-20 ms).

Is there any chance to have a notification (avoiding SqlDependency, that in my scenario is too slow) instead polling?

Do you have any idea?

My scenario is .net + Sql Server 2008

Thanks!

Upvotes: 3

Views: 1999

Answers (5)

Sri Harsha
Sri Harsha

Reputation: 21

To get onChange event notification in SQL, you can use the Query Notification feature which is built on Server Broker.

Alternatives are:

  1. DB trigger
  2. SignalR
  3. Change Data Capture
  4. Change Tracking

Upvotes: 1

Michael Lloyd Lee mlk
Michael Lloyd Lee mlk

Reputation: 14661

Can the calling application be changed to say write to a queue instead?

If not I guess a trigger on the database which calls a CLR Stored Procedure? That could fire off any kind of event required.

Upvotes: 0

TX_
TX_

Reputation: 5466

I see you say you need notifications (and SqlDependency is available for that), but maybe you don't need instant notifications, and efficiently reading the changes periodically will do the task. If so, go google Change Data Capture, and Change Tracking.

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

It can be done, using a CLR stored procedure that will call a WCF/or a webservice. It is not something very difficult to do.

This needs practically 2 steps.

  1. The modification of data. After you modified the data you have to send the data to the clr stored procedure. The easiest way is to write it into one ore more temporary tables.

  2. The clr stored procedure. The clr store procedure will connect to the db with

    "context connection=true"

so that you will have access to the stored procedures that you need. After loading the data you send it to a server (WCF/webservice). In the CLR you just need to add the service references that you need. Also on the server you will have to register some dlls for the server to use: 

system.web
smdiagnostics
system.runtime.serialization
system.identitymodel
system.identitymodel.selectors
system.messagng
system.transactions.bridge
system.servicemodel

Everything else is plain .NET code to call a WCF/Web service. This approach is very fast and very reliable.

Upvotes: 1

ZombieSheep
ZombieSheep

Reputation: 29953

You could use the SqlChangeMonitor class, but that wraps SqlDependency in cached data scenarios. Your question is a little vague on why you want to do this, though.

Upvotes: 0

Related Questions