Countries - Duplicates Code
The full code to this stage is shown here.
Index.razor
@page "/"
@using BlazorCountries.Data
@inject ICountriesService CountriesService
@inject NavigationManager NavigationManager
<h3>Countries List</h3>
<SfGrid @ref="FirstGrid"
DataSource="@countries"
AllowSorting="true"
AllowResizing="true"
AllowFiltering="true"
AllowPaging="true"
AllowReordering="true"
AllowExcelExport="true"
ContextMenuItems="@(new List<object>() {"AutoFit", "AutoFitAll", "SortAscending", "SortDescending","Copy", "ExcelExport", "CsvExport", "FirstPage", "PrevPage","LastPage", "NextPage"})"
Toolbar="Toolbaritems">
<GridPageSettings PageSize="5"></GridPageSettings>
<GridEvents OnToolbarClick="ToolbarClickHandler" RowSelected="RowSelectHandler" TValue="Countries"></GridEvents>
<GridColumns>
<GridColumn Field="@nameof(Countries.CountryId)"
HeaderText="Country Id"
TextAlign="@TextAlign.Left"
Width="20">
</GridColumn>
<GridColumn Field="@nameof(Countries.CountryName)"
HeaderText="Country Name"
TextAlign="@TextAlign.Left"
Width="90">
</GridColumn>
</GridColumns>
</SfGrid>
<SfDialog @ref="DialogDelete" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Confirm Delete </Header>
<Content> Please confirm that you want to delete this record </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="Delete" IsPrimary="true" OnClick="@ConfirmDeleteYes" />
<DialogButton Content="Cancel" IsPrimary="false" OnClick="@ConfirmDeleteNo" />
</DialogButtons>
</SfDialog>
<SfDialog @ref="DialogNoRecordSelected" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Warning! </Header>
<Content> You must select a country </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialogNoRecordSelected" />
</DialogButtons>
</SfDialog>
@code {
private SfGrid<Countries> FirstGrid;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
private int? CountryID;
SfDialog DialogDelete;
SfDialog DialogNoRecordSelected;
// Create an empty list, named countries, of empty Counties objects.
IEnumerable<Countries> countries;
protected override async Task OnInitializedAsync()
{
//Populate the list of countries objects from the Countries table.
countries = await CountriesService.CountriesGetAll();
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new country", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected country", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected country", PrefixIcon = "e-delete" });
}
public async Task ExcelExport()
{
await this.FirstGrid.ExcelExport();
}
public void ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
CountryID = 0;
NavigationManager.NavigateTo($"/countriesaddedit/{CountryID}");
}
if (args.Item.Text == "Edit")
{
//Check that a record has been selected
if (CountryID > 0)
{
NavigationManager.NavigateTo($"/countriesaddedit/{CountryID}");
}
else
{
//No record has been selected
DialogNoRecordSelected.Show();
}
}
if (args.Item.Text == "Delete")
{
//Check that a record has been selected
if (CountryID > 0)
{
DialogDelete.Show();
}
else
{
//No record has been selected
DialogNoRecordSelected.Show();
}
}
}
public void RowSelectHandler(RowSelectEventArgs<Countries> args)
{
//{args.Data} returns the current selected records.
CountryID = args.Data.CountryId;
}
public async void ConfirmDeleteNo()
{
await DialogDelete.Hide();
}
public async void ConfirmDeleteYes()
{
await CountriesService.CountriesDelete(CountryID.GetValueOrDefault()); //This deletes the record
await DialogDelete.Hide();
// Both following lines required to refresh the grid
countries = await CountriesService.CountriesGetAll();
CountryID = 0; //Reset CountryID
StateHasChanged();
}
private async Task CloseDialogNoRecordSelected()
{
await this.DialogNoRecordSelected.Hide();
}
}CountiesAddEdit.razor
@using BlazorCountries.Data
@page "/countriesaddedit/{CountryId:int}"
@inject ICountriesService CountriesService
@inject NavigationManager NavigationManager
<h1>@pagetitle</h1>
<SfDialog IsModal="true" Width="500px" ShowCloseIcon="false" Visible="true">
<h5>@pagetitle</h5>
<br />
<EditForm Model="@countries" OnValidSubmit="@CountriesSave">
<div>
<SfTextBox Enabled="true" Placeholder="Country"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="countries.CountryName"></SfTextBox>
</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="@Cancel">Cancel</button>
</div>
</div>
</EditForm>
</SfDialog>
<SfDialog @ref="DialogWarning" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Warning! </Header>
<Content> This country already exists! It won't be added again. </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialog" />
</DialogButtons>
</SfDialog>
@code {
// Create a new, empty Countries object
Countries countries = new Countries();
[Parameter]
public int CountryId { get; set; }
SfDialog DialogWarning;
public string pagetitle = "Add a Country";
//Executes on page open, sets headings and gets data in the case of edit
protected override async Task OnInitializedAsync()
{
if (CountryId == 0)
{
pagetitle = "Add a Country";
}
else
{
countries = await CountriesService.CountriesGetOne(CountryId);
pagetitle = "Edit a Country";
}
}
// Executes OnValidSubmit of EditForm above.
protected async Task CountriesSave()
{
if (CountryId == 0)
{
int Success = await CountriesService.CountriesInsertWithDuplicatesCheck(countries.CountryName);
if (Success != 0)
{
//Country Name already exists
await this.OpenDialog();
}
else
{
NavigationManager.NavigateTo("/");
}
}
else
{
int Success = await CountriesService.CountriesUpdateWithDuplicatesCheck(countries);
if (Success != 0)
{
//Country Name already exists
await this.OpenDialog();
}
else
{
NavigationManager.NavigateTo("/");
}
}
}
//Executes if user clicks the Cancel button.
void Cancel()
{
NavigationManager.NavigateTo("/");
}
private async Task OpenDialog()
{
await this.DialogWarning.Show();
}
private async Task CloseDialog()
{
await this.DialogWarning.Hide();
NavigationManager.NavigateTo("/");
}
}CountriesService.cs
// This is the service for the Countries class.
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
public class CountriesService : ICountriesService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public CountriesService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
// Add (create) a Countries table row (SQL Insert)
public async Task<int> CountriesInsertWithDuplicatesCheck(string CountryName)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("@CountryName", CountryName, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_InsertWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
// Get a list of countries rows (SQL Select)
public async Task<IEnumerable<Countries>> CountriesGetAll()
{
IEnumerable<Countries> countries;
using (var conn = new SqlConnection(_configuration.Value))
{
countries = await conn.QueryAsync<Countries>("spCountries_GetAll", commandType: CommandType.StoredProcedure);
}
return countries;
}
// Get one country based on its CountriesID (SQL Select)
public async Task<Countries> CountriesGetOne(int @CountryId)
{
Countries countries = new Countries();
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
countries = await conn.QueryFirstOrDefaultAsync<Countries>("spCountries_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return countries;
}
// Update one Countries row based on its CountriesID (SQL Update)
public async Task<int> CountriesUpdateWithDuplicatesCheck(Countries countries)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("CountryId", countries.CountryId, DbType.Int32);
parameters.Add("CountryName", countries.CountryName, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_UpdateWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
// Physically delete one Countries row based on its CountriesID (SQL Delete)
public async Task<bool> CountriesDelete(int CountryId)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_Delete", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
}
}ICountriesService.cs
// This is the Countries Interface
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
// Each item below provides an interface to a method in CountriesServices.cs
public interface ICountriesService
{
Task<int> CountriesInsertWithDuplicatesCheck(string CountryName);
Task<IEnumerable<Countries>> CountriesGetAll();
Task<Countries> CountriesGetOne(int CountryId);
Task<int> CountriesUpdateWithDuplicatesCheck(Countries countries);
Task<bool> CountriesDelete(int CountryId);
}
}SQL - spCountries_InsertWithDuplicateChecking
USE [CountriesDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCountries_InsertWithDuplicateChecking]
(
@CountryName VARCHAR(50)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Countries
WHERE CountryName = @CountryName
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
INSERT INTO Countries
(
CountryName
)
VALUES
(
@CountryName
)
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
SQL - spCountries_UpdateWithDuplicateChecking
USE [CountriesDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spCountries_UpdateWithDuplicateChecking]
(
@CountryId int,
@CountryName varchar(50)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Countries
WHERE CountryName = @CountryName and CountryId <> @CountryId
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
UPDATE Countries SET CountryName = @CountryName WHERE CountryId = @CountryId
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO