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