Countries & Cities - Part 4 - Code

Again, there have been quite a few changes, but the pattern of what is required should be becoming established.

SQL for spCities_UpdateWithDuplicateChecking

USE [CountriesDb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spCities_UpdateWithDuplicateChecking]
-- Parameters for Update stored procedure.
@CityId int,
@CityName varchar(50),
@CountryId int,
@CityPopulation int
AS
DECLARE @ResultValue int  
BEGIN TRAN  
IF EXISTS  
    (  
          SELECT * FROM Cities  
          WHERE (CityName = @CityName and CountryId = @CountryId) AND CityId <> @CityId
        )  
     BEGIN  
         SET  @ResultValue = 99  
     END  
ELSE 
	BEGIN
			UPDATE Cities SET CityName = @CityName, CountryId = @CountryId, CityPopulation = @CityPopulation WHERE CityId = @CityId
			set @ResultValue = @@ERROR
	END

IF @ResultValue <> 0  
     BEGIN  
            ROLLBACK TRAN  
      END  
ELSE  
      BEGIN  
            COMMIT TRAN  
      END  
RETURN @ResultValue  

GO

CitiesService.cs

// This is the service for the Cities 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 CitiesService : ICitiesService
    {
        // Database connection
        private readonly SqlConnectionConfiguration _configuration;
        public CitiesService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }
        // Add (create) a Cities table row (SQL Insert)        
        public async Task<bool> CitiesInsert(Cities cities)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("CityName", cities.CityName, DbType.String);
                parameters.Add("CountryId", cities.CountryId, DbType.Int32);
                parameters.Add("CityPopulation", cities.CityPopulation, DbType.Int32);

                // Stored procedure method
                await conn.ExecuteAsync("spCities_Insert", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }

        // Get a list of cities rows (SQL Select)
        public async Task<IEnumerable<Cities>> CitiesGetAll()
        {
            IEnumerable<Cities> citiess;
            using (var conn = new SqlConnection(_configuration.Value))
            {
                citiess = await conn.QueryAsync<Cities>("spCities_GetAll", commandType: CommandType.StoredProcedure);
            }
            return citiess;
        }

        // Get one cities based on its CitiesID (SQL Select)
        public async Task<Cities> CitiesGetOne(int @CityId)
        {
            Cities cities = new Cities();
            var parameters = new DynamicParameters();
            parameters.Add("@CityId", CityId, DbType.Int32);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                cities = await conn.QueryFirstOrDefaultAsync<Cities>("spCities_GetOne", parameters, commandType: CommandType.StoredProcedure);
            }
            return cities;
        }

        // Update one Cities row based on its CitiesID (SQL Update)
        public async Task<bool> CitiesUpdate(Cities cities)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("CityId", cities.CityId, DbType.Int32);

                parameters.Add("CityName", cities.CityName, DbType.String);
                parameters.Add("CountryId", cities.CountryId, DbType.Int32);
                parameters.Add("CityPopulation", cities.CityPopulation, DbType.Int32);

                await conn.ExecuteAsync("spCities_Update", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }

        // Physically delete one Cities row based on its CitiesID (SQL Delete)
        public async Task<bool> CitiesDelete(int CityId)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@CityId", CityId, DbType.Int32);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                await conn.ExecuteAsync("spCities_Delete", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }

        public async Task<IEnumerable<Cities>> Cities_GetByCountry(int @CountryId)
        {
            //Cities cities = new Cities();
            IEnumerable<Cities> cities;
            var parameters = new DynamicParameters();
            parameters.Add("@CountryId", CountryId, DbType.Int32);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                cities = await conn.QueryAsync<Cities>("spCities_GetByCountry", parameters, commandType: CommandType.StoredProcedure);
            }
            return cities;
        }

        public async Task<int> CitiesInsertWithDuplicateCheck(string CityName, int CountryId, int CityPopulation)
        {
            int Success = 0;
            var parameters = new DynamicParameters();
            parameters.Add("CityName", CityName, DbType.String);
            parameters.Add("CountryId", CountryId, DbType.Int32);
            parameters.Add("CityPopulation", CityPopulation, DbType.Int32);
            parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                await conn.ExecuteAsync("spCities_InsertWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);

                Success = parameters.Get<int>("@ReturnValue");
            }
            return Success;
        }

        public async Task<int> CitiesUpdateWithDuplicateCheck(string CityName, int CountryId, int CityPopulation, int CityId)
        {
            int Success = 0;
            var parameters = new DynamicParameters();
            parameters.Add("CityName", CityName, DbType.String);
            parameters.Add("CountryId", CountryId, DbType.Int32);
            parameters.Add("CityPopulation", CityPopulation, DbType.Int32);
            parameters.Add("CityId", CityId, DbType.Int32);
            parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                await conn.ExecuteAsync("spCities_UpdateWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);

                Success = parameters.Get<int>("@ReturnValue");
            }
            return Success;
        }
    }
}

ICitiesService.cs

// This is the Cities Interface
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace BlazorCountries.Data
{
    // Each item below provides an interface to a method in CitiesServices.cs
    public interface ICitiesService
    {
        Task<bool> CitiesInsert(Cities cities);
        Task<int> CitiesInsertWithDuplicateCheck(string CityName, int CountryId, int CityPopulation);
        Task<int> CitiesUpdateWithDuplicateCheck(string CityName, int CountryId, int CityPopulation, int CityId);
        Task<IEnumerable<Cities>> CitiesGetAll();
        Task<IEnumerable<Cities>> Cities_GetByCountry(int @CountryId);
        Task<Cities> CitiesGetOne(int CityId);
        Task<bool> CitiesUpdate(Cities cities);
        Task<bool> CitiesDelete(int CityId);
    }
}

CountriesAndCities.razor

@page "/countriesandcities"
@using BlazorCountries.Data
@inject ICountriesService CountriesService
@inject ICitiesService CitiesService

<h3>Countries and Cities</h3>

<div class="control_wrapper">
    <SfDropDownList TItem="Countries"
                    TValue="string"
                    DataSource="@countries"
                    Placeholder="Select a country"
                    PopupHeight="200px"
                    PopupWidth="250px">
        <DropDownListFieldSettings Text="CountryName" Value="CountryId"></DropDownListFieldSettings>
        <DropDownListEvents TItem="Countries" TValue="string" ValueChange="OnChange"></DropDownListEvents>
    </SfDropDownList>
</div>

<div>
    <SfGrid ID="CityGrid"
            DataSource="@cities"
            AllowSorting="true"
            AllowResizing="true"
            Height="200">
        <GridEvents RowSelected="RowSelectHandler" TValue="Cities"></GridEvents>
        <GridColumns>
            <GridColumn Field="@nameof(Cities.CityName)"
                        HeaderText="City Name"
                        TextAlign="@TextAlign.Left"
                        Width="50">
            </GridColumn>
            <GridColumn Field="@nameof(Cities.CityPopulation)"
                        HeaderText="Population"
                        Format="n"
                        TextAlign="@TextAlign.Right"
                        Width="50">
            </GridColumn>
        </GridColumns>
    </SfGrid>
</div>

<div class="e-footer-content">
    <br />
    <SfButton CssClass="e-small e-success" @onclick="AddCity">
        Add a City
    </SfButton>
    <SfButton CssClass="e-small e-success" @onclick="EditCity">
        Edit a City
    </SfButton>
</div>

<SfDialog @ref="DialogAddCity" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
    <EditForm Model="@addCities" OnValidSubmit="@CitiesSave">
        <div>
            <SfTextBox Enabled="true" Placeholder="City"
                       FloatLabelType="@FloatLabelType.Always"
                       @bind-Value="addCities.CityName"></SfTextBox>
            <SfNumericTextBox Enabled="true" Placeholder="Population" Width="50"
                              FloatLabelType="@FloatLabelType.Always"
                              @bind-Value="addCities.CityPopulation"></SfNumericTextBox>
        </div>
        <br /><br />
        <div class="e-footer-content">
            <div class="button-container">
                <button type="submit" class="e-btn e-normal e-primary">Save</button>
            </div>

        </div>
    </EditForm>
</SfDialog>

<SfDialog @ref="DialogEditCity" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
    <EditForm Model="@editCities" OnValidSubmit="@CitiesSaveEdit">
        <div>
            <SfTextBox Enabled="true" Placeholder="City"
                       FloatLabelType="@FloatLabelType.Always"
                       @bind-Value="editCities.CityName"></SfTextBox>
            <SfNumericTextBox Enabled="true" Placeholder="Population" Width="50"
                              FloatLabelType="@FloatLabelType.Always"
                              @bind-Value="editCities.CityPopulation"></SfNumericTextBox>
        </div>
        <br /><br />
        <div class="e-footer-content">
            <div class="button-container">
                <button type="submit" class="e-btn e-normal e-primary">Save</button>

            </div>
        </div>
    </EditForm>
</SfDialog>

<SfDialog @ref="DialogMissingCountry" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
    <DialogTemplates>
        <Header> Warning! </Header>
        <Content> You must select a country from the drop-down list. </Content>
    </DialogTemplates>
    <DialogButtons>
        <DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialogMissingCountry" />
    </DialogButtons>
</SfDialog>

<SfDialog @ref="DialogMissingCity" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
    <DialogTemplates>
        <Header> Warning! </Header>
        <Content> You must select a city from the grid.</Content>
    </DialogTemplates>
    <DialogButtons>
        <DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialogMissingCity" />
    </DialogButtons>
</SfDialog>

<SfDialog @ref="DialogDuplicateCity" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
    <DialogTemplates>
        <Header> Warning! </Header>
        <Content> City already exists for this country; it cannot be added again.</Content>
    </DialogTemplates>
    <DialogButtons>
        <DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialogDuplicateCity" />
    </DialogButtons>
</SfDialog>

<style>
    .control_wrapper {
        width: 250px;
    }
</style>


@code{
    IEnumerable<Countries> countries;
    IEnumerable<Cities> cities;

    SfDialog DialogAddCity;
    SfDialog DialogMissingCountry;
    SfDialog DialogDuplicateCity;
    SfDialog DialogEditCity;
    SfDialog DialogMissingCity;


    Cities addCities = new Cities();
    Cities editCities = new Cities();

    [Parameter]
    public int SelectedCountryId { get; set; } = 0;
    public int SelectedCityId { get; set; } = 0;

    protected override async Task OnInitializedAsync()
    {
        //Populate the list of countries objects from the Countries table.
        countries = await CountriesService.CountriesGetAll();
    }

    public async Task OnChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, Countries> args)
    {
        this.SelectedCountryId = args.ItemData.CountryId;
        cities = await CitiesService.Cities_GetByCountry(this.SelectedCountryId);
        SelectedCityId = 0;         //If this isn't reset, editing a city for a new country could display an old record.
        StateHasChanged();
    }

    public void RowSelectHandler(RowSelectEventArgs<Cities> args)
    {
        //{args.Data} returns the current selected records.
        SelectedCityId = args.Data.CityId;
    }

    private async Task AddCity()
    {
        //Check that a Country has been selected
        if (SelectedCountryId == 0)
        {
            await this.DialogMissingCountry.Show();
        }
        else
        {
            await this.DialogAddCity.Show();
        }
    }

    private async Task CloseDialogMissingCountry()
    {
        await this.DialogMissingCountry.Hide();
    }

    protected async Task CitiesSave()
    {
        if (addCities.CityId == 0)
        {
            // Insert if CityId is zero.

            addCities.CountryId = SelectedCountryId;

            int Success = await CitiesService.CitiesInsertWithDuplicateCheck(addCities.CityName, addCities.CountryId, addCities.CityPopulation);
            if (Success != 0)
            {
                //City Name already exists
                await this.DialogDuplicateCity.Show();
            }
            else
            {
                await this.DialogAddCity.Hide();
                this.StateHasChanged();
            }
        }
        //clear City data
        addCities.CityName = "";
        addCities.CityPopulation = 0;
        cities = await CitiesService.Cities_GetByCountry(this.SelectedCountryId);
        StateHasChanged();
    }

    private async Task EditCity()
    {
        //Check that a City has been selected
        if (SelectedCityId == 0)
        {
            await this.DialogMissingCity.Show();
        }
        else
        {
            //populate editCities (temporary data set used for the editing process)
            editCities = await CitiesService.CitiesGetOne(SelectedCityId);
            await this.DialogEditCity.Show();
        }
    }

    protected async Task CitiesSaveEdit()
    {
        int Success = await CitiesService.CitiesUpdateWithDuplicateCheck(editCities.CityName, editCities.CountryId, editCities.CityPopulation, SelectedCityId);
        if (Success != 0)
        {
            //City Name already exists
            await this.DialogDuplicateCity.Show();
        }
        else
        {
            await this.DialogEditCity.Hide();
            this.StateHasChanged();
            editCities = new Cities();
        }

        cities = await CitiesService.Cities_GetByCountry(this.SelectedCountryId);
        StateHasChanged();
        SelectedCityId = 0;         //If this isn't reset, editing a city for a new country could display an old record.
    }

    private async Task CloseDialogDuplicateCity()
    {
        await this.DialogDuplicateCity.Hide();
    }

    private async Task CloseDialogMissingCity()
    {
        await this.DialogMissingCity.Hide();
    }

}