0

I want to get notified when a certain change occurs in Database table. Consider the case: I want to perform a certain action when the column in a row changes its value to 5. How can I achieve it. I am using C# and entity framework to access the database.

Madhur Maurya
  • 1,016
  • 4
  • 19
  • 42
  • 1
    Have you done any research? Any nifty code to show? This will help us help you. Good luck! –  Oct 27 '16 at 05:53
  • I know a little about triggers. But triggers require to be created in database like stored procedures and also I want to get notified when value changes, (any way to know that the required change occurred) which I think is not possible with triggers. Any more suggestions are most welcome. – Madhur Maurya Oct 27 '16 at 05:58
  • @MadhurMaurya: tell us about what your application is. Web/desktop, client-server/3(n)-tier, intranet/internet? – Dennis Oct 27 '16 at 06:00
  • 1
    @Dennis A webservice changes the value inside database table. I have unit test framework. I want to verify this action of change. Since the value gets set only for small duration say 1 second, I want to get notified if the column/cell was set to desired value or not. – Madhur Maurya Oct 27 '16 at 06:04
  • @MadhurMaurya: if you just want to check data for modifications from your test framework, query them before running test, run test, then re-query data once again, and compare data. Honestly, I don't understand, why does the question ask for some notification. In fact, this is polling from Jim's answer, but you even don't need to poll server periodically - only before test and after it. – Dennis Oct 27 '16 at 06:13
  • @Dennis Here it is. The value gets set for only a small time duration (say status column gets set to 5). By the time I check this value the value is already set to some different value (say it becomes 10). That is why I want to get notified if the value was set to 5 or not. Or any other way to know this. – Madhur Maurya Oct 27 '16 at 06:27
  • @MadhurMaurya: wait a minute. :) Who else can set values? I thought, that it is a test environment - single database, single client (test). You should provide more info. – Dennis Oct 27 '16 at 06:40
  • I call a web service inside my test method. Which in turn changes the database value. This change in database column is momentarily that I want to verify (i.e before its value changes to some other value). – Madhur Maurya Oct 27 '16 at 06:45

3 Answers3

1

For this you have to make a schedule job which will continuously(like interval of 5 minutes) ping database and notify you as like Facebook's notification bar.

Also you can write trigger on that table which will insert/update notification table and from there you will get notify.

Husen
  • 1,541
  • 10
  • 14
  • "as like Facebook's notification bar" - I doubt, that it uses server polling. Most probably, it is web sockets-based app. – Dennis Oct 27 '16 at 05:56
  • @saddam_msp Thanks saddam. What if I do not want to use a separate table just to keep track of the change. – Madhur Maurya Oct 27 '16 at 05:59
  • @Dennis i just told that you will get notification. Madhur to keep track you have multiple option as like above you can user table of write log file in specific location on upload data over ftp etc.. – Husen Oct 27 '16 at 06:07
1

The short answer is that you should probably try and manage this outside of SQL server. I have to assume that you have some application logic executing outside of SQL server that is the source of the update. Ideally your notification logic should be placed in your application tier before or after the database is updated.

Should you not be able to achieve this, three other options I can offer are:

  • polling You build a service that reads the value from SQL server in a loop. The loop should read the value periodically, and perform the notification. Most engineers avoid polling as from a best practices standpoint it is typically contra indicated due to adding persistent load to the database. Although polling should be avoided, it's surprisingly common in the field.

  • msmq You update the value via a stored procedure, and use this article to send a message to MSMQ when the value is 5. You will need to write a service to consume the MSMQ message and process the notification. You may use a WCF service using MSMQ transport to make this easy.

  • email You send an email using sp_send_dbmail in the update stored procedure, and build the necessary notification consumer(s). It should be noted that this method will likely also involve polling if you consume the email electronically. You can avoid that by using IMAP IDLE to process the email notifications. Try MailKit

Reporting services also apparently offers notifications, but I am not familiar with them.

Jim
  • 14,952
  • 15
  • 80
  • 167
-1
using(var context = new FooEntities)
{
    try
    {
    var customer = context.Customers.First(i=> i.CustomerID = 23);
    customer.Name = "Bar";
    context.SaveChanges();
    //Write your notification code here
    }
    catch(Exception ex)
    {
        //Write notification along with the error you want to display.
    }
}

Search in google there's many different way of displaying a notification.

Carlo Luisito
  • 179
  • 1
  • 15