Daily reminder using SQL Agent and SignalR

Introduction

It is our obvious requirement to send notifications/reminders to our users via our application. These notifications may be emails, text/voice messages, etc. We have options available to automate this process such as database mail in SQL Server (links are available on the internet to enable and configure), we can create Windows service or we can write code on Application_Start method and a timer can do the job at a certain time on a daily basis. Recently, I got a requirement to send automated mail and text messages to the application users at 7am daily whether they are online or offline.

I do not want to use any of the above, so I decided to go with SignalR, SqlTableDependency, and SQL Agent. The idea is to create a separate table to record the notification on a daily basis, as this table is pinched by SQL Agent job our SignalR client initializes the SqlTableDependency. It will get the record(s) and send the mail/text messages via our ASP.NET application.

The Process

The above solution works only when the application as well as server is up. I am sharing the working code here which can be used to do similar tasks.

Step 1: Create the Following Tables

First, we are going to create some tables to save the user, tasks, etc.

CREATE TABLE [dbo].[tblUser](
                    [UserId] [int] NOT NULL,
                    [Name] [nvarchar](255) NOT NULL,
                    [Email] [nvarchar](500) NOT NULL,
                    [Mobile] [nvarchar](15) NOT NULL
 )
CREATE TABLE [dbo].[tblTask](
                    [TaskId] [int] NOT NULL,
                    [TaskCode] [nvarchar](50) NOT NULL,
                    [TaskDate] [date] NULL,
                    [AssignedTo] [int] NOT NULL,
                    [Status] [nvarchar](50) NOT NULL,
                    [Message] [nvarchar](500) NULL,
                    [ReminderStart] [date] NOT NULL
 )
CREATE TABLE [dbo].[tblDailyReminder](
                    [ReminderId] [int] IDENTITY(1,1) NOT NULL,
                    [TaskId] [int] NOT NULL,
                    [IsSent] [bit] NOT NULL CONSTRAINT [DF_tblDailyReminder_IsSent]  _
                    DEFAULT ((0))
)

Step 2: Create a Stored Procedure

This is the stored procedure which is going to be executed by SQL agent on a daily basis. Here, we are fetching some tasks which are pending and their reminder date has begun.

Create PROCEDURE [dbo].[sp_PushReminder]                     
AS
BEGIN                   
                    Delete From tblDailyReminder
                    Insert into tblDailyReminder(TaskId,IsSent)
                    Select TaskId,0 from tblTask _
                           where Status='Pending'and ReminderStart<=GETDATE()
END
Go

Note: We must enable the broker service to work with SqlTableDependency.

USE master;
GO
ALTER DATABASE <Database_Name> SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 3: Create a Job

Right click on SQL Agent ⇒Job⇒New job
Enter the general information such as name of job.

Click on steps on the left side pane.

Enter step name, select database and type “exec procedure name” in the command box. Click OK.
On the left side pane, click on schedules and schedule your job accordingly.

Step 4: Create SignalR Hub

[HubName("ReminderHub")]
   public class ReminderHub : Hub {
      private readonly ReminderClient _reminderClient;
       public ReminderHub() : this(ReminderClient.Instance) {
       }
       public ReminderHub(ReminderClient reminderClient) {
           _reminderClient = reminderClient;
       }
   }

Step 6: Create SignalR Client

public class ReminderClient {
        private readonly static Lazy<ReminderClient> _instance = new Lazy<ReminderClient>(
        () => new ReminderClient
              (GlobalHost.ConnectionManager.GetHubContext<ReminderHub>().Clients));
        private static SqlTableDependency<tblDailyReminder> _sqltableDependency;
        public Dictionary<string, string> DictUsers = new Dictionary<string, string>();
        private ReminderClient(IHubConnectionContext<dynamic> clients) {
            Clients = clients;
            var mapper = new ModelToTableMapper<tblDailyReminder>();
            mapper.AddMapping(s => s.TaskId, "TaskId");
            mapper.AddMapping(s => s.ReminderId, "ReminderId");
            _ sqltableDependency = new SqlTableDependency<tblDailyReminder>
            (ConfigurationManager.ConnectionStrings["LocalConStr"].ConnectionString, 
            "tblDailyReminder", "", mapper);
            _ sqltableDependency.OnChanged += SqlTableDependency_Changed;
            _ sqltableDependency.OnError += SqlTableDependency_OnError;
            _ sqltableDependency.Start();
        }
        public static ReminderClient Instance  {
            get {
                return _instance.Value;
            }
        }
        private IHubConnectionContext<dynamic> Clients {
            get;
            set;
        }
        void SqlTableDependency_OnError(object sender, ErrorEventArgs e) {
            throw e.Error;
        }
        void SqlTableDependency_Changed(object sender, 
                  RecordChangedEventArgs<tblDailyReminder> e) {
            if (e.ChangeType == ChangeType.Insert)
            {
                NotificationDemoEntities notificationDemoEntities = 
                                             new NotificationDemoEntities();
                var mailRecepients = notificationDemoEntities.tblDailyReminders.Where
                (x => x.ReminderId == e.Entity.ReminderId).Include
                (x => x.tblTask).Include(x => x.tblTask.tblUser).ToList();
                if (mailRecepients != null && mailRecepients.Count() > 0)
                {
                    SendMailNotification(mailRecepients.FirstOrDefault().tblTask.tblUser.Email, 
                    mailRecepients.FirstOrDefault().tblTask.Message);
                }
            }
        }
        private void SendMailNotification(string Tomail, string message)  {
            try
            {
                MailMessage mailMessage = new MailMessage();
                SmtpClient smtp = new SmtpClient();
                mailMessage.From = new MailAddress("FromMailAddress");
                mailMessage.To.Add(new MailAddress(Tomail));
                mailMessage.Subject = "Test";
                mailMessage.IsBodyHtml = true;
                mailMessage.Body = message;
                smtp.Port = 587;
                smtp.Host = "smtp.gmail.com";
                smtp.EnableSsl = true;
                smtp.UseDefaultCredentials = false;
                smtp.Credentials = 
                new NetworkCredential("FromMailAddress", "password");
                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                smtp.Send(mailMessage);
            }
            catch (Exception) {
            }
        }
        #region IDisposable Support
        private bool disposedValue = false; // To detect redundant calls
        protected virtual void Dispose(bool disposing) {
            if (!disposedValue) {
                if (disposing)
                {
                    _sqltableDependency.Stop();
                }
                disposedValue = true;
            }
        }

        ~ReminderClient() {
            Dispose(false);
        }
        // This code added to correctly implement the disposable pattern.
        public void Dispose() {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion
    }

Step 7: Register SignalR in Startup

Make sure that signalR is mapped in Configuration method of Startup class:

public class Startup{
        public void Configuration(IAppBuilder app)        {
            app.MapSignalR();
        }
 }

Step 8: Create Hub Proxy

We have to create the proxy of our hub, in order to make it available every time. I am going to do so in Application_Start() method of Global.asax class.

protected void Application_Start(){
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
           HubConnection hubConnection = 
                         new HubConnection("http://localhost:55027/signalr/hubs");
           hubProxy = hubConnection.CreateHubProxy("ReminderHub");
           hubConnection.Start();
  }

Step 9: Test the App

In order to test, we need to execute our job manually.

Right click on the job and start the job. As the job finishes, our code will be executed and notification will be sent via mail.

The same job will be executed daily or as you have scheduled it.

References
To know more about SqlTableDependency please  visit https://www.nuget.org/packages/SqlTableDependency.

Conclusion

We can send daily reminders to our users using the technique discussed above. Here, we have used SqlTableDependency object to catch the database changes, and we can also push app level notifications to the user at the same time we are sending the mail/Message. I think it can help other developers too. Suggestions/queries are welcome.

History

• 8^th December, 2019: Initial version