Server > Controllers > 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);
}
[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 > 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">
<div>
<SfTextBox Enabled="true" Placeholder="City"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="citiesAddEdit.CityName"></SfTextBox>
<SfNumericTextBox Enabled="true" Placeholder="Population" Width="50"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="citiesAddEdit.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>
<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;
[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;
await CityService.CityInsert(citiesAddEdit);
await DialogCity.HideAsync();
}
else
{
//Editing an existing city
await CityService.CityUpdate(CityId, citiesAddEdit);
await DialogCity.HideAsync();
}
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;
}
}