CitiesService.cs
// This is the service for the Cities class.
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
public class CitiesService : ICitiesService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public CitiesService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
// Add (create) a Cities table row (SQL Insert)
public async Task<bool> CitiesInsert(Cities cities)
{
using (var conn = new SqlConnection(_configuration.Value))
{
var parameters = new DynamicParameters();
parameters.Add("CityName", cities.CityName, DbType.String);
parameters.Add("CountryId", cities.CountryId, DbType.Int32);
parameters.Add("CityPopulation", cities.CityPopulation, DbType.Int32);
// Stored procedure method
await conn.ExecuteAsync("spCities_Insert", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
// Get a list of cities rows (SQL Select)
public async Task<IEnumerable<Cities>> CitiesGetAll()
{
IEnumerable<Cities> citiess;
using (var conn = new SqlConnection(_configuration.Value))
{
citiess = await conn.QueryAsync<Cities>("spCities_GetAll", commandType: CommandType.StoredProcedure);
}
return citiess;
}
// Get one cities based on its CitiesID (SQL Select)
public async Task<Cities> CitiesGetOne(int @CityId)
{
Cities cities = new Cities();
var parameters = new DynamicParameters();
parameters.Add("@CityId", CityId, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
cities = await conn.QueryFirstOrDefaultAsync<Cities>("spCities_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return cities;
}
// Update one Cities row based on its CitiesID (SQL Update)
public async Task<bool> CitiesUpdate(Cities cities)
{
using (var conn = new SqlConnection(_configuration.Value))
{
var parameters = new DynamicParameters();
parameters.Add("CityId", cities.CityId, DbType.Int32);
parameters.Add("CityName", cities.CityName, DbType.String);
parameters.Add("CountryId", cities.CountryId, DbType.Int32);
parameters.Add("CityPopulation", cities.CityPopulation, DbType.Int32);
await conn.ExecuteAsync("spCities_Update", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
// Physically delete one Cities row based on its CitiesID (SQL Delete)
public async Task<bool> CitiesDelete(int CityId)
{
var parameters = new DynamicParameters();
parameters.Add("@CityId", CityId, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCities_Delete", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
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;
}
}
}
ICitiesService.cs
// This is the Cities Interface
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
// Each item below provides an interface to a method in CitiesServices.cs
public interface ICitiesService
{
Task<bool> CitiesInsert(Cities cities);
Task<IEnumerable<Cities>> CitiesGetAll();
Task<IEnumerable<Cities>> Cities_GetByCountry(int @CountryId);
Task<Cities> CitiesGetOne(int CityId);
Task<bool> CitiesUpdate(Cities cities);
Task<bool> CitiesDelete(int CityId);
}
}
CountriesAndCities.razor
@page "/countriesandcities"
@using BlazorCountries.Data
@inject ICountriesService CountriesService
@inject ICitiesService CitiesService
<h3>Countries and Cities</h3>
<div class="control_wrapper">
<SfDropDownList TItem="Countries"
TValue="string"
DataSource="@countries"
Placeholder="Select a country"
PopupHeight="200px"
PopupWidth="250px">
<DropDownListFieldSettings Text="CountryName" Value="CountryId"></DropDownListFieldSettings>
<DropDownListEvents TItem="Countries" TValue="string" ValueChange="OnChange"></DropDownListEvents>
</SfDropDownList>
</div>
<div>
<SfGrid ID="CityGrid"
DataSource="@cities"
AllowSorting="true"
AllowResizing="true"
Height="200">
<GridColumns>
<GridColumn Field="@nameof(Cities.CityName)"
HeaderText="City Name"
TextAlign="@TextAlign.Left"
Width="50">
</GridColumn>
<GridColumn Field="@nameof(Cities.CityPopulation)"
HeaderText="Population"
Format="n"
TextAlign="@TextAlign.Right"
Width="50">
</GridColumn>
</GridColumns>
</SfGrid>
</div>
<style>
.control_wrapper {
width: 250px;
}
</style>
@code{
IEnumerable<Countries> countries;
IEnumerable<Cities> cities;
[Parameter]
public int SelectedCountryId { get; set; } = 0;
protected override async Task OnInitializedAsync()
{
//Populate the list of countries objects from the Countries table.
countries = await CountriesService.CountriesGetAll();
}
public async Task OnChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, Countries> args)
{
this.SelectedCountryId = args.ItemData.CountryId;
cities = await CitiesService.Cities_GetByCountry(this.SelectedCountryId);
StateHasChanged();
}
}
spCities_GetByCountry
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