Shared > Cities.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
namespace BlazorCountriesWasm.Shared
{
public class City
{
public int CityId { get; set; }
[Required(ErrorMessage = "A City name is required.")]
[StringLength(50, MinimumLength = 3, ErrorMessage = "Name must be more than 3 and less than 50 characters.")]
//[MinLength(3, ErrorMessage="Min length is 3")]
//[MaxLength(50, ErrorMessage = "Max length is 50")]
//[Url(ErrorMessage="Not a valid URL")]
//[EmailAddress(ErrorMessage = "Not a valid email address")]
//[CreditCard(ErrorMessage = "Not a valid credit card number (Must be 16 digits)")]
public string CityName { get; set; } = string.Empty;
[Required]
[Range(0, 25000000, ErrorMessage = "Population must be less than 25 million")]
public int CityPopulation { get; set; } = 0;
public int CountryId { get; set; }
}
}
Server > CityController.cs
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Dapper;
using System.Data;
using System.Data.SQLite;
namespace BlazorCountriesWasm.Server.Controllers
{
//[Route("api/[controller]")]
[ApiController]
public class CityController : ControllerBase
{
private readonly IConfiguration _config;
public CityController(IConfiguration config)
{
_config = config;
}
public string connectionId = "Default";
public string sqlCommand = "";
IEnumerable<City>? cities;
[HttpGet]
[Route("api/city/")]
public async Task<ActionResult<List<City>>> GetCities()
{
sqlCommand = "Select * From City ";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
cities = await conn.QueryAsync<City>(sqlCommand);
}
return Ok(cities);
}
[HttpGet]
[Route("api/city/{CityId}")]
public async Task<ActionResult<City>> GetCityById(int CityId)
{
var parameters = new DynamicParameters();
parameters.Add("@CityId", CityId, DbType.Int32);
sqlCommand = $"Select * From City " +
"Where CityId = @CityId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
var city = await conn.QueryFirstAsync<City>(sqlCommand, parameters);
return Ok(city);
}
}
[HttpGet]
[Route("api/citiesbycountryid/{CountryId}")]
public async Task<ActionResult<City>> GetCitiesByCountryId(int CountryId)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
sqlCommand = $"Select * From City " +
"Where CountryId = @CountryId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
cities = await conn.QueryAsync<City>(sqlCommand, parameters);
}
return Ok(cities);
}
[HttpGet]
[Route("api/city/{CountryId}/{CityName}")]
public async Task<ActionResult> CountCitiesForInsert(int CountryId, string CityName)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
parameters.Add("@CityName", CityName, DbType.String);
sqlCommand = $"Select Count(*) From City " +
"Where Upper(CityName) = Upper(@CityName)" +
" and CountryId = @CountryId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
int duplicates = await conn.QuerySingleAsync<int>(sqlCommand, parameters);
return Ok(duplicates);
}
}
[HttpGet]
[Route("api/city/{CountryId}/{CityName}/{CityId}")]
public async Task<ActionResult> CountCitiesForEdit(int CountryId, string CityName, int CityId)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
parameters.Add("@CityName", CityName, DbType.String);
parameters.Add("@CityId", CityId, DbType.Int32);
sqlCommand = $"Select Count(*) From City " +
"Where Upper(CityName) = Upper(@CityName)" +
" and CountryId = @CountryId" +
" and CityId <> @CityId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
int duplicates = await conn.QuerySingleAsync<int>(sqlCommand, parameters);
return Ok(duplicates);
}
}
[HttpPost]
[Route("api/city/")]
public async Task<ActionResult> CityInsert(City city)
{
var parameters = new DynamicParameters();
parameters.Add("CityName", city.CityName, DbType.String);
parameters.Add("@CityPopulation", city.CityPopulation, DbType.Int32);
parameters.Add("@CountryId", city.CountryId, DbType.Int32);
sqlCommand = "Insert into City (CityName, CityPopulation, CountryId) " +
"values(@CityName, @CityPopulation, @CountryId)";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
[HttpPut]
[Route("api/city/{CityId}")]
public async Task<ActionResult> CityUpdate(City city)
{
var parameters = new DynamicParameters();
parameters.Add("CityId", city.CityId, DbType.Int32);
parameters.Add("CityName", city.CityName, DbType.String);
parameters.Add("@CityPopulation", city.CityPopulation, DbType.Int32);
parameters.Add("@CountryId", city.CountryId, DbType.Int32);
sqlCommand =
"Update City " +
"set CityName = @CityName, " +
"CityPopulation = @CityPopulation, " +
"CountryId = @CountryId " +
"Where CityId = @CityId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
[HttpDelete]
[Route("api/city/{CityId}")]
public async Task<ActionResult> CityDelete(int CityId)
{
var parameters = new DynamicParameters();
parameters.Add("CityId", CityId, DbType.Int32);
sqlCommand =
"Delete From City " +
"Where CityId = @CityId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
[HttpGet]
[Route("api/cityname/{CityName}")]
public async Task<ActionResult> CountCitiesByName(string CityName)
{
var parameters = new DynamicParameters();
parameters.Add("@CityName", CityName, DbType.String);
sqlCommand = $"Select Count(*) From City " +
"Where Upper(CityName) = Upper(@CityName)";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
int duplicates = await conn.QueryFirstAsync<int>(sqlCommand, parameters);
return Ok(duplicates);
}
}
[HttpGet]
[Route("api/cityname/{CityName}/{CityId}")]
public async Task<ActionResult> CountCitiesByNameAndId(string CityName, int CityId)
{
var parameters = new DynamicParameters();
parameters.Add("@CityName", CityName, DbType.String);
parameters.Add("@CityId", CityId, DbType.Int32);
sqlCommand = $"Select Count(*) From City " +
"Where Upper(CityName) = Upper(@CityName)" +
"And CityId <> @CityId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
int duplicates = await conn.QueryFirstAsync<int>(sqlCommand, parameters);
return Ok(duplicates);
}
}
}
}
Client > Services > CityService > CityService.cs
using System.Net.Http.Json;
namespace BlazorCountriesWasm.Client.Services.CityService
{
public class CityService : ICityService
{
private readonly HttpClient _http;
public CityService(HttpClient http)
{
_http = http;
}
public List<City> Cities { get; set; } = new List<City>();
public HttpClient? Http { get; } //? here gets rid of green squiggly on "Public CityService(HttpClient http)"
public async Task GetCities()
{
var result = await _http.GetFromJsonAsync<List<City>>("api/city");
if (result != null)
Cities = result;
}
public async Task<City> GetCityById(int id)
{
var result = await _http.GetFromJsonAsync<City>($"api/city/{id}");
return result;
}
public async Task GetCitiesByCountryId(int CountryId)
{
var result = await _http.GetFromJsonAsync<List<City>>($"api/citiesbycountryid/{CountryId}");
Cities = result;
}
public async Task<int> CountCitiesForInsert(int CountryId, string cityName)
{
var result = await _http.GetFromJsonAsync<int>($"api/city/{CountryId}/{cityName}");
return result;
}
public async Task<int> CountCitiesForEdit(int CountryId, string cityName, int CityId)
{
var result = await _http.GetFromJsonAsync<int>($"api/city/{CountryId}/{cityName}/{CityId}");
return result;
}
public async Task<HttpResponseMessage> CityInsert(City city)
{
var result = await _http.PostAsJsonAsync("api/city/", city);
return result;
}
public async Task<HttpResponseMessage> CityUpdate(int Cityid, City city)
{
var result = await _http.PutAsJsonAsync($"api/city/{Cityid}", city);
return result;
}
public async Task CityDelete(int Cityid)
{
var result = await _http.DeleteAsync($"api/city/{Cityid}");
}
public async Task<int> CountCitiesByName(string cityName)
{
var result = await _http.GetFromJsonAsync<int>($"api/cityname/{cityName}");
return result;
}
public async Task<int> CountCitiesByNameAndId(string cityName, int id)
{
var result = await _http.GetFromJsonAsync<int>($"api/cityname/{cityName}/{id}");
return result;
}
}
}
Client > Services > CityService > ICityService.cs
namespace BlazorCountriesWasm.Client.Services.CityService
{
public interface ICityService
{
List<City> Cities { get; set; }
Task GetCities();
Task<HttpResponseMessage> CityInsert(City city);
Task<City> GetCityById(int Cityid);
Task GetCitiesByCountryId(int CountryId);
Task<int> CountCitiesForInsert(int CountryId, string cityName);
Task<int> CountCitiesForEdit(int CountryId, string cityName, int CityId);
Task<HttpResponseMessage> CityUpdate(int Cityid, City city);
Task CityDelete(int Cityid);
Task<int> CountCitiesByName(string cityName);
Task<int> CountCitiesByNameAndId(string cityName, int Id);
}
}
Client > Pages > Index.razor
@page "/"
@inject ICountryService CountryService
@inject ICityService CityService
@inject SfDialogService DialogService
<PageTitle>Countries & Cities List</PageTitle>
<h3>Countries and Cities</h3>
<div class="DropDownWrapper">
<SfDropDownList TItem="Country"
TValue="string"
DataSource="@countries"
Placeholder="Select a country"
PopupHeight="200px"
PopupWidth="250px">
<DropDownListFieldSettings Text="CountryName" Value="CountryId"></DropDownListFieldSettings>
<DropDownListEvents TItem="Country" TValue="string" ValueChange="OnChange"></DropDownListEvents>
</SfDropDownList>
</div>
<hr />
<div>
<SfGrid ID="CityGrid"
DataSource="@cities"
AllowSorting="true"
AllowResizing="true"
Height="200"
Toolbar="Toolbaritems">
<GridColumns>
<GridColumn Field="@nameof(City.CityName)"
HeaderText="City Name"
TextAlign="@TextAlign.Left"
Width="50">
</GridColumn>
<GridColumn Field="@nameof(City.CityPopulation)"
HeaderText="Population"
Format="n"
TextAlign="@TextAlign.Right"
Width="50">
</GridColumn>
</GridColumns>
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="City" RowSelected="RowSelectHandler" />
</SfGrid>
</div>
<div>
<SfDialog @ref="DialogCity" IsModal="true" Width="500px" ShowCloseIcon="false" Visible="false" AllowDragging="true">
<DialogTemplates>
<Header> @dialogTitle</Header>
<Content>
<EditForm Model="@citiesAddEdit" OnValidSubmit="@CitiesSave">
<DataAnnotationsValidator />
<div>
<SfTextBox Enabled="true" Placeholder="City"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="citiesAddEdit.CityName"></SfTextBox>
<ValidationMessage For="@(() => citiesAddEdit.CityName)" />
<SfNumericTextBox Enabled="true" Placeholder="Population" Width="50"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="citiesAddEdit.CityPopulation"></SfNumericTextBox>
<ValidationMessage For="@(() => citiesAddEdit.CityPopulation)" />
</div>
<br /><br />
<div class="e-footer-content">
<div class="button-container">
<button type="submit" disabled aria-hidden="true"></button>
<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>
</Content>
</DialogTemplates>
</SfDialog>
</div>
<style>
.DropDownWrapper {
width: 250px;
}
</style>
@code {
List<Country>? countries;
List<Country>? countriesUnordered;
List<City>? cities;
List<City>? citiesUnordered;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
SfDialog? DialogCity;
City citiesAddEdit = new City();
public string dialogTitle = "Add a City";
private int CityId = 0;
private string CityName = string.Empty;
private int CityPopulation = 0;
private int countOfExistingCities = 0;
[Parameter]
public int SelectedCountryId { get; set; } = 0;
protected override async Task OnInitializedAsync()
{
//Add options for the custom toolbar
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new city", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected city", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected city", PrefixIcon = "e-delete" });
//Populate the list of countries objects from the Countries table.
await CountryService.GetCountries();
countriesUnordered = new();
foreach (var country in CountryService.Countries)
countriesUnordered.Add(country);
countries = countriesUnordered.OrderBy(c => c.CountryName).ToList();
}
public async Task OnChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, Country> args)
{
// Populate list of cities for the selected country
SelectedCountryId = args.ItemData.CountryId;
await RefreshCitiesGrid();
}
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (SelectedCountryId == 0)
{
await DialogService.AlertAsync("Please select a country.", "No Country Selected");
return;
}
if (args.Item.Text == "Add")
{
//Code for adding goes here
dialogTitle = "Add a City";
citiesAddEdit = new();
await DialogCity.ShowAsync(false);
}
if (args.Item.Text == "Edit")
{
//Code for editing
dialogTitle = "Edit a City";
//Check that a City has been selected
if (CityId == 0)
{
await DialogService.AlertAsync("Please select a city.", "No City Selected");
return;
}
citiesAddEdit = new();
citiesAddEdit.CityId = CityId;
citiesAddEdit.CityName = CityName;
citiesAddEdit.CityPopulation = CityPopulation;
citiesAddEdit.CountryId = SelectedCountryId;
await DialogCity.ShowAsync(false);
}
if (args.Item.Text == "Delete")
{
//code for deleting
//Check a City has been selected
if (CityId == 0)
{
await DialogService.AlertAsync("Please select a city.", "No City Selected");
return;
}
else
{
//code for deleting
//Check that user really wants to delete the selected city
string dialogMessage = $"Are you sure you want to delete {CityName}?";
bool isConfirm = await DialogService.ConfirmAsync(dialogMessage, "Delete City");
if (isConfirm)
{
await CityService.CityDelete(CityId);
await RefreshCitiesGrid();
}
}
}
}
protected async Task CitiesSave()
{
if (citiesAddEdit.CityId == 0)
{
// Insert if CityId is zero.
citiesAddEdit.CountryId = SelectedCountryId;
//Check for duplicates (countOfExistingCities ==0)
countOfExistingCities = await CityService.CountCitiesForInsert(SelectedCountryId, citiesAddEdit.CityName);
if (countOfExistingCities == 0)
{
await CityService.CityInsert(citiesAddEdit);
await DialogCity.HideAsync();
}
else
{
//Otherwise, display a warning message
string warningHeader = "City/Country Already Exists";
string warningMessage = "Sorry, you can't add this city for this country, it already exists";
await DialogService.AlertAsync(warningMessage, warningHeader);
}
}
else
{
//Editing an existing city
//Check for duplicates (countOfExistingCities ==0)
countOfExistingCities = await CityService.CountCitiesForEdit(SelectedCountryId, citiesAddEdit.CityName, citiesAddEdit.CityId);
if (countOfExistingCities == 0)
{
await CityService.CityUpdate(CityId, citiesAddEdit);
await DialogCity.HideAsync();
}
else
{
//Otherwise, display a warning message
string warningHeader = "City/Country Already Exists";
string warningMessage = "Sorry, you can't the name of the city to this for this country, it already exists";
await DialogService.AlertAsync(warningMessage, warningHeader);
}
}
await RefreshCitiesGrid();
}
public async Task RefreshCitiesGrid()
{
citiesUnordered = new();
await CityService.GetCitiesByCountryId(SelectedCountryId);
foreach (var city in CityService.Cities)
citiesUnordered.Add(city);
//Sort in alphabetical name ascending
cities = citiesUnordered.OrderBy(c => c.CityName).ToList();
//Clear city data
citiesAddEdit = new();
CityId = 0;
CityName = string.Empty;
CityPopulation = 0;
}
void Cancel()
{
DialogCity.HideAsync();
}
public void RowSelectHandler(RowSelectEventArgs<City> args)
{
//{args.Data} returns the current selected records.
CityId = args.Data.CityId;
CityName = args.Data.CityName;
CityPopulation = args.Data.CityPopulation;
}
}