Countries & Cities - Part 3 - Code

by Christopher Bell November 1, 2020

There have been quite a few changes in Countries & Cities - Part 3, these are the files that have been added or modified.

SQL for spCities_InsertWithDuplicateChecking

USE [CountriesDb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spCities_InsertWithDuplicateChecking]  
(  
    @CityName varchar(50),
	@CountryId int,
	@CityPopulation int  
)  
AS  
DECLARE @ResultValue int  
BEGIN TRAN  
IF EXISTS  
    (  
          SELECT * FROM Cities  
          WHERE CityName = @CityName and CountryId = @CountryId
        )  
     BEGIN  
         SET  @ResultValue = 99  
     END  
ELSE  
      BEGIN  
           INSERT INTO Cities(CityName, CountryId, CityPopulation) VALUES (@CityName, @CountryId, @CityPopulation)
           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;
        }
    }
}

ICitiesService

// 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<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">

        <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>
</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="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="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;

    Cities addCities = new Cities();

    [Parameter]
    public int SelectedCountryId { 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);
        StateHasChanged();
    }

    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 CloseDialogDuplicateCity()
    {
        await this.DialogDuplicateCity.Hide();
    }

}