Server > Controllers > CityController.cs
using Dapper;
using Microsoft.AspNetCore.Mvc;
using System.Data;
using System.Data.SQLite;
using System.Threading.Tasks;
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 Cities";
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 Cities " +
"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 Cities " +
"Where CountryId = @CountryId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
cities = await conn.QueryAsync<City>(sqlCommand, parameters);
}
return Ok(cities);
}
[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 Cities " +
"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 Cities " +
"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);
}
}
[HttpPost]
[Route("api/city/")]
public async Task<ActionResult<List<City>>> CityInsert(City city)
{
var parameters = new DynamicParameters();
parameters.Add("@CityName", city.CityName, DbType.String);
sqlCommand = "Insert into Cities (CityName) values(@CityName)";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
[HttpPut]
[Route("api/city/{cityId}")]
public async Task<ActionResult<List<City>>> CityUpdate(City city)
{
var parameters = new DynamicParameters();
parameters.Add("@CityId", city.CityId, DbType.Int32);
parameters.Add("@CityName", city.CityName, DbType.String);
sqlCommand =
"Update Cities " +
"set CityName = @CityName " +
"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 Cities " +
"Where CityId = @CityId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
}
}
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 CityDelete(int Cityid)
{
var result = await _http.DeleteAsync($"api/city/{Cityid}");
}
public async Task CityInsert(City city)
{
var result = await _http.PostAsJsonAsync("api/city/", city); ;
}
public async Task CityUpdate(int Cityid, City city)
{
var result = await _http.PutAsJsonAsync($"api/city/{Cityid}", city);
}
public async Task GetCities()
{
var result = await _http.GetFromJsonAsync<List<City>>("api/city/");
Cities = result;
}
public async Task GetCitiesByCountryId(int CountryId)
{
var result = await _http.GetFromJsonAsync<List<City>>($"api/citiesbycountryid/{CountryId}");
Cities = result;
}
public async Task<City> GetCityById(int id)
{
var result = await _http.GetFromJsonAsync<City>($"api/city/{id}");
if (result != null)
return result;
throw new Exception("City not found!");
}
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<City> GetCityById(int Cityid);
Task GetCitiesByCountryId(int CountryId);
Task CityInsert(City city);
Task 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;
<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>
<div>
<SfGrid ID="CityGrid"
DataSource="@cities"
AllowSorting="true"
AllowResizing="true"
Height="200">
<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>
</SfGrid>
</div>
<style>
.DropDownWrapper {
width: 250px;
}
</style>
@code {
List<Country>? countries;
List<Country>? countriesUnordered;
List<City>? cities;
List<City>? citiesUnordered;
[Parameter]
public int SelectedCountryId { get; set; } = 0;
protected override async Task OnInitializedAsync()
{
//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;
citiesUnordered = new();
await CityService.GetCitiesByCountryId(SelectedCountryId);
foreach (var city in CityService.Cities)
citiesUnordered.Add(city);
cities = citiesUnordered.OrderBy(c => c.CityName).ToList();
}
}
Client > Program.cs
global using BlazorCountriesWasm.Client.Services.CountryService;
global using BlazorCountriesWasm.Client.Services.CityService;
global using BlazorCountriesWasm.Shared;
using BlazorCountriesWasm.Client;
using Microsoft.AspNetCore.Components.Web;
using Microsoft.AspNetCore.Components.WebAssembly.Hosting;
using Syncfusion.Blazor;
using Syncfusion.Blazor.Popups;
var builder = WebAssemblyHostBuilder.CreateDefault(args);
builder.RootComponents.Add<App>("#app");
builder.RootComponents.Add<HeadOutlet>("head::after");
builder.Services.AddScoped(sp => new HttpClient { BaseAddress = new Uri(builder.HostEnvironment.BaseAddress) });
builder.Services.AddScoped<ICountryService, CountryService>();
builder.Services.AddScoped<ICityService, CityService>();
builder.Services.AddSyncfusionBlazor();
builder.Services.AddScoped<SfDialogService>();
var SyncfusionLicenceKey = builder.Configuration["SyncfusionLicenceKey"];
Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense(SyncfusionLicenceKey);
await builder.Build().RunAsync();