SQL
Person_GetByUser
USE [Birthdays]
GO
/****** Object: StoredProcedure [dbo].[spPerson_GetAll] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------
CREATE PROCEDURE [dbo].[spPerson_GetByUser]
@PersonSendReminderTo nvarchar(100)
AS
BEGIN
--SQL for Select stored procedure.
SELECT PersonID,
PersonFirstName,
PersonLastName,
PersonDateOfBirth,
PersonSendReminderTo,
dbo.NextBirthday(PersonDateOfBirth) AS NextBirthday,
DATEDIFF(year, PersonDateOfBirth, dbo.NextBirthday(PersonDateOfBirth)) AS AgeNextBirthday
FROM Person
WHERE PersonSendReminderTo = @PersonSendReminderTo
ORDER BY NextBirthday ASC
END
C#
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 people based on logged in user email (SQL Select)
public async Task<IEnumerable<Person>> PersonGetByUser(string @PersonSendReminderTo)
{
IEnumerable<Person> people;
var parameters = new DynamicParameters();
parameters.Add("@PersonSendReminderTo", PersonSendReminderTo, DbType.String);
using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
{
people = await conn.QueryAsync<Person>("spPerson_GetByUser", parameters, 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>> PersonGetByUser(string @PersonSendReminderTo);
Task<IEnumerable<Person>> PersonGetByDaysToNextBirthday(int DaysToNextBirthday);
Task<Person> PersonGetOne(Guid PersonID);
Task<int> PersonUpdate(Person person);
Task<bool> PersonDelete(Guid PersonID);
}
}
Index.razor
@page "/"
<PageTitle>Birthday Reminders</PageTitle>
@using BlazorBirthdayReminders.Data
@inject IPersonService PersonService
@using Microsoft.AspNetCore.Components.Authorization
@using Microsoft.Identity.Client
@using System
@using System.Collections.Generic
@using System.Security.Claims
@inject AuthenticationStateProvider AuthenticationStateProvider
<div class="col-sm-12">
<h3>Birthday Reminders</h3>
<br />
<SfGrid DataSource="@people"
Toolbar="Toolbaritems"
AllowSorting="true">
<GridEvents RowSelected="RowSelectHandler" OnToolbarClick="ToolbarClickHandler" TValue="Person"></GridEvents>
<GridColumns>
<GridColumn Field="@nameof(Person.NextBirthday)"
HeaderText="Next Birthday"
Format="d"
Type="ColumnType.Date"
TextAlign="@TextAlign.Left"
Width="15">
</GridColumn>
<GridColumn Field="@nameof(Person.PersonFirstName)"
HeaderText="First Name"
TextAlign="@TextAlign.Left"
Width="20">
</GridColumn>
<GridColumn Field="@nameof(Person.PersonLastName)"
HeaderText="Last Name"
TextAlign="@TextAlign.Left"
Width="20">
</GridColumn>
<GridColumn Field="@nameof(Person.AgeNextBirthday)"
HeaderText="Age Next Birthday"
Type="ColumnType.Number"
TextAlign="@TextAlign.Right"
Width="15">
</GridColumn>
<GridColumn Field="@nameof(Person.PersonDateOfBirth)"
HeaderText="Date of Birth"
Format="d"
Type="ColumnType.Date"
TextAlign="@TextAlign.Center"
Width="20">
</GridColumn>
@*<GridColumn Field="@nameof(Person.PersonSendReminderTo)"
HeaderText="Send Reminder to:"
TextAlign="@TextAlign.Left"
Width="40">
</GridColumn>*@
</GridColumns>
</SfGrid>
</div>
<SfDialog @ref="DialogAddEditPerson" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> @HeaderText </Header>
</DialogTemplates>
<EditForm Model="@personaddedit" OnValidSubmit="@PersonSave">
<DataAnnotationsValidator/>
<div>
<SfTextBox Enabled="true" Placeholder="First Name"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="personaddedit.PersonFirstName">
</SfTextBox>
<ValidationMessage For="@(() => personaddedit.PersonFirstName)" />
<SfTextBox Enabled="true" Placeholder="Last Name"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="personaddedit.PersonLastName">
</SfTextBox>
<ValidationMessage For="@(() => personaddedit.PersonLastName)" />
<SfDatePicker TValue="DateTime"
Placeholder='Date of Birth'
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="personaddedit.PersonDateOfBirth">
</SfDatePicker>
<ValidationMessage For="@(() => personaddedit.PersonDateOfBirth)" />
<SfTextBox Enabled="false" Placeholder="Send Reminders to:"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="personaddedit.PersonSendReminderTo">
</SfTextBox>
<ValidationMessage For="@(() => personaddedit.PersonSendReminderTo)" />
</div>
<br /><br />
<div class="e-footer-content">
<div class="button-container">
<button type="submit" class="e-btn e-normal e-primary">Save</button>
<button type="button" class="e-btn e-normal" @onclick="@CloseDialog">Cancel</button>
</div>
</div>
</EditForm>
</SfDialog>
<SfDialog @ref="DialogDeletePerson" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Confirm Delete </Header>
<Content>
<SfTextBox Enabled="false" Placeholder="First Name"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="personaddedit.PersonFirstName"></SfTextBox>
<SfTextBox Enabled="false" Placeholder="Last Name"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="personaddedit.PersonLastName"></SfTextBox>
<br />
<br />
<span class="text-danger">Please confirm that you want to delete this record</span>
</Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="Delete" IsPrimary="true" OnClick="@ConfirmDeleteYes" />
<DialogButton Content="Cancel" IsPrimary="false" OnClick="@ConfirmDeleteNo" />
</DialogButtons>
</SfDialog>
<WarningPage @ref="Warning" WarningHeaderMessage="@WarningHeaderMessage" WarningContentMessage="@WarningContentMessage" />
@code {
// Create an empty list, named people, of empty Person objects.
IEnumerable<Person>? people;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
SfDialog DialogAddEditPerson;
Person personaddedit = new Person();
string HeaderText = "";
private string? UserEmail;
[CascadingParameter]
private Task<AuthenticationState>? authState { get; set; }
private ClaimsPrincipal? principal;
WarningPage Warning;
string WarningHeaderMessage = "";
string WarningContentMessage = "";
public Guid SelectedPersonId { get; set; } = Guid.Empty;
SfDialog DialogDeletePerson;
protected override async Task OnInitializedAsync()
{
if (authState != null)
{
principal = (await authState).User;
}
foreach (Claim claim in principal.Claims)
{
//claimtype = claimtype + "Claim Type: " + claim.Type + "; CLAIM VALUE: " + claim.Value + "</br>";
if(claim.Type == "emails")
{
UserEmail = claim.Value;
}
}
//Populate the list of Person objects from the Person table.
//people = await PersonService.PersonList();
people = await PersonService.PersonGetByUser(UserEmail);
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new person", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected person", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected person", PrefixIcon = "e-delete" });
}
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
//Code for adding goes here
personaddedit = new Person(); // Ensures a blank form when adding
HeaderText = "Add a Contact";
await this.DialogAddEditPerson.Show();
personaddedit.PersonDateOfBirth = new DateTime(2000, 12, 31);
personaddedit.PersonSendReminderTo = UserEmail;
}
if (args.Item.Text == "Edit")
{
//Code for editing
//Check that a Person has been selected
if (SelectedPersonId == Guid.Empty)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Contact from the grid.";
Warning.OpenDialog();
}
else
{
//populate personaddedit (temporary data set used for the editing process)
HeaderText = "Edit Contact";
personaddedit = await PersonService.PersonGetOne(SelectedPersonId);
await this.DialogAddEditPerson.Show();
}
}
if (args.Item.Text == "Delete")
{
//Code for deleting
if (SelectedPersonId == Guid.Empty)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Contact from the grid.";
Warning.OpenDialog();
}
else
{
//populate personaddedit (temporary data set used for the editing process)
HeaderText = "Delete Contact";
personaddedit = await PersonService.PersonGetOne(SelectedPersonId);
await this.DialogDeletePerson.Show();
}
}
}
protected async Task PersonSave()
{
//In all cases check the reasonableness of the date of birth
//Make sure it's not in the future
if(personaddedit.PersonDateOfBirth> DateTime.Now)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = $"It looks like the Date of Birth is wrong. It's in the future!";
Warning.OpenDialog();
return;
}
//Check whether they are more than, say, 105 years old...
DateTime zeroTime = new DateTime(1, 1, 1);
TimeSpan span = DateTime.Today - personaddedit.PersonDateOfBirth;
// Because we start at year 1 for the Gregorian calendar, we must subtract a year here.
// We need to add zeroTime because span is just a number of days (i.e. not date format)
int years = (zeroTime + span).Year - 1;
//double ageInDays = span.TotalDays;
//int ageInYears = Convert.ToInt32(ageInDays/365.25);
if(years > 105)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = $"It looks like the Date of Birth is wrong. They would be { years } old!";
Warning.OpenDialog();
return;
}
if (personaddedit.PersonID == Guid.Empty)
{
int Success = await PersonService.PersonInsert(personaddedit);
if (Success != 0)
{
//Person already exists - warn the user
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Person already exists; it cannot be added again.";
Warning.OpenDialog();
}
else
{
//Refresh datagrid
//people = await PersonService.PersonList();
people = await PersonService.PersonGetByUser(UserEmail);
StateHasChanged();
// Ensures a blank form for adding a new record
personaddedit = new Person();
//Adds defaults for a new record
personaddedit.PersonDateOfBirth = new DateTime(2000, 12, 31);
personaddedit.PersonSendReminderTo = UserEmail;
}
}
else
{
// Item is being edited
int Success = await PersonService.PersonUpdate(personaddedit);
if (Success != 0)
{
//Person already exists
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Person already exists; it cannot be added again.";
Warning.OpenDialog();
// Data is left in the dialog so the user can see the problem.
}
else
{
//Refresh datagrid
//people = await PersonService.PersonList();
people = await PersonService.PersonGetByUser(UserEmail);
StateHasChanged();
await CloseDialog();
personaddedit = new Person();
SelectedPersonId = Guid.Empty;
}
}
}
private async Task CloseDialog()
{
await this.DialogAddEditPerson.Hide();
//Refresh datagrid
//people = await PersonService.PersonList();
people = await PersonService.PersonGetByUser(UserEmail);
StateHasChanged();
}
public void RowSelectHandler(RowSelectEventArgs<Person> args)
{
//{args.Data} returns the current selected records.
SelectedPersonId = args.Data.PersonID;
}
public async void ConfirmDeleteNo()
{
await DialogDeletePerson.Hide();
SelectedPersonId = Guid.Empty;
}
public async void ConfirmDeleteYes()
{
bool Success = await PersonService.PersonDelete(SelectedPersonId);
if (Success == false)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Unknown error has occurred - the record has not been deleted!";
Warning.OpenDialog();
}
else
{
await this.DialogDeletePerson.Hide();
//people = await PersonService.PersonList();
people = await PersonService.PersonGetByUser(UserEmail);
this.StateHasChanged();
//personaddedit = new Person();
SelectedPersonId = Guid.Empty;
}
}
}
Program.cs
using BlazorBirthdayReminders.Data;
using Microsoft.AspNetCore.Authentication;
using Microsoft.AspNetCore.Authentication.OpenIdConnect;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Components.Web;
using Microsoft.AspNetCore.Mvc.Authorization;
using Microsoft.Identity.Web;
using Microsoft.Identity.Web.UI;
using Syncfusion.Blazor;
using Hangfire;
using Hangfire.SqlServer;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddAuthentication(OpenIdConnectDefaults.AuthenticationScheme)
.AddMicrosoftIdentityWebApp(builder.Configuration.GetSection("AzureAd"));
builder.Services.AddControllersWithViews()
.AddMicrosoftIdentityUI();
builder.Services.AddAuthorization(options =>
{
// By default, all incoming requests will be authorized according to the default policy
options.FallbackPolicy = options.DefaultPolicy;
});
builder.Services.AddRazorPages();
builder.Services.AddServerSideBlazor()
.AddMicrosoftIdentityConsentHandler();
builder.Services.AddScoped<IPersonService, PersonService>();
//builder.Services.AddScoped<IEmailService, EmailService>();
builder.Services.AddTransient<IEmailService, EmailService>(); //Scoped didn't seem to work. Singleton did work.
builder.Services.AddSingleton<IReminderService, ReminderService>();
builder.Services.AddSyncfusionBlazor(options => { options.IgnoreScriptIsolation = true; });
builder.Services.AddHangfire(x => x
.SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
.UseSimpleAssemblyNameTypeSerializer()
.UseRecommendedSerializerSettings()
.UseSqlServerStorage(builder.Configuration.GetConnectionString("Default"), new SqlServerStorageOptions
{
CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
QueuePollInterval = TimeSpan.Zero,
UseRecommendedIsolationLevel = true,
DisableGlobalLocks = true
}));
builder.Services.AddHangfireServer();
builder.Services.Configure<OpenIdConnectOptions>(OpenIdConnectDefaults.AuthenticationScheme, options =>
{
options.Events.OnSignedOutCallbackRedirect = context =>
{
context.HttpContext.Response.Redirect(context.Options.SignedOutRedirectUri);
context.HandleResponse();
return global::System.Threading.Tasks.Task.CompletedTask;
};
});
var app = builder.Build();
///Register Syncfusion license
var SyncfusionLicenceKey = builder.Configuration["SyncfusionLicenceKey"];
Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense(SyncfusionLicenceKey);
// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
app.UseExceptionHandler("/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();
app.MapBlazorHub();
app.MapFallbackToPage("/_Host");
app.UseHangfireDashboard("/dashboard");
//IEmailService EmailService = app.Services.GetRequiredService<IEmailService>(); //Needed for items below
//BackgroundJob.Enqueue(() => EmailService.Send("christopherbell@blazorcode.uk",
// "SUBJECT - The program has started",
// "This is the body of the email. This is the email to say the program has started"));
//BackgroundJob.Schedule(() => EmailService.Send("christopherbell@blazorcode.uk",
// "This is sent after a minute"
// , "Welcome - This was sent one minute after starting"),
// TimeSpan.FromMinutes(1));
//RecurringJob.AddOrUpdate(
// "Run every 5 minutes", () => EmailService.Send("christopherbell@blazorcode.uk",
// "Recurring Email every 5 minutes",
// "Another 5 minutes ticks by...."),
// "*/5 * * * *");
//RecurringJob.AddOrUpdate(
// "Run every day at 10:57", () => EmailService.Send("christopherbell@blazorcode.uk",
// "Recurring Email every day at 10:57",
// "This is the body of the email"),
// "57 10 * * *");
IReminderService ReminderService = app.Services.GetRequiredService<IReminderService>(); //Needed for items below
RecurringJob.AddOrUpdate(
"Daily Birthday Reminder", () => ReminderService.SendReminders(),
"35 16 * * *", TimeZoneInfo.Local);
app.Run();