Countries & Cities - Part 2

In CountriesAndCities.razor we have a page with a Countries drop-down list and a Cities datagrid, but they are currently not connected, and we haven't got a way of adding (or editing or deleting) Cities.

Linking Countries with Cities

To link the Countries drop-down list to the Cities grid, we need to add something to the drop-down list to detect when the value in the drop-down changes and then to do something to get the cities records.

Step one is to open CountriesAndCities.razor (if not already) and to add the following after the <DropDownListFieldSettings.. line:

<DropDownListEvents TItem="Countries" TValue="string" ValueChange="OnChange"></DropDownListEvents>

When the value of the country changes we need the datagrid to be populated with cities for that country only.  However, the SQL stored procedures created by Alan Simpson's code generator do not include one that just returns the cities for a specific country.  We therefore need to add a new SQL Stored Procedure for that situation and then add the associated code to the CitiesService and ICitiesService interface.

Step two is to add the SQL Stored Procedure (called spCities_GetByCountry).  In SQL Management Studio, open a New Query window and paste into it the following code and then run the query.

USE [CountriesDb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE [dbo].[spCities_GetByCountry]
@CountryId int
AS 
BEGIN
-- SQL Select 
SELECT CityId, CityName, CountryId, CityPopulation FROM Cities WHERE CountryId= @CountryId
ORDER BY CityName
END

GO

Step three is to update CitiesService.cs to include the code to handle getting cities by CountryId. Paste the following code anywhere within the CitiesService procedure.

        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;
        }

Step four is to update ICitiesService.cs to include the Cities_GetByCountry service.  Insert the following line in ICitiesService.cs, I suggest under the existing Task<IEnumerable>

Task<IEnumerable<Cities>> Cities_GetByCountry(int @CountryId);

Step five is to add code into the @code section of CountriesAndCities.razor to  the handle the change event in the drop-down list, and to declare the parameter being passed to it.  Add the following under the 'IEnumerablecities' line;.

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

And add the following to the end of the code section.

public async Task OnChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, Countries> args)
    {
        this.SelectedCountryId = args.ItemData.CountryId;
        cities = await CitiesService.Cities_GetByCountry(this.SelectedCountryId);
        StateHasChanged();
    }

When the value in the Countries drop-down list changes, the above refreshes the 'cities', limiting them to those associated with the country.

Although we still have no City records, build and run the application to check no errors have crept in.

Full Code

The full code for the changes made in this section can be found by clicking the above heading.

Powered by Publii