Complete Code
Data Models
Cities.cs
using System;
using System.ComponentModel.DataAnnotations;
namespace BlazorCountries.Data
{
public class Cities
{
[Required]
public int CityId { get; set; }
[Required]
public int CountryId { get; set; }
[Required]
[StringLength(50, ErrorMessage = "Name is too long - it cannot be longer than 50 characters.")]
public string CityName { get; set; }
[Required]
[Range(0, 50000000, ErrorMessage = "Population must be between 0 and 50 million")]
public int CityPopulation { get; set; }
}
}
Countries.cs
using System;
using System.ComponentModel.DataAnnotations;
namespace BlazorCountries.Data
{
public class Countries
{
[Required]
public int CountryId { get; set; }
[Required]
[StringLength(50, ErrorMessage = "Name is too long - it cannot be longer than 50 characters.")]
public string CountryName { get; set; }
}
}
Services
CitiesService.cs
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;
}
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;
}
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;
}
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;
}
public async Task<IEnumerable<Cities>> Cities_GetByCountry(int @CountryId)
{
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;
}
public async Task<int> CitiesUpdateWithDuplicateCheck(string CityName, int CountryId, int CityPopulation, int CityId)
{
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("CityId", CityId, DbType.Int32);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCities_UpdateWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
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;
}
}
}
CountriesService.cs
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
public class CountriesService : ICountriesService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public CountriesService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
public async Task<int> CountriesInsertWithDuplicatesCheck(string CountryName)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("@CountryName", CountryName, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_InsertWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
public async Task<IEnumerable<Countries>> CountriesGetAll()
{
IEnumerable<Countries> countries;
using (var conn = new SqlConnection(_configuration.Value))
{
countries = await conn.QueryAsync<Countries>("spCountries_GetAll", commandType: CommandType.StoredProcedure);
}
return countries;
}
public async Task<Countries> CountriesGetOne(int @CountryId)
{
Countries countries = new Countries();
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
countries = await conn.QueryFirstOrDefaultAsync<Countries>("spCountries_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return countries;
}
public async Task<int> CountriesUpdateWithDuplicatesCheck(Countries countries)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("CountryId", countries.CountryId, DbType.Int32);
parameters.Add("CountryName", countries.CountryName, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_UpdateWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
public async Task<bool> CountriesDelete(int CountryId)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_Delete", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
}
}
Interfaces
ICitiesService.cs
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<int> CitiesInsertWithDuplicateCheck(string CityName, int CountryId, int CityPopulation);
Task<IEnumerable<Cities>> CitiesGetAll();
Task<Cities> CitiesGetOne(int CityId);
Task<IEnumerable<Cities>> Cities_GetByCountry(int CountryId);
Task<int> CitiesUpdateWithDuplicateCheck(string CityName, int CountryId, int CityPopulation, int CityId);
Task<bool> CitiesDelete(int CityId);
}
}
ICountriesService.cs
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
// Each item below provides an interface to a method in CountriesServices.cs
public interface ICountriesService
{
Task<int> CountriesInsertWithDuplicatesCheck(string CountryName);
Task<IEnumerable<Countries>> CountriesGetAll();
Task<Countries> CountriesGetOne(int CountryId);
Task<int> CountriesUpdateWithDuplicatesCheck(Countries countries);
Task<bool> CountriesDelete(int CountryId);
}
}
SqlConnectionConfiguration.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
public class SqlConnectionConfiguration
{
public SqlConnectionConfiguration(string value) => Value = value;
public string Value { get; }
}
}
Pages
Index.razor
@page "/"
@using BlazorCountries.Data
@inject ICountriesService CountriesService
@inject NavigationManager NavigationManager
<div class="col-sm-8">
<h3>Countries List</h3>
<br />
<SfGrid DataSource="@countries"
AllowSorting="true"
AllowResizing="true"
AllowFiltering="true"
AllowPaging="true"
AllowReordering="true"
Toolbar="Toolbaritems">
<GridFilterSettings Type="Syncfusion.Blazor.Grids.FilterType.Menu"></GridFilterSettings>
<GridPageSettings PageSize="10"></GridPageSettings>
<GridEvents RowSelected="RowSelectHandler" OnToolbarClick="ToolbarClickHandler" TValue="Countries"></GridEvents>
<GridColumns>
<GridColumn Field="@nameof(Countries.CountryName)"
HeaderText="Country Name"
TextAlign="@TextAlign.Left"
Width="90">
</GridColumn>
</GridColumns>
</SfGrid>
</div>
<SfDialog @ref="DialogDelete" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Confirm Delete </Header>
<Content> Please confirm that you want to delete this record </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="Delete" IsPrimary="true" OnClick="@ConfirmDeleteYes" />
<DialogButton Content="Cancel" IsPrimary="false" OnClick="@ConfirmDeleteNo" />
</DialogButtons>
</SfDialog>
<SfDialog @ref="DialogNoRecordSelected" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Warning! </Header>
<Content> You must select a country </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialogNoRecordSelected" />
</DialogButtons>
</SfDialog>
@code {
SfDialog DialogDelete;
SfDialog DialogNoRecordSelected;
private int? CountryID;
// Create an empty list, named countries, of empty Counties objects.
IEnumerable<Countries> countries;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
protected override async Task OnInitializedAsync()
{
//Populate the list of countries objects from the Countries table.
countries = await CountriesService.CountriesGetAll();
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new country", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected country", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected country", PrefixIcon = "e-delete" });
}
public void RowSelectHandler(RowSelectEventArgs<Countries> args)
{
//{args.Data} returns the current selected records.
CountryID = args.Data.CountryId;
}
public void ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
//Code for adding goes here
CountryID = 0;
NavigationManager.NavigateTo($"/countriesaddedit/{CountryID}");
}
if (args.Item.Text == "Edit")
{
//Code for editing
if (CountryID > 0)
{
NavigationManager.NavigateTo($"/countriesaddedit/{CountryID}");
}
else
{
//No record has been selected
DialogNoRecordSelected.Show();
}
}
if (args.Item.Text == "Delete")
{
if (CountryID > 0)
{
DialogDelete.Show();
}
else
{
//No record has been selected
DialogNoRecordSelected.Show();
}
}
}
private async Task CloseDialogNoRecordSelected()
{
await this.DialogNoRecordSelected.Hide();
}
public async void ConfirmDeleteNo()
{
await DialogDelete.Hide();
}
public async void ConfirmDeleteYes()
{
await CountriesService.CountriesDelete(CountryID.GetValueOrDefault()); //This deletes the record
await DialogDelete.Hide();
// Both following lines required to refresh the grid
countries = await CountriesService.CountriesGetAll();
CountryID = 0; //Reset CountryID
StateHasChanged();
}
}
CountriesAddEdit.razor
@using BlazorCountries.Data
@page "/countriesaddedit/{CountryId:int}"
@inject ICountriesService CountriesService
@inject NavigationManager NavigationManager
<h1>@pagetitle</h1>
<SfDialog IsModal="true" Width="500px" ShowCloseIcon="false" Visible="true">
<h5>@pagetitle</h5>
<br />
<EditForm Model="@countries" OnValidSubmit="@CountriesSave">
<DataAnnotationsValidator />
<div>
<SfTextBox Enabled="true" Placeholder="Country"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="countries.CountryName"></SfTextBox>
<ValidationMessage For="@(() => countries.CountryName)" />
</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>
</SfDialog>
<SfDialog @ref="DialogWarning" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Warning! </Header>
<Content> This country already exists! It won't be added again. </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialog" />
</DialogButtons>
</SfDialog>
@code {
// Create a new, empty Countries object
Countries countries = new Countries();
SfDialog DialogWarning;
[Parameter]
public int CountryId { get; set; }
public string pagetitle = "Add a Country";
//Executes on page open, sets headings and gets data in the case of edit
protected override async Task OnInitializedAsync()
{
if (CountryId == 0)
{
pagetitle = "Add a Country";
}
else
{
countries = await CountriesService.CountriesGetOne(CountryId);
pagetitle = "Edit a Country";
}
}
// Executes OnValidSubmit of EditForm above.
protected async Task CountriesSave()
{
if (CountryId == 0)
{
int Success = await CountriesService.CountriesInsertWithDuplicatesCheck(countries.CountryName);
if (Success != 0)
{
//Country Name already exists
await OpenDialog();
}
else
{
NavigationManager.NavigateTo("/");
}
}
else
{
int Success = await CountriesService.CountriesUpdateWithDuplicatesCheck(countries);
if (Success != 0)
{
//Country Name already exists
await this.OpenDialog();
}
else
{
NavigationManager.NavigateTo("/");
}
}
}
//Executes if user clicks the Cancel button.
void Cancel()
{
NavigationManager.NavigateTo("/");
}
private async Task OpenDialog()
{
await this.DialogWarning.Show();
}
private async Task CloseDialog()
{
await this.DialogWarning.Hide();
NavigationManager.NavigateTo("/");
}
}
CountriesAndCities.razor
@page "/countriesandcities"
@using BlazorCountries.Data
@inject ICountriesService CountriesService
@inject ICitiesService CitiesService
<div class="col-sm-8">
<h3>Countries and Cities</h3>
<br />
<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>
<br />
<div>
<SfGrid ID="CityGrid"
DataSource="@cities"
AllowSorting="true"
AllowResizing="true"
Height="200">
<GridEvents RowSelected="RowSelectHandler" TValue="Cities"></GridEvents>
<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>
<GridAggregates>
<GridAggregate>
<GridAggregateColumns>
<GridAggregateColumn Field=@nameof(Cities.CityPopulation) Type="AggregateType.Sum" Format="N">
<FooterTemplate>
@{
var aggregate = (context as AggregateTemplateContext);
<div>
<p>Total Cities Population: @aggregate.Sum</p>
</div>
}
</FooterTemplate>
</GridAggregateColumn>
</GridAggregateColumns>
</GridAggregate>
</GridAggregates>
</SfGrid>
</div>
<div class="e-footer-content">
<br />
<SfButton CssClass="e-small e-success" @onclick="AddCity">
Add a City
</SfButton>
<SfButton CssClass="e-small e-success" @onclick="EditCity">
Edit a City
</SfButton>
<SfButton CssClass="e-small e-success" @onclick="DeleteCity">
Delete a City
</SfButton>
</div>
</div>
<SfDialog @ref="DialogAddCity" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<EditForm Model="@addCities" OnValidSubmit="@CitiesSave">
<DataAnnotationsValidator />
<div>
<SfTextBox Enabled="true" Placeholder="City"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="addCities.CityName"></SfTextBox>
<ValidationMessage For="@(() => addCities.CityName)" />
<SfNumericTextBox Enabled="true" Placeholder="Population" Width="50"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="addCities.CityPopulation"></SfNumericTextBox>
<ValidationMessage For="@(() => addCities.CityPopulation)" />
</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>
<SfDialog @ref="DialogEditCity" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<EditForm Model="@editCities" OnValidSubmit="@CitiesSaveEdit">
<DataAnnotationsValidator />
<div>
<SfTextBox Enabled="true" Placeholder="City"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="editCities.CityName"></SfTextBox>
<ValidationMessage For="@(() => editCities.CityName)" />
<SfNumericTextBox Enabled="true" Placeholder="Population" Width="50"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="editCities.CityPopulation"></SfNumericTextBox>
<ValidationMessage For="@(() => editCities.CityPopulation)" />
</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>
<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>
<SfDialog @ref="DialogMissingCity" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Warning! </Header>
<Content> You must select a city from the grid.</Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialogMissingCity" />
</DialogButtons>
</SfDialog>
<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>
<SfDialog @ref="DialogDeleteCity" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Confirm Delete </Header>
<Content> Please confirm that you want to delete this record. </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="Delete" IsPrimary="true" OnClick="@ConfirmDeleteYes" />
<DialogButton Content="Cancel" IsPrimary="false" OnClick="@ConfirmDeleteNo" />
</DialogButtons>
</SfDialog>
<style>
.control_wrapper {
width: 250px;
}
</style>
@code{
IEnumerable<Countries> countries;
IEnumerable<Cities> cities;
SfDialog DialogAddCity;
SfDialog DialogMissingCountry;
SfDialog DialogDuplicateCity;
SfDialog DialogEditCity;
SfDialog DialogMissingCity;
SfDialog DialogDeleteCity;
Cities addCities = new Cities();
Cities editCities = new Cities();
[Parameter]
public int SelectedCountryId { get; set; } = 0;
public int SelectedCityId { 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);
SelectedCityId = 0; //If this isn't reset, editing a city for a new country could display an old record.
StateHasChanged();
}
public void RowSelectHandler(RowSelectEventArgs<Cities> args)
{
//{args.Data} returns the current selected records.
SelectedCityId = args.Data.CityId;
}
private async Task AddCity()
{
//Check that a Country has been selected
if (SelectedCountryId == 0)
{
await this.DialogMissingCountry.Show();
}
else
{
await this.DialogAddCity.Show();
}
}
private async Task CloseDialogMissingCountry()
{
await this.DialogMissingCountry.Hide();
}
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();
}
private async Task EditCity()
{
//Check that a City has been selected
if (SelectedCityId == 0)
{
await this.DialogMissingCity.Show();
}
else
{
//populate editCities (temporary data set used for the editing process)
editCities = await CitiesService.CitiesGetOne(SelectedCityId);
await this.DialogEditCity.Show();
}
}
protected async Task CitiesSaveEdit()
{
int Success = await CitiesService.CitiesUpdateWithDuplicateCheck(editCities.CityName, editCities.CountryId, editCities.CityPopulation, SelectedCityId);
if (Success != 0)
{
//City Name already exists
await this.DialogDuplicateCity.Show();
}
else
{
await this.DialogEditCity.Hide();
this.StateHasChanged();
editCities = new Cities();
}
cities = await CitiesService.Cities_GetByCountry(this.SelectedCountryId);
StateHasChanged();
SelectedCityId = 0; //If this isn't reset, editing a city for a new country could display an old record.
}
private async Task CloseDialogDuplicateCity()
{
await this.DialogDuplicateCity.Hide();
}
private async Task CloseDialogMissingCity()
{
await this.DialogMissingCity.Hide();
}
private async Task DeleteCity()
{
//Check that a City has been selected
if (SelectedCityId == 0)
{
await this.DialogMissingCity.Show();
}
else
{
await this.DialogDeleteCity.Show();
}
}
public async void ConfirmDeleteYes()
{
await CitiesService.CitiesDelete(SelectedCityId);
cities = await CitiesService.Cities_GetByCountry(this.SelectedCountryId);
await this.DialogDeleteCity.Hide();
StateHasChanged();
SelectedCityId = 0;
}
public async void ConfirmDeleteNo()
{
await this.DialogDeleteCity.Hide();
}
}
NavMenu.razor
<div class="top-row pl-4 navbar navbar-dark">
<a class="navbar-brand" href="">Blazor Countries</a>
<button class="navbar-toggler" @onclick="ToggleNavMenu">
<span class="navbar-toggler-icon"></span>
</button>
</div>
<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
<ul class="nav flex-column">
<li class="nav-item px-3">
<NavLink class="nav-link" href="" Match="NavLinkMatch.All">
<span class="oi oi-home" aria-hidden="true"></span> Countries
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="countriesandcities">
<span class="oi oi-plus" aria-hidden="true"></span> Cities
</NavLink>
</li>
</ul>
</div>
@code {
private bool collapseNavMenu = true;
private string NavMenuCssClass => collapseNavMenu ? "collapse" : null;
private void ToggleNavMenu()
{
collapseNavMenu = !collapseNavMenu;
}
}
Imports.razor
@using System.Net.Http
@using Microsoft.AspNetCore.Authorization
@using Microsoft.AspNetCore.Components.Authorization
@using Microsoft.AspNetCore.Components.Forms
@using Microsoft.AspNetCore.Components.Routing
@using Microsoft.AspNetCore.Components.Web
@using Microsoft.JSInterop
@using BlazorCountries
@using BlazorCountries.Shared
@using Syncfusion.Blazor
@using Syncfusion.Blazor.Inputs
@using Syncfusion.Blazor.Popups
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor.DropDowns
@using Syncfusion.Blazor.Layouts
@using Syncfusion.Blazor.Calendars
@using Syncfusion.Blazor.Navigations
@using Syncfusion.Blazor.Lists
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Buttons
@using Syncfusion.Blazor.NotificationsSystem
appsettings.json
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"SqlDBcontext": "YOUR Data Source HERE...."
}
}Program.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
namespace BlazorCountries
{
public class Program
{
public static void Main(string[] args)
{
CreateHostBuilder(args).Build().Run();
}
public static IHostBuilder CreateHostBuilder(string[] args) =>
Host.CreateDefaultBuilder(args)
.ConfigureWebHostDefaults(webBuilder =>
{
webBuilder.UseStartup<Startup>();
});
}
}Startup.cs
using BlazorCountries.Data;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Syncfusion.Blazor;
namespace BlazorCountries
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
public void ConfigureServices(IServiceCollection services)
{
services.AddRazorPages();
services.AddServerSideBlazor();
services.AddSyncfusionBlazor();
var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDBContext"));
services.AddSingleton(sqlConnectionConfiguration);
services.AddScoped<ICountriesService, CountriesService>();
services.AddScoped<ICitiesService, CitiesService>();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseEndpoints(endpoints =>
{
endpoints.MapBlazorHub();
endpoints.MapFallbackToPage("/_Host");
});
//Register Syncfusion license
Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense("Your Licence Key Here");
}
}
}
SQL
Script to drop and create all tables and stored procedures
USE [CountriesDb]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCountries_UpdateWithDuplicateChecking]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCountries_InsertWithDuplicateChecking]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCountries_GetOne]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCountries_GetAll]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCountries_Delete]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCities_UpdateWithDuplicateChecking]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCities_InsertWithDuplicateChecking]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCities_GetOne]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCities_GetByCountry]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCities_GetAll]
GO
DROP PROCEDURE IF EXISTS [dbo].[spCities_Delete]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
ALTER TABLE [dbo].[Cities] DROP CONSTRAINT IF EXISTS [FK_Cities_Countries]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cities]') AND type in (N'U'))
ALTER TABLE [dbo].[Cities] DROP CONSTRAINT IF EXISTS [DF_Cities_CityPopulation]
GO
DROP TABLE IF EXISTS [dbo].[Countries]
GO
DROP TABLE IF EXISTS [dbo].[Cities]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cities](
[CityId] [int] IDENTITY(1,1) NOT NULL,
[CountryId] [int] NOT NULL,
[CityName] [varchar](50) NOT NULL,
[CityPopulation] [int] NOT NULL,
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
[CityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Countries](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Cities] ADD CONSTRAINT [DF_Cities_CityPopulation] DEFAULT ((0)) FOR [CityPopulation]
GO
ALTER TABLE [dbo].[Cities] WITH CHECK ADD CONSTRAINT [FK_Cities_Countries] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Countries] ([CountryId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Cities] CHECK CONSTRAINT [FK_Cities_Countries]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for DELETE
CREATE PROCEDURE [dbo].[spCities_Delete]
-- One parameter required to identify row to delete.
@CityId int
AS
BEGIN
-- SQL for Delete stored procedure (physically deletes, you may want to change this to mark inactive)
DELETE FROM Cities WHERE CityId = @CityId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE [dbo].[spCities_GetAll]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT CityId, CountryId, CityName, CityPopulation FROM Cities ORDER BY CityId DESC
END
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for SELECT (one)
CREATE PROCEDURE [dbo].[spCities_GetOne]
-- Needs one parameter for primary key
@CityId int
AS
BEGIN
-- SQL Select for one table row
SELECT CityId, CountryId, CityName, CityPopulation FROM Cities WHERE CityId= @CityId
END
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
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spCities_UpdateWithDuplicateChecking]
-- Parameters for Update stored procedure.
@CityId int,
@CityName varchar(50),
@CountryId int,
@CityPopulation int
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Cities
WHERE (CityName = @CityName and CountryId = @CountryId) AND CityId <> @CityId
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
UPDATE Cities SET CityName = @CityName, CountryId = @CountryId, CityPopulation = @CityPopulation WHERE CityId = @CityId
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for DELETE
CREATE PROCEDURE [dbo].[spCountries_Delete]
-- One parameter required to identify row to delete.
@CountryId int
AS
BEGIN
-- SQL for Delete stored procedure (physically deletes, you may want to change this to mark inactive)
DELETE FROM Countries WHERE CountryId = @CountryId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE [dbo].[spCountries_GetAll]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT CountryId, CountryName FROM Countries ORDER BY CountryName ASC
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for SELECT (one)
CREATE PROCEDURE [dbo].[spCountries_GetOne]
-- Needs one parameter for primary key
@CountryId int
AS
BEGIN
-- SQL Select for one table row
SELECT CountryId, CountryName FROM Countries WHERE CountryId= @CountryId
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCountries_InsertWithDuplicateChecking]
(
@CountryName VARCHAR(50)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Countries
WHERE CountryName = @CountryName
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
INSERT INTO Countries
(
CountryName
)
VALUES
(
@CountryName
)
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spCountries_UpdateWithDuplicateChecking]
(
@CountryId int,
@CountryName varchar(50)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Countries
WHERE CountryName = @CountryName and CountryId <> @CountryId
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
UPDATE Countries SET CountryName = @CountryName WHERE CountryId = @CountryId
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO