Reputation: 399

Getting an alert after a new database row inserted.

I have created an application to store a SMS text into a database table. Since I can not predict about when an SMS will be received; I need to find a way to get notified when there is a new row added to the database table. I am using MSSQLServer 2005 and a Windows forms application using C#. Can somebody suggest me a way to do this? Thank you.

Upvotes: 2

Answers (6)

MWH

Reputation: 399

I used the timer object in c# to periodically check new database updates. Thank you all for your answers.

Upvotes: 0

Remus Rusanu

Reputation: 294387

You cannot use a trigger, despite the overwhelming voice favoring such an erroneous way, at least not to directly notify the application. There is simply no way from the trigger to connect back to the application to notify the change, and solution like 'use a socket' or 'use mail' are naive at best and they fail in flames under real world conditions.

You have basically three alternatives:

pool for changes. Easiest to implement, simply query periodically to see if a new record appeared. The drawback is that 1) is sometimes difficult to detect the change, depending on your data model schema and 2) there is a difficult to achieve balance between latency and load, ie. how often should you poll.
notify form the inserting application. Those records are inserted by application, have the code that inserts the record also notify your application. Requires changes to code that is often not under your control.
use Query Notifications.

The fourth alternative is to use a trigger to send a message to a local queue and have the application dedicate wait in WAITFOR(RECEIVE...) in the background (this is not the same as polling) but this better done by leveraging Query Notifications and SqlDependency.

Upvotes: 4

PeteH

Reputation: 2454

You don't give enough detail in your question but I'm assuming you mean that a row gets inserted into the database (somehow) and you want the c# application to know about it? (otherwise, just use triggers as many other people have said)

Under these circumstances I'd look at having the database send some kind of "ping" via udp or tcp/ip, and writing a corresponding listener component in your c# application.

I just googled this and some relevant links appear to be http://www.codeproject.com/Articles/8973/SQL-Server-extended-stored-procedure-to-send-UDP-m and http://lamahashim.blogspot.co.uk/2009/06/using-c-udpclient-send-and-receive.html

If you take this approach you'll need to be aware of any firewall issues etc. which might block comms, you might also want to consider things like "what happens when the c# app isn't running" etc.

Upvotes: 0