Notify C# application when record inserted in sql server

The scenario is to notify c# application when a new record inserted into sql server 2008 database. we can use this approach in many cases for example. If student comes to school and he/she marked present/absent you may notify his/her father via email/mobile message that your son is present or absent today.

you will require the following.

1. sql server 2008
2. visual studio 2012
3. install SqlTableDependency 4.0.0.5 via Nuget package manager
4. specify your table to notify on insertion
5. Enable service broker for database

Create New windows form project in visual studio.

Install SqlTableDependency 4.0.0.5 In package manager console write the following and press enter

PM>  Install-Package SqlTableDependency -Version 4.0.0.5

Enable the service broker for database using the following t-sql (database name is test in my case)

ALTER DATABASE [test] SET ENABLE_BROKER

Add the following model for Attendance table

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MessagingAP
{
    public class Attendance
    {
        public int AttendaceID { get; set; }
        public int StudentID { get; set; }
        public int Year { get; set; }
        public int Month { get; set; }
        public int Day { get; set; }
        public string AttendaceState { get; set; }

    }
}

Add the following class to your project. (Table_Watcher)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TableDependency;
using TableDependency.Enums;
using TableDependency.Mappers;
using TableDependency.SqlClient;

namespace MessagingAP
{
   public class Table_Watcher
    {
       public string _connectionString ="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=NORTH\SQLSERVER2008;";
       // System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
       private  SqlTableDependency<Attendance> _dependency;
       public void WatchTable()
       {
           var mapper = new ModelToTableMapper<Attendance>();        
           mapper.AddMapping(model => model.Day, "Day");
           _dependency = new SqlTableDependency<Attendance>(_connectionString, "Attendance", mapper);
           _dependency.OnChanged += _dependency_OnChanged;
           _dependency.OnError += _dependency_OnError;
           
       }

       public void StartTableWatcher()
       {
           _dependency.Start();
       }
       public void StopTableWatcher()
       {
           _dependency.Stop();
       }
       void _dependency_OnError(object sender, TableDependency.EventArgs.ErrorEventArgs e)
       {
          
           throw e.Error;
       }

       void _dependency_OnChanged(object sender, TableDependency.EventArgs.RecordChangedEventArgs<Attendance> e)
       {
          
               if (e.ChangeType != ChangeType.None)
               {
                   switch (e.ChangeType)
                   {
                       case ChangeType.Delete:
                           
                           break;
                       case ChangeType.Insert:
                           if(e.Entity.AttendaceState.ToLower()=="p")
                           {
                               // Send Present Message Here

                              MessageBox.Show("Student is present");
                           }
                           else if(e.Entity.AttendaceState.ToLower()=="l")
                           {
                               // send Leave Message here

                              MessageBox.Show("Student is Leave");
                           }
                           else if(e.Entity.AttendaceState.ToLower()=="a")
                           {
                               // send absent Message here

                              MessageBox.Show("Student is absent");
                           }                            else                           {                                // send Other Message here                           }                            System.Windows.Forms.MessageBox.Show(e.Entity.StudentID + " Inserted");                            break;                        case ChangeType.Update:                                                       break;                    }                                  }            }             }      }

Add the following code to form load Event type

private void Form1_Load(object sender, EventArgs e)
        {
            Table_Watcher tw = new Table_Watcher();
            tw.WatchTable();
            tw.StartTableWatcher();
            
        }

Now insert row in attendance table from sql direct or from c# application you will get the notification
if you need tables script for student and Attendace tables run the following scripts

CREATE TABLE [dbo].[Student](
 [StudentID] [int] IDENTITY(1,1) NOT NULL,
 [StudentName] [varchar](150) NULL,
 [Address] [varchar](200) NULL,
 [Phone] [varchar](20) NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
 [StudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT [dbo].[Student] ([StudentID], [StudentName], [Address], [Phone]) VALUES (1, N'Tariq', N'Jalalabad', N'+93782974051')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Address], [Phone]) VALUES (2, N'zamary', N'Kabul', N'+93781203050')

Attendace Table

CREATE TABLE [dbo].[Attendance](
 [AttendaceID] [int] IDENTITY(1,1) NOT NULL,
 [StudentID] [int] NULL,
 [Year] [int] NULL,
 [Month] [int] NULL,
 [Day] [int] NULL,
 [AttendaceState] [varchar](50) NULL,
 CONSTRAINT [PK_Attendance] PRIMARY KEY CLUSTERED 
(
 [AttendaceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Attendance] ON
INSERT [dbo].[Attendance] ([AttendaceID], [StudentID], [Year], [Month], [Day], [AttendaceState]) VALUES (1, 1, 2016, 3, 1, N'p')
INSERT [dbo].[Attendance] ([AttendaceID], [StudentID], [Year], [Month], [Day], [AttendaceState]) VALUES (2, 1, 2016, 3, 2, N'p')