Countries & Cities - Part 3
Adding Cities
On the Countries page we opened another 'page' to add or edit a country. With Cities we will take a slightly different approach, this time using Syncfusion dialogs within the page to add cities.
The starting point for this is the addition of a button to open an 'Add a City' dialog. Add the following to the HTML section after the </div> following </SfGrid>:
<div class="e-footer-content">
<br />
<SfButton CssClass="e-small e-success" @onclick="AddCity">
Add a City
</SfButton>
</div>We will be adding additional buttons for editing and deleting cities later, but for the time being we keep things as simple as possible by just having 'Add a City'.
To add the dialog to enable adding cities, insert the following immediately after the button section;
<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>The SfDialog is defined with properties of an @ref of "DialogAddCity", it's modal, the close icon will be visible, but the dialog's visibility is set to "false", i.e. it will only become visible once the Add a City button is clicked. Within the SfDialog is an EditForm which uses a Model="@addCities" and an OnvalidSubmit of "@CitiesSave". The form will display 2 text boxes, one for the CityName and the other for the CityPopulation. The form also has a button to handle a valid submission.
In the @code section there is quite a bit to do, and can appear to be a bit messy - but is just a series of steps. There are a couple of potential user-type of errors to trap and deal with, and we may as well deal with them as part of this exercise:
- The user tries to add the same city twice (for a particular country).
- The user doesn't select a Country before clicking the 'Add a City' button.
Checking for duplicate cities
As with the checking for duplicate countries, the basic checking will take place at the SQL level, returning either 0 if no duplicates were found and the new record inserted into the table or 99 if a duplicate found and the insert abandoned. The SQL will be fundamentally the same as for countries, but with the exception that the SQL won't check for a simple duplicate (i.e. the city doesn't already exist), but will add the country into the criteria (i.e. that a duplicate city isn't being inserted for a particular country - Birmingham can exist once in the UK, but it can also exist in another country, such as USA).
SQL
Open SQL Management Studio and open a new query and paste the following code to add a new stored procedure called '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
GOCitiesService
CitiesService.cs needs to be amended to handle the new SQL (just as we did for CountriesService).
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
Similarly we need to update ICitiesService.cs by inserting the following line.
Task<int> CitiesInsertWithDuplicateCheck(string CityName, int CountryId, int CityPopulation);
Warn the user
When an attempt to add a duplicate city has been made, the user must be informed; this will by use of a dialog. Add the following into the HTML section after the AddCity
<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>Checking a country is selected
To warn that a country has not been selected a dialog will be displayed with simply an OK option. To add the dialog, add the following after the 'DialogAddCity' dialog.
<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>With this dialog added we must add the declaration of the SfDialogs to the list of things to do, as well as handling the OnClick events.
Changes to @code
To handle the new dialogs and associated events, we have a bit of work to do:
- Declare the SfDialog for 'DialogAddCity'
- Declare the SfDialog for 'DialogMissingCountry'
- Declare the SfDialog for 'DialogDuplicateCity'
- Declare the addCities object.
- Add the logic to open the dialog when the 'Add a City' button is clicked
- Add the logic to handle the non-selection of a country
- Add the logic to save the City
- Add the logic to handle attempts to add a duplicate city
Add the following at the top of the code section, under the IEnumerables to declare the SfDialogs and 'addCities' object.
SfDialog DialogAddCity;
SfDialog DialogMissingCountry;
SfDialog DialogDuplicateCity;
Cities addCities = new Cities();When 'Add a City' is clicked the onClick event is 'AddCity'. This is handled by the following procedure; add it to the end of the code section.
private async Task AddCity()
{
//Check that a Country has been selected
if (SelectedCountryId == 0)
{
await this.DialogMissingCountry.Show();
}
else
{
await this.DialogAddCity.Show();
}
}The above procedure checks that a country has been selected; if no country is selected it will display the missing country dialog, otherwise it will display the dialog for adding a city.
Taking the first situation, where no country has been selected the 'DialogMissingCountry' displayed informing the user. There is only an OK button for which the 'OnClick' calls 'CloseDialogMissingCountry. The code for this is shown below. Add it to the code section.
private async Task CloseDialogMissingCountry()
{
await this.DialogMissingCountry.Hide();
}
Where a country has been selected and DialogAddCity displayed, it has an on OnValidSubmit procedure. Add the code shown below to handle this event.
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();
}This procedure checks that the CityId is 0, indicating we are adding a new record, not editing an existing city. (This might be superfluous?) It then attempts to add the city; if a duplicate is detected the DialogDuplicateCity is displayed, as a modal dialog nested within the AddCity dialog - (once the warning is acknowledged DialogDuplicateCity is closed using a separate procedure). If no duplicate has been detected, and therefore the new city added, the AddCity dialog is closed. In either case the 'addCities' object is initialised and the 'cities' object refreshed - updating the data-grid for the user.
If a duplicate city is detected the 'DialogDuplicateCity' dialog is displayed. This has on onClick event to handle the user clicking 'OK'. Copy and paste the following code after the 'CitiesSave' procedure.
private async Task CloseDialogDuplicateCity()
{
await this.DialogDuplicateCity.Hide();
}That should be it. Save all files, build and run the application.
Full Code
The full code for the changes made in this section can be found by clicking the above heading.