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;
protected virtual void Dispose(bool disposing) {
if (!disposedValue) {
if (disposing)
{
_sqltableDependency.Stop();
}
disposedValue = true;
}
}
~ReminderClient() {
Dispose(false);
}
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
- 8th December, 2019: Initial version
