SQL
Person_Update Stored Procedure
USE [Birthdays]
GO
/****** Object: StoredProcedure [dbo].[spPerson_Update] ******/
DROP PROCEDURE [dbo].[spPerson_Update]
GO
/****** Object: StoredProcedure [dbo].[spPerson_Update] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spPerson_Update]
(
@PersonID uniqueidentifier,
@PersonFirstName nvarchar(50),
@PersonLastName nvarchar(50),
@PersonDateOfBirth date,
@PersonSendReminderTo nvarchar(100)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Person
WHERE PersonFirstName = @PersonFirstName
and PersonLastName = @PersonLastName
and PersonDateOfBirth = @PersonDateOfBirth
and PersonSendReminderTo = @PersonSendReminderTo
and PersonID <> @PersonID
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
UPDATE Person SET PersonFirstName = @PersonFirstName, PersonLastName = @PersonLastName, PersonDateOfBirth = @PersonDateOfBirth, PersonSendReminderTo = @PersonSendReminderTo WHERE PersonID = @PersonID
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
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 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;
}
// 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<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">
<GridEvents RowSelected="RowSelectHandler" OnToolbarClick="ToolbarClickHandler" TValue="Person"></GridEvents>
<GridColumns>
<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.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();
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();
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();
StateHasChanged();
await CloseDialog();
personaddedit = new Person();
SelectedPersonId = Guid.Empty;
}
}
}
private async Task CloseDialog()
{
await this.DialogAddEditPerson.Hide();
//Refresh datagrid
people = await PersonService.PersonList();
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();
this.StateHasChanged();
//personaddedit = new Person();
SelectedPersonId = Guid.Empty;
}
}
}