SQL
Function: NextBirthday
USE [Birthdays]
GO
/****** Object: UserDefinedFunction [dbo].[NextBirthday] Script Date: 29/03/2022 14:51:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[NextBirthday]
(
@DateOfBirth Date
)
RETURNS Date
AS
BEGIN
DECLARE @CurrentAge int
DECLARE @NextBirthday Date
/****** DateDiff(interval, Date1, Date2) ******/
SET @CurrentAge = DateDiff(year, @DateOfBirth, GETDATE())
/****** DateAdd(interval, number, Date) ******/
SET @NextBirthday = DateAdd(year,@CurrentAge,@DateOfBirth)
IF @NextBirthday < GetDate()
SET @NextBirthday = DateAdd(year,1,@NextBirthday)
RETURN @NextBirthday
END
GO
Stored Procedure: pPerson_GetByDaysToNextBirthday
USE [Birthdays]
GO
/****** Object: StoredProcedure [dbo].[spNextBirthday_GetByDaysToNextBirthday] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spPerson_GetByDaysToNextBirthday]
@DaysToNextBirthday int
AS
BEGIN
--SQL for Select stored procedure.
SELECT TOP (100) PERCENT PersonFirstName,
ISNULL(PersonFirstName, '') + ' ' + ISNULL(PersonLastName, '') AS PersonFullName,
dbo.NextBirthday(PersonDateOfBirth) AS NextBirthday,
DATEDIFF(day, GETDATE(), dbo.NextBirthday(PersonDateOfBirth)) AS DaysToNextBirthday,
DATEDIFF(year, PersonDateOfBirth, dbo.NextBirthday(PersonDateOfBirth)) AS AgeNextBirthday,
PersonSendReminderTo
FROM dbo.Person
WHERE DATEDIFF(day, GETDATE(), dbo.NextBirthday(PersonDateOfBirth)) = @DaysToNextBirthday
ORDER BY NextBirthday
END
GO
C#
Person.cs
// This is the model for one row in the database table. You may need to make some adjustments.
using System.ComponentModel.DataAnnotations;
namespace BlazorBirthdayReminders.Data
{
public class Person
{
[Required]
public Guid PersonID { get; set; }
[Required(ErrorMessage = "'First Name' is required.")]
[StringLength(50, ErrorMessage = "'First Name' has a maximum length of 50 characters.")]
public string PersonFirstName { get; set; } = String.Empty;
[Required(ErrorMessage = "'Last Name' is required.")]
[StringLength(50, ErrorMessage = "'Last Name' has a maximum length of 50 characters.")]
public string PersonLastName { get; set; } = String.Empty;
[Required(ErrorMessage = "Date of Birth is compulsory")]
public DateTime PersonDateOfBirth { get; set; }
[Required(ErrorMessage = "'Send Reminder To' is required.")]
[EmailAddress(ErrorMessage = "Invalid Email Address format.")]
[StringLength(100, ErrorMessage = "'Email' has a maximum length of 100 characters.")]
public string PersonSendReminderTo { get; set; } = String.Empty;
public string? PersonFullName { get; }
public DateTime NextBirthday { get; }
public int? DaysToNextBirthday { get; }
public int? AgeNextBirthday { get; }
}
}
PersonService.cs
// This is the service for the Person class.
using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;
namespace BlazorBirthdayReminders.Data
{
public class PersonService : IPersonService
{
// Database connection
private readonly IConfiguration _configuration;
public PersonService(IConfiguration configuration)
{
_configuration = configuration;
}
public string connectionId = "Default";
// Add (create) a Person table row (SQL Insert)
public async Task<int> PersonInsert(Person person)
{
int Success = 99;
using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
{
var parameters = new DynamicParameters();
parameters.Add("PersonFirstName", person.PersonFirstName, DbType.String);
parameters.Add("PersonLastName", person.PersonLastName, DbType.String);
parameters.Add("PersonDateOfBirth", person.PersonDateOfBirth, DbType.Date);
parameters.Add("PersonSendReminderTo", person.PersonSendReminderTo, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
// Stored procedure method
await conn.ExecuteAsync("spPerson_Insert", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
// Get a list of person rows (SQL Select)
public async Task<IEnumerable<Person>> PersonList()
{
IEnumerable<Person> people;
using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
{
people = await conn.QueryAsync<Person>("spPerson_List", commandType: CommandType.StoredProcedure);
}
return people;
}
// Get one person based on its PersonID (SQL Select)
public async Task<Person> PersonGetOne(Guid @PersonID)
{
Person person = new Person();
var parameters = new DynamicParameters();
parameters.Add("@PersonID", PersonID, DbType.Guid);
using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
{
person = await conn.QueryFirstOrDefaultAsync<Person>("spPerson_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return person;
}
public async Task<IEnumerable<Person>> PersonGetByDaysToNextBirthday(int DaysToNextBirthday)
{
var parameters = new DynamicParameters();
parameters.Add("@DaysToNextBirthday", DaysToNextBirthday, DbType.Int32);
IEnumerable<Person> people;
using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
{
people = await conn.QueryAsync<Person>("spPerson_GetByDaysToNextBirthday", parameters, commandType: CommandType.StoredProcedure);
}
return people;
}
// Update one Person row based on its PersonID (SQL Update)
public async Task<int> PersonUpdate(Person person)
{
int Success = 0;
using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
{
var parameters = new DynamicParameters();
parameters.Add("PersonID", person.PersonID, DbType.Guid);
parameters.Add("PersonFirstName", person.PersonFirstName, DbType.String);
parameters.Add("PersonLastName", person.PersonLastName, DbType.String);
parameters.Add("PersonDateOfBirth", person.PersonDateOfBirth, DbType.Date);
parameters.Add("PersonSendReminderTo", person.PersonSendReminderTo, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
await conn.ExecuteAsync("spPerson_Update", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
// Physically delete one Person row based on its PersonID (SQL Delete)
public async Task<bool> PersonDelete(Guid PersonID)
{
var parameters = new DynamicParameters();
parameters.Add("@PersonID", PersonID, DbType.Guid);
using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
{
await conn.ExecuteAsync("spPerson_Delete", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
}
}
IPersonService.cs
// This is the Person Interface
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorBirthdayReminders.Data
{
// Each item below provides an interface to a method in PersonServices.cs
public interface IPersonService
{
Task<int> PersonInsert(Person person);
Task<IEnumerable<Person>> PersonList();
Task<IEnumerable<Person>> PersonGetByDaysToNextBirthday(int DaysToNextBirthday);
Task<Person> PersonGetOne(Guid PersonID);
Task<int> PersonUpdate(Person person);
Task<bool> PersonDelete(Guid PersonID);
}
}
@page "/emailtest/"
@using MailKit.Net.Smtp;
@using MailKit.Security;
@using MimeKit;
@using MimeKit.Text;
@inject IEmailService EmailService
@inject IPersonService PersonService
<h3>Email Test Page</h3>
<hr />
<br/>
<div>
<p class="alert-success">
These are the settings for common email providers. Substitute in the code as necessary.
<br/>
</p>
<p>
//GMail<br />
smtp.Connect("smtp.gmail.com", 587, SecureSocketOptions.StartTls);
<br/>
</p>
<p>
//Outlook<br />
smtp.Connect("smtp-mail.outlook.com", 587, SecureSocketOptions.StartTls);
<br/>
</p>
<p>
//Office 365<br />
smtp.Connect("smtp.office365.com", 587, SecureSocketOptions.StartTls);
<br/>
</p>
<p>
//Generic<br />
smtp.Connect("smtp.ServerNameOrDomainName", 587, SecureSocketOptions.StartTls);<br />
or:<br />
smtp.Connect("mail.ServerNameOrDomainName", 587, SecureSocketOptions.StartTls);
<br/>
</p>
</div>
<br/>
<hr />
<br/>
<div class="button-container">
<button type="button" class="e-btn e-normal e-primary" @onclick="@SendEmail">Send Email</button>
</div>
<div class="col-lg-12 control-section toast-default-section">
<SfToast @ref="ToastObj" Timeout="4000">
<ToastPosition X="Right" Y="Top"></ToastPosition>
</SfToast>
</div>
<style>
@@font-face {
font-family: 'Toast_icons';
src: url(data:application/x-font-ttf;charset=utf-8;base64,AAEAAAAKAIAAAwAgT1MvMj0gSRkAAAEoAAAAVmNtYXDnM+eRAAABsAAAAEpnbHlmzVnmlwAAAhgAAAZAaGVhZBEYIl8AAADQAAAANmhoZWEHlgN3AAAArAAAACRobXR4LvgAAAAAAYAAAAAwbG9jYQnUCGIAAAH8AAAAGm1heHABHQBcAAABCAAAACBuYW1lfUUTYwAACFgAAAKpcG9zdAxfTDgAAAsEAAAAggABAAADUv9qAFoEAAAAAAAD6AABAAAAAAAAAAAAAAAAAAAADAABAAAAAQAACcU5MF8PPPUACwPoAAAAANcI7skAAAAA1wjuyQAAAAAD6APoAAAACAACAAAAAAAAAAEAAAAMAFAABwAAAAAAAgAAAAoACgAAAP8AAAAAAAAAAQPqAZAABQAAAnoCvAAAAIwCegK8AAAB4AAxAQIAAAIABQMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAUGZFZABA5wDnCgNS/2oAWgPoAJYAAAABAAAAAAAABAAAAAPoAAAD6AAAA+gAAAPoAAAD6AAAA+gAAAPoAAAD6AAAA+gAAAPoAAAD6AAAAAAAAgAAAAMAAAAUAAMAAQAAABQABAA2AAAABAAEAAEAAOcK//8AAOcA//8AAAABAAQAAAABAAIAAwAEAAUABgAHAAgACQAKAAsAAAAAAAAAQgB8AMIA4gEcAZQCBgJwAo4DAAMgAAAAAwAAAAADlAOUAAsAFwAjAAABFwcXNxc3JzcnBycFDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgEBTXh4L3h4L3h4L3h4AbwDt4qKtwMDt4qKt/0eBeuxsesFBeuxsesCbHh4L3h4L3h4L3h4p4q3AwO3ioq3AwO3irHrBQXrsbHrBQXrAAAAAwAAAAADlAOUAAUAEQAdAAABJwcXAScXDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgEBr2UylwEbMqADt4qKtwMDt4qKt/0eBeuxsesFBeuxsesBrGQylgEcMqKKtwMDt4qKtwMDt4qx6wUF67Gx6wUF6wAAAAAFAAAAAAOUA5cABQARAB0AIQAlAAABFzcnNSMFDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgElFzcnBRc3JwHKxiCnPwFOA6V8fKUDA6V8fKX9aATToJ/UBATUn5/UAh7ANsD9fja/NQGedzNj29F8pAMDpHx8pQMDpXyf1AQE1J+g0wQE0/GhQKGhQKFAAAQAAAAAA74DfgADAAcACgANAAAlMzUjNTM1IwEhCQEhAQHLUlJSUgFj/YwBOv42A5T+NuZUUqf+igIc/ZADFgAEAAAAAAOUA5QAAwAHABMAHwAAATM1IzUzNSMFDgEHLgEnPgE3HgEFHgEXPgE3LgEnDgEBylRUVFQBbgO3ioq3AwO3ioq3/R4F67Gx6wUF67Gx6wEk+lNT0Iq3AwO3ioq3AwO3irHrBQXrsbHrBQXrAAAAAAcAAAAAA+gDMQALABUAJQAuADcAQQBLAAABFhcVITUmJz4BMxYFFhcVITU+ATcWJQYHFSE1LgEjIgYHLgEjIgEWFAYiJjQ2MgUWFAYiJjQ2MiUGFBYXPgE0JiIFBhQWFz4BNCYiA1xEBP6sAxUeRiRX/qxEBP45BIlXV/7xZQsD6AvKUypvMzNvKlMCKxozTTMzTP6CGTNMNDRMAQItWUREWlqI/jstWkREWVmIAWMbFjc3IBgKDwQcGxY3NxY3BAQjJUt7e0tKFxgYFwEMGU01NU0zGhlNNTVNMxYthloCAlqGWy4thloCAlqGWwAAAAQAAAAAA5wCxwAIABQANABFAAABFBYyNjQmIgYXDgEHLgEnPgE3HgEfAQcOAQ8BNz4BNS4BJw4BBxQWHwEnLgEvATc+ATc2FiUOAQ8BFx4BNz4BPwEnJiciAb8fLR4eLR+wAkU0NEUBAUU0NEX8BgEemG0FBB8kAlZBQFcBKyUCCkeVTAYBH76RVMP+3bDPBwcKZclcu/AGCwrM2AoBxxYfHy0eHhc0RQEBRTQ1RQEBRSgEARpWGAECFUIoQVcCAldBLEYUAQEIQkAGASJsBwFCoRbFFAoJW0sBCo8LCgztAQAAAAIAAAAAA4ADbAA4AEEAAAEEJCcmDgEWFx4BHwEVFAYHDgEnJg4BFhcWNjc2Fx4BBx4BFzc+ASc2JicmJzUzPgE3PgEnJicjIiUUFjI2NCYiBgNM/tz+pwwMGxEDDAaMfAcSETKEQw8WBg8Og80hNSg4JwICEw0FDhECAjFJEBICPYhKDQgGChQCB/5dMUgxMUgxAuB/ZRcIAxgbCQdHEQGTGi8TOVgKAw8dFwMNuDUFHTGDCA0QAQECFQ8Mnz8LCasJKiUHGg0SATMkMDBJMDAAAAAAAgAAAAAC/QMkAAMADQAAAQchJxMeATMhMjY3EyEC2x3+bB0kBCQZAQQZJARH/ewDBuDg/fcZICAZAicAAwAAAAACzwPoACwAQwBPAAABERQfARYfAzMVHgE7ATI2NRE0JisBNTEWOwEyNjQmJyMiJi8BLgErAQ4BAxUzNTQ2NzMeARcVMzUuAScjIgcjESM1HgEXPgE3LgEnDgEBVQEBAwQCCAjXARENOg0REQ2zDROVExoaE2UQGAQfAxAKYg0RPR8RDZcNEQEeASIalxANAR8CTTo6TQEBTTo6TQJ8/nYEBQIGBAIFArYNERENARENEUoNGicZARMPfQoNARH98Hl5DREBARENeXkaIgEIAe3FOk0CAk06Ok0BAU0AAAAAAgAAAAAC5gMyAAkAEQAAJRQWMyEyNjURITcjFSE1IycjASApHgEaHin+WFBuAeR+JLD8HigoHgGfeT09HgAAAAAAEgDeAAEAAAAAAAAAAQAAAAEAAAAAAAEAEgABAAEAAAAAAAIABwATAAEAAAAAAAMAEgAaAAEAAAAAAAQAEgAsAAEAAAAAAAUACwA+AAEAAAAAAAYAEgBJAAEAAAAAAAoALABbAAEAAAAAAAsAEgCHAAMAAQQJAAAAAgCZAAMAAQQJAAEAJACbAAMAAQQJAAIADgC/AAMAAQQJAAMAJADNAAMAAQQJAAQAJADxAAMAAQQJAAUAFgEVAAMAAQQJAAYAJAErAAMAAQQJAAoAWAFPAAMAAQQJAAsAJAGnIEZpbmFsIFRvYXN0IE1ldHJvcFJlZ3VsYXJGaW5hbCBUb2FzdCBNZXRyb3BGaW5hbCBUb2FzdCBNZXRyb3BWZXJzaW9uIDEuMEZpbmFsIFRvYXN0IE1ldHJvcEZvbnQgZ2VuZXJhdGVkIHVzaW5nIFN5bmNmdXNpb24gTWV0cm8gU3R1ZGlvd3d3LnN5bmNmdXNpb24uY29tACAARgBpAG4AYQBsACAAVABvAGEAcwB0ACAATQBlAHQAcgBvAHAAUgBlAGcAdQBsAGEAcgBGAGkAbgBhAGwAIABUAG8AYQBzAHQAIABNAGUAdAByAG8AcABGAGkAbgBhAGwAIABUAG8AYQBzAHQAIABNAGUAdAByAG8AcABWAGUAcgBzAGkAbwBuACAAMQAuADAARgBpAG4AYQBsACAAVABvAGEAcwB0ACAATQBlAHQAcgBvAHAARgBvAG4AdAAgAGcAZQBuAGUAcgBhAHQAZQBkACAAdQBzAGkAbgBnACAAUwB5AG4AYwBmAHUAcwBpAG8AbgAgAE0AZQB0AHIAbwAgAFMAdAB1AGQAaQBvAHcAdwB3AC4AcwB5AG4AYwBmAHUAcwBpAG8AbgAuAGMAbwBtAAAAAAIAAAAAAAAACgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADAECAQMBBAEFAQYBBwEIAQkBCgELAQwBDQAFRXJyb3IHU3VjY2VzcwVBbGFybQdXYXJuaW5nBEluZm8HTWVldGluZwVCbGluawdTdHJldGNoA1NpcANTaXQFVHJhc2gAAAAA) format('truetype');
font-weight: normal;
font-style: normal;
}
#toast_types button {
margin: 5px;
min-width: 160px;
max-width: 160px;
}
.toast-icons {
font-family: 'Toast_icons' !important;
speak: none;
font-size: 55px;
font-style: normal;
font-weight: normal;
font-variant: normal;
text-transform: none;
line-height: 1;
-webkit-font-smoothing: antialiased;
-moz-osx-font-smoothing: grayscale;
}
#toast_type .e-toast-icon.e-icons {
height: auto;
font-size: 30px;
}
.toast-icons.e-success::before {
content: "\e701";
}
.toast-icons.e-error::before {
content: "\e700";
}
.toast-icons.e-info::before {
content: "\e704";
}
.toast-icons.e-warning::before {
content: "\e703";
}
#toast_types {
text-align: center;
}
</style>
@code {
public MimeMessage email = new MimeMessage();
private IEnumerable<Person>? people;
private List<Person>? birthdaypeople;
SfToast ToastObj;
bool SomeoneWithBirthdayFound = false;
private List<ToastModel> Toast = new List<ToastModel>
{
new ToastModel{ Title = "Warning!", Content="There was a problem with your network connection.", CssClass="e-toast-warning", Icon="e-warning toast-icons" },
new ToastModel{ Title = "Success!", Content="Your message has been sent successfully.", CssClass="e-toast-success", Icon="e-success toast-icons" },
new ToastModel{ Title = "Error!", Content="A problem has been occurred while submitting your email", CssClass="e-toast-danger", Icon="e-error toast-icons" },
new ToastModel{ Title = "Information!", Content="No one with an upcoming birthday was found.", CssClass="e-toast-info", Icon="e-info toast-icons" }
};
public async Task SendEmail()
{
string? SendTo;
string? EmailSubject;
string? EmailBody;
for(int daysNotice = 7; daysNotice < 22; daysNotice = daysNotice + 7)
{
people = await PersonService.PersonGetByDaysToNextBirthday(daysNotice);
birthdaypeople = people.ToList(); //Convert from IEnumerable to List
foreach(var person in birthdaypeople)
{
SomeoneWithBirthdayFound = true;
SendTo = person.PersonSendReminderTo;
EmailSubject = "It's " + person.PersonFullName + "'s Birthday!";
EmailBody = "<p>Hello</p>";
if(daysNotice == 21)
{
EmailBody = EmailBody + "<p>A bit of warning that it's ";
}
else if(daysNotice == 14)
{
EmailBody = EmailBody + "<p>Don't forget, it's ";
}
else
{
EmailBody = EmailBody + "<p>Better get cracking, it's only " + daysNotice + "days until ";
}
EmailBody = EmailBody + person.PersonFirstName + "'s birthday on " + person.NextBirthday.ToString("dd/MM/yyyy") + ".</p>";
if (person.AgeNextBirthday < 21)
{
EmailBody = EmailBody + "<p>" + person.PersonFirstName + " will be " + person.AgeNextBirthday + ".</p>";
}
EmailBody = EmailBody + "<p>Chris <br/>";
EmailBody = EmailBody + "Chris's automated Birthday Reminder Service<p>";
if (EmailService.Send(SendTo, EmailSubject, EmailBody))
{
//do something
await this.ToastObj.Show(Toast[1]);
}
else
{
//do something else
await this.ToastObj.Show(Toast[2]);
};
}
}
if(!SomeoneWithBirthdayFound)
{
await this.ToastObj.Show(Toast[3]);
}
}
}