Click here to Skip to main content
14,359,963 members
Rate this:
Please Sign up or sign in to vote.
Dear Friends

I have a SQL DB, in which we have 1 table called as events. This table gets populated with lots of events within each sec.

I need something like file-watcher to watch this table as soon as new row(event) is inserted i want to pull it and display it in my application.

I don't want to do it with triggers and if i am running query in each sec. the query is returning the value after 39 sec. so this is not helping.

I appreciate if anyone can help me in this.

Thanks in advance !!!

Have a good day.

updated
Still Struck with this.... Please anyone can advice on this....
Posted
Updated 17-Jul-14 4:48am
v2
Comments
Mike Meinz 15-Jun-13 6:34am
   
If the query of an "events" table takes 39 seconds, you've got a performance issue in the design of the table and its indexes or in the hardware used.

I think polling the SQL Server the way that you are doing now is the way to go if the data must be in SQL Server and you are unable to use MSMQ.

Although I am not an expert in this area, I have seen other developers use <a href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms711472(v=vs.85).aspx" target="XX">MSMQ</a> to send event messages to a "watcher" program rather than put them into a database table. The "watcher" program could put the event messages into the database if you really need them archived.
Amaan23 15-Jun-13 14:52pm
   
Thanks for your reply.

I have no control over the table or hardware thats why i cannt use MSMQ :(

My scope is only to connect and get the newly row(event) and display it on web page. -- Live events

Thanks
Rob Branaghan 17-Jun-13 4:19am
   
Do you have access to the stored procedures that write to the database?
Amaan23 17-Jun-13 7:25am
   
Yes i can write Stored procedure to the database
Rate this:
Please Sign up or sign in to vote.

Solution 1

Hi,

You can do something like this:

Create a stored procedure as:

select     top 1 e.EventID, e.EventName, ....
from       dbo.events e
order by   e.EventID desc


and call this stored procedure every 2 seconds and check in the front end whether this event is fetched. If not, then add it to display otherwise ignore it.

I still don't think this is a good idea but since you do not have control over the insert stored procedure, this could be the only way you can do it.

If you have control over the insert stored procedure, then just insert the following just after the insert statement:

declare @newEventID bigint
set @newEventID = @@IDENTITY

select     e.EventID, e.EventName, ....
from       dbo.events e
where      e.EventID = @newEventID


Hope this helps.

Regards,
Nayan
   
Comments
Amaan23 23-Jun-13 9:07am
   
sorry for delayed reply, i was out of city.

I tired this but still no luck. once we have order by time, it takes lot of time to execute it.

Thank for your help
Rate this:
Please Sign up or sign in to vote.

Solution 2

All what you want is: SqlDependency class[^]. Here is interesting implementation: Using SqlDependency for data change events[^]
   
Comments
Amaan23 18-Jul-14 3:32am
   
Thanks, this is something new which i was not aware. Let me check and try out.

Still have doubts on above as my table(Time Attendance Table In and Out info) will have approx 50 events getting inserted in table in 2 min.

Well lets see. Thank you for your help.
Maciej Los 18-Jul-14 16:05pm
   
You're welcome ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100