2

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.

MWH
  • 399
  • 1
  • 5
  • 19
  • 2
    presumably when you say "notified" you mean the c# application? – PeteH Sep 11 '12 at 12:49
  • 1
    @Pete Yes it means C# application. – MWH Sep 11 '12 at 14:16
  • then check out my answer below. Hopefully the snippets in the links will contain something useful for you to build upon. I designed and delivered an alerting component along these lines for some clients, and its used in a bunch of apps where the server wishes to prod the client. I built it originally for UDP (more lightweight) but they extended it to tcp/ip and its used across a couple of their sites. – PeteH Sep 11 '12 at 15:08

6 Answers6

4

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • @Pete: the problems with tcp/udp are manyfold: 1. Security (authentication and authorization from the client that it *is* the the server SP sending the notification, not a rogue process) 2. NAT traversal (connecting from server to client) 3. Availability (SP failure due to network failures). You do mention some of these problems in your post, reckon that. I've seen attempts to do this approach, and they *seem* to work until they start failing. The added coupling introduces cascading failures and is very hard to troubleshoot. – Remus Rusanu Sep 12 '12 at 08:41
  • @Pete: at the very least one could consider using a queueing technology ([MSMQ](http://msdn.microsoft.com/en-us/library/windows/desktop/ms711472(v=vs.85).aspx), [ZeroMQ](http://www.zeromq.org/), [RabbitMQ](http://www.rabbitmq.com/) etc etc) instead of raw TCP to decouple the server from the client, primarily to avoid the availability issues. – Remus Rusanu Sep 12 '12 at 08:43
  • Now there I agree, I'm a big fan of messaging and in fact anything that helps to decouple things from each other. As a matter of fact in an Oracle environment we had stored procedures fire AQ messages which were fielded by a .net listener - this was a brilliant way of getting control up to the business tier, where it belongs. The main factor in udp, then tcp/ip over queues was lightweightedness. – PeteH Sep 12 '12 at 09:26
  • @Pete: but with SQL Server you have built-in queues (Service Broker) which, being integrated into SQL storage and transaction management, are extremely lightweight *and* decoupled, and is pure T-SQL too boost. This ends up being the fourth alternative in my answer, but overall I would still recommend Query Notifications instead. – Remus Rusanu Sep 12 '12 at 09:41
2

You can use triggers combined with DatabaseMail to send an email to alert you.

saj
  • 4,626
  • 2
  • 26
  • 25
  • Are database triggers evil? http://stackoverflow.com/questions/460316/are-database-triggers-evil – Dennis Traub Sep 11 '12 at 12:51
  • 2
    They have their uses, and in the current context, i.e. sending an alert, I would consider them to be useful. Complications arise when data is altered through them. – saj Sep 11 '12 at 12:53
  • 1
    @DennisTraub: I'm going to go out on a limb here and say that triggers are well suited if used properly and are not **evil** per say. Can they be abused? You bet! But everything can be abused and many fall into that by following the http://en.wikipedia.org/wiki/Law_of_the_instrument. Staying away from that will help you find the right technology for the problem. – Mike Perrenoud Sep 11 '12 at 12:55
  • @Mike this is exactly what the answer in the link I posted states. I personally have a problem with a trigger here because it probably encapsulates business logic, i.e. "When a new record is created then someone needs to be notified". In my opinion this belongs in the business layer (or domain model, or transaction script), not the database. Triggers can be useful to enforce integrity, not business processes. – Dennis Traub Sep 11 '12 at 12:59
  • 3
    @DennisTraub: and I can understand what you're saying. However, this business process is clearly fine-grained and has no side effects unlike many triggers I've encountered that I killed. I guess it's just that the longer I'm in this thing the less I believe there are hard and fast rules regarding when to use specific technologies. I guess we'll never agree on this one. – Mike Perrenoud Sep 11 '12 at 13:02
  • 1
    @Mike I agree with "no hard and fast rules", I don't believe in ultimate "best practices" either. Just wanted to throw in that triggers should be handled with care. – Dennis Traub Sep 11 '12 at 13:04
0

Yes, use an INSERT trigger.

Here is a Stackoverflow article: SQL Server 2008 - Help writing simple INSERT Trigger

Here is the T-SQL reference: http://msdn.microsoft.com/en-us/library/ms189799.aspx

Community
  • 1
  • 1
Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
0

Best suited solution for this is TRIGGER after insert

AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
0

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.

PeteH
  • 2,394
  • 1
  • 20
  • 29
0

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

MWH
  • 399
  • 1
  • 5
  • 19