Index.razor
Simple Data Grid
To test that we can connect and read from the SQLite database we will construct a simple razor page and display some columns from the SQLite Person table.
Replace the code in Index.razor with the following:
@page "/"
<PageTitle>Birthday Reminders</PageTitle>
@inject IPersonService PersonService
<div class="col-sm-12">
<h3>Birthday Reminders</h3>
<br />
<SfGrid DataSource="@people">
<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>
@code
{
IEnumerable<Person>? people;
protected override async Task OnInitializedAsync()
{
people = await PersonService.PersonList();
}
}Save and run the application. With a bit of luck the data manually entered in the SQLite database should be displayed.
The thing that amazed me most at this point was that it worked! I expected that the fact that we set the 'datatype' for PersonDateOfBirth in the SQLite database to be 'Text' and and as DateTime in the Person model to cause all sorts of problems. It was only be accident that PersonDateOfBirth was declared as DateTime in Person.cs, I had intended to change it to string!! Sometimes you are lucky!
Modify Index
Having established that we can retrieve data from the SQLite database I'm going to take the plunge and replace the whole of the code for Index.razor with code copied from the SQL Server version of Birthday Reminders. For convenience this is the un-modified code from the SQL Server version:
@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;
}
}
}Modify the code as shown below:
- Replace '@using BlazorBirthdayReminders.Data' with '@using SQLiteBirthdayReminders.Data
We will start by reverting back to the grid that we know works, so
- Delete the 'Person.NextBirthday' GridColumn
- Delete the 'Person.AgeNextBirthday' GridColumn
- In the OnInitialzedAsync method change the people declaration to:
people = await PersonService.PersonList();With the exception of a missing 'Warning' component and the fact the variable 'SelectedPersonID' is declared as a GUID, the rest of the problems are all associated with missing methods in PersonService (and IPersonService). We will tackle these one by one.
Add the Warning Component
In the Shared folder create a new Razor file an call it WarningPage.razor.
Copy and paste the following code into the new file, replacing the existing code:
@using Syncfusion.Blazor.Popups;
<SfDialog @ref="DialogWarning" @bind-Visible="@IsVisible" IsModal="true" Width="300px" ShowCloseIcon="true">
<DialogTemplates>
<Header> @WarningHeaderMessage </Header>
<Content>@WarningContentMessage</Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialog" />
</DialogButtons>
</SfDialog>
@code {
SfDialog? DialogWarning;
public bool IsVisible { get; set; } = false;
[Parameter] public string? WarningHeaderMessage { get; set; }
[Parameter] public string? WarningContentMessage { get; set; }
public void OpenDialog()
{
this.IsVisible = true;
this.StateHasChanged();
}
public void CloseDialog()
{
this.IsVisible = false;
this.StateHasChanged();
}
}Change SelectedPersonID
Change the declaration of SelectedPersonID to:
public int SelectedPersonId { get; set; } = 0;There are a number places where variables are either checked for Guid.Empty, or set to Guid.Empty. All these can be corrected by replacing 'Guid.Empty' with '0'.
Comment out missing methods
To allow the project to compile, comment out the lines that call missing methods. In some cases a variable is declared and set to the return value of the method; in these cases insert a line above the method and declare the variable as an int or bool and set to '99' or 'false'
For reference, the code for Index.razor should now be:
@page "/"
<PageTitle>Birthday Reminders</PageTitle>
@using SQLiteBirthdayReminders.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.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 int SelectedPersonId { get; set; } = 0;
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 == 0)
{
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 == 0)
{
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 == 0)
{
int Success = 99;
//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 = 99;
//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 = 0;
}
}
}
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 = 0;
}
public async void ConfirmDeleteYes()
{
bool Success = false;
//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 = 0;
}
}
}Add New Contact
I anticipate problems with replacing some of the more complex stored procedures, such as the ones that calculate NextBirthday, AgeNextBirthday and DaysToNextBirthday, and as these are used by the full grid and by the email service, I decided to tackle 'add', 'update' and 'delete' functions first. These are not without problems, such as duplicate checking that I previously included in stored procedures, but will now have to tackle by different techniques. I start with adding new contacts.
Before dealing with possible duplicates we will get the basic insert working. Add the following method to PersonService. Note that '@PersonDateOfBirth' is enclosed with by date(...). This will ensure that the database is updated with a Date rather than DateTime.
// Add (create) a Person table row (SQL Insert)
public async Task<bool> PersonInsert(Person person)
{
sqlCommand = "Insert into Person (PersonFirstName, PersonLastName, PersonDateOfBirth, PersonSendReminderTo) ";
sqlCommand += "values(@PersonFirstName, @PersonLastName, date(@PersonDateOfBirth), @PersonSendReminderTo)";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, person);
}
return true;
}An alternative version could be as shown below. In this case the parameters are declared and the 'ExecureAsync has 'parameters' passed to it in place of the person 'object'. I must admit to not knowing which is best practice.
// Add (create) a Person table row (SQL Insert)
public async Task<bool> PersonInsert(Person person)
{
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);
sqlCommand = "Insert into Person (PersonFirstName, PersonLastName, PersonDateOfBirth, PersonSendReminderTo) ";
sqlCommand += "values(@PersonFirstName, @PersonLastName, date(@PersonDateOfBirth), @PersonSendReminderTo)";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return true;
}And insert this line into IPersonService.
Task<bool> PersonInsert(Person person);The PersonInsert method is a boolean that will return 'true' if the insert succeeds and the sqlCommand is the same insert command we previously used in the stored procedure. Change the code for PersonInsert in the PersonSave method to the following and un-comment the Refresh Datagrid code to use PersonList.
bool Success = await PersonService.PersonInsert(personaddedit);
if (Success != true)Save and test. We should now be able to add new contacts, but there is no duplicate check.
Duplicate Check
There are (at least) two ways we can approach this. The first is, in SQLite, to put a unique key on a combinations of the fields we want to check for duplicates, in this case, FirstName, LastName, DateOfBirth and SendReminderTo. If we did this and an insert was attempted where a record already existed that matched the attempted insert, the insert would fail.
However, in the original Birthday Reminders project I tested for duplicates in the Insert and Update stored procedures. To follow that pattern for this project we will move the duplicate check from the stored procedure to C# code. We can do this by running a select query, passing to it details of the proposed new/updated contact, and counting the number of records. If inserting we need to check that the count is zero, and in the case of update we do the same, but exclude the ID of the record being updated. If the count returns more than 0, we know a duplicate would be created, so warn the user and stop the insert/update.
In the code for Index.razor add the following after "if (personaddedit.PersonID == 0)" in the 'PersonSave' method.
/Check for duplicates
int duplicates = await PersonService.PersonInsertDuplicateCheck(personaddedit);
if(duplicates > 0)
{
//Person already exists - warn the user
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Person already exists; they cannot be added again.";
Warning.OpenDialog();
return;
}The code should look like this:
I also took the opportunity to amend the error message if the insert failed for any other reason.
The above uses a new Service called 'PersonInsertDuplicatesCheck', which is where the count for duplicates is carried out. The 'personaddedit' object is passed to this service.
To add the service, open PersonService and add the following:
public async Task<int> PersonInsertDuplicateCheck(Person person)
{
int Count = 0;
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);
sqlCommand = "SELECT COUNT(*) ";
sqlCommand += "FROM Person";
sqlCommand += " WHERE PersonFirstName = @PersonFirstName";
sqlCommand += " AND PersonLastName = @PersonLastName";
sqlCommand += " AND PersonDateOfBirth = date(@PersonDateOfBirth)";
sqlCommand += " AND PersonSendReminderTo = @PersonSendReminderTo";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
Count = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
}
return Count; ;
}The SQL command is a straightforward Count of records in Person where the criteria match. The criteria parameters are passed using the 'parameters' option. The things to note in this are that the parameters.add for DateOfBirth is dbType.Date and that in the sqlCommand '@PersonDateOfBirth' is enclosed by date(...) - this converts the C# date format into the text format that we defined for PersonDateOfBirth in SQLite.
Now add to IPersonService
Task<int> PersonInsertDuplicateCheck(Person person);Update Contact
Following the pattern used for adding a new contact, we will first check for duplicates. Unfortunately we can't use exactly the same query to check for duplicates because the record we are updating will be included in the 'Count'. Add the following to PersonService
public async Task<int> PersonUpdateDuplicateCheck(Person person)
{
int Count;
var parameters = new DynamicParameters();
parameters.Add("@PersonID", person.PersonID, DbType.Int32);
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);
sqlCommand = "SELECT COUNT(*) ";
sqlCommand += "FROM Person";
sqlCommand += " WHERE PersonFirstName = @PersonFirstName";
sqlCommand += " AND PersonLastName = @PersonLastName";
sqlCommand += " AND PersonDateOfBirth = date(@PersonDateOfBirth)";
sqlCommand += " AND PersonSendReminderTo = @PersonSendReminderTo";
sqlCommand += " AND PersonID != @PersonID";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
Count = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
}
return Count;
}
And this to IPersonService
Task<int> PersonUpdateDuplicateCheck(Person person);Back in Index.razor, add the following to the top of the edit section
//Check for duplicates
int duplicates = await PersonService.PersonUpdateDuplicateCheck(personaddedit);
if(duplicates > 0)
{
//Person already exists - warn the user
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Person already exists; they cannot be added again.";
Warning.OpenDialog();
return;
}We now need to add a method in PersonService to update a record. Add the following code to create the PersonUpdate method:
public async Task<bool> PersonUpdate(Person person)
{
var parameters = new DynamicParameters();
parameters.Add("PersonID", person.PersonID, DbType.Int32);
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);
sqlCommand = "Update Person ";
sqlCommand += "SET PersonFirstName = @PersonFirstName, ";
sqlCommand += "PersonLastName = @PersonLastName, ";
sqlCommand += "PersonDateOfBirth = date(@PersonDateOfBirth), ";
sqlCommand += "PersonSendReminderTo = @PersonSendReminderTo ";
sqlCommand += "WHERE PersonID = @PersonID";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return true;
}
And this in IPersonService
Task<bool> PersonUpdate(Person person);But we also need to retrieve the record for the person selected by the user. To do this add the following to PersonService:
public async Task<Person> PersonGetOne(int PersonID)
{
Person person = new Person();
var parameters = new DynamicParameters();
parameters.Add("@PersonID", PersonID, DbType.Int32);
sqlCommand = "SELECT PersonID, PersonFirstName, ";
sqlCommand += "PersonLastName, PersonDateOfBirth, ";
sqlCommand += "PersonSendReminderTo ";
sqlCommand += "FROM Person WHERE PersonID= @PersonID";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
person = await conn.QueryFirstOrDefaultAsync<Person>(sqlCommand, parameters);
}
return person;
}And another line in IPersonService
Task<Person> PersonGetOne(int PersonID);Finally, un-comment the line that gets the selected record, i.e.
personaddedit = await PersonService.PersonGetOne(SelectedPersonId);With those changes in place we should be able to select, check for potential duplicates and update contacts.
Delete a Contact
Add the following method to PersonService:
public async Task<bool> PersonDelete(int PersonID)
{
var parameters = new DynamicParameters();
parameters.Add("@PersonID", PersonID, DbType.Int32);
sqlCommand = "DELETE FROM Person ";
sqlCommand += "WHERE PersonID= @PersonID";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return true;
}And the following to IPersonService:
Task<bool> PersonDelete(int PersonID);In Index.razor, in the ToolbarClickHandler method un-comment the line in the 'Delete' section that gets the selected record, i.e.
personaddedit = await PersonService.PersonGetOne(SelectedPersonId);In the ConfirmDeleteYes method, un-comment the call to the PersonDelete service and the line "people = await PersonService.PersonList();"
Save and test; it should now be possible to delete records after confirming that you do want to delete the selected record. And it should not be deleted if you decline!
Restrict Contacts by User
We need to limit the PersonList to those records where the person to send the reminder to is the person logged in. To make these changes:
Create a new method in PersonService as follows. This has the user EmailAddress passed in as a parameter and the select statement then applies a 'where' clause using this EmailAddress.
public async Task<IEnumerable<Person>> PersonListGetByUser(string PersonSendReminderTo)
{
IEnumerable<Person> people;
var parameters = new DynamicParameters();
parameters.Add("@PersonSendReminderTo", PersonSendReminderTo, DbType.String);
sqlCommand = "SELECT * From Person ";
sqlCommand += "WHERE PersonSendReminderTo = @PersonSendReminderTo";
using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
{
people = await conn.QueryAsync<Person>(sqlCommand, parameters);
}
return people;
}Add the following to IPersonService:
Task<IEnumerable<Person>> PersonListGetByUser(string @PersonSendReminderTo);In Index we now need to comment out all instances of:
people = await PersonService.PersonList()
I have changed the old name of the method from 'PersonGetByUser' to 'PersonListGetByUser', so uncomment and amend all instances of the old to this (I counted 5 places this needed changing).
people = await PersonService.PersonListGetByUser(UserEmail);