CountryController.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 CountryController : ControllerBase
{
private readonly IConfiguration _config;
public CountryController(IConfiguration config)
{
_config = config;
}
public string connectionId = "Default";
public string sqlCommand = "";
IEnumerable<Country>? countries;
[HttpGet]
[Route("api/country/")]
public async Task<ActionResult<List<Country>>> GetCountries()
{
sqlCommand = "Select * From Country";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
countries = await conn.QueryAsync<Country>(sqlCommand);
}
return Ok(countries);
}
[HttpGet]
[Route("api/country/{CountryId}")]
public async Task<ActionResult<Country>> GetCountryById(int CountryId)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryId", CountryId, DbType.Int32);
sqlCommand = $"Select * From Country " +
"Where CountryId = @CountryId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
var country = await conn.QueryFirstAsync<Country>(sqlCommand, parameters);
return Ok(country);
}
}
[HttpPost]
[Route("api/country/")]
public async Task<ActionResult> CountryInsert(Country country)
{
var parameters = new DynamicParameters();
parameters.Add("CountryName", country.CountryName, DbType.String);
sqlCommand = "Insert into Country (CountryName) values(@CountryName)";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
[HttpPut]
[Route("api/country/{CountryId}")]
public async Task<ActionResult> CountryUpdate(Country country)
{
var parameters = new DynamicParameters();
parameters.Add("CountryId", country.CountryId, DbType.Int32);
parameters.Add("CountryName", country.CountryName, DbType.String);
sqlCommand =
"Update Country " +
"set CountryName = @CountryName " +
"Where CountryId = @CountryId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
[HttpDelete]
[Route("api/country/{CountryId}")]
public async Task<ActionResult> CountryDelete(int CountryId)
{
var parameters = new DynamicParameters();
parameters.Add("CountryId", CountryId, DbType.Int32);
sqlCommand =
"Delete From Country " +
"Where CountryId = @CountryId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
await conn.ExecuteAsync(sqlCommand, parameters);
}
return Ok();
}
[HttpGet]
[Route("api/countryname/{CountryName}")]
public async Task<ActionResult> CountCountriesByName(string CountryName)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryName", CountryName, DbType.String);
sqlCommand = $"Select Count(*) From Country " +
"Where Upper(CountryName) = Upper(@CountryName)";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
int duplicates = await conn.QueryFirstAsync<int>(sqlCommand, parameters);
return Ok(duplicates);
}
}
[HttpGet]
[Route("api/countryname/{CountryName}/{CountryId}")]
public async Task<ActionResult> CountCountriesByNameAndId(string CountryName, int CountryId)
{
var parameters = new DynamicParameters();
parameters.Add("@CountryName", CountryName, DbType.String);
parameters.Add("@CountryId", CountryId, DbType.Int32);
sqlCommand = $"Select Count(*) From Country " +
"Where Upper(CountryName) = Upper(@CountryName)" +
"And CountryId <> @CountryId";
using IDbConnection conn = new SQLiteConnection(_config.GetConnectionString(connectionId));
{
int duplicates = await conn.QueryFirstAsync<int>(sqlCommand, parameters);
return Ok(duplicates);
}
}
}
}
CountryService.cs
using System.Net.Http.Json;
namespace BlazorCountriesWasm.Client.Services.CountryService
{
public class CountryService : ICountryService
{
private readonly HttpClient _http;
public CountryService(HttpClient http)
{
_http = http;
}
public List<Country> Countries { get; set; } = new List<Country>();
public HttpClient? Http { get; } //? here gets rid of green squiggly on "Public CountryService(HttpClient http)"
public async Task GetCountries()
{
var result = await _http.GetFromJsonAsync<List<Country>>("api/country");
if (result != null)
Countries = result;
}
public async Task<Country> GetCountryById(int id)
{
var result = await _http.GetFromJsonAsync<Country>($"api/country/{id}");
return result;
}
public async Task<HttpResponseMessage> CountryInsert(Country country)
{
var result = await _http.PostAsJsonAsync("api/country/", country);
return result;
}
public async Task<HttpResponseMessage> CountryUpdate(int Countryid, Country country)
{
var result = await _http.PutAsJsonAsync($"api/country/{Countryid}", country);
return result;
}
public async Task CountryDelete(int Countryid)
{
var result = await _http.DeleteAsync($"api/country/{Countryid}");
}
public async Task<int> CountCountriesByName(string countryName)
{
var result = await _http.GetFromJsonAsync<int>($"api/countryname/{countryName}");
return result;
}
public async Task<int> CountCountriesByNameAndId(string countryName, int id)
{
var result = await _http.GetFromJsonAsync<int>($"api/countryname/{countryName}/{id}");
return result;
}
}
}
ICountryService.cs
namespace BlazorCountriesWasm.Client.Services.CountryService
{
public interface ICountryService
{
List<Country> Countries { get; set; }
Task GetCountries();
Task<HttpResponseMessage> CountryInsert(Country country);
Task<Country> GetCountryById(int Countryid);
Task<HttpResponseMessage> CountryUpdate(int Countryid, Country country);
Task CountryDelete(int Countryid);
Task<int> CountCountriesByName(string countryName);
Task<int> CountCountriesByNameAndId(string countryName, int Id);
}
}
@page "/countriesaddedit/{CountryId:int}"
@inject ICountryService CountryService
@inject NavigationManager NavigationManager
@inject SfDialogService DialogService
<PageTitle>@pagetitle</PageTitle>
<h1>@pagetitle</h1>
<SfDialog IsModal="true" Width="500px" ShowCloseIcon="false" Visible="true">
<h5>@pagetitle</h5>
<br />
<EditForm Model="@country" OnValidSubmit="@CountriesSave">
<div>
<SfTextBox Enabled="true" Placeholder="Country"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="country.CountryName"></SfTextBox>
</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>
@code {
// Create a new, empty Country object
public Country country = new Country();
public string pagetitle = "Add a Country";
[Parameter]
public int CountryId { get; set; }
//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
{
pagetitle = "Edit a Country";
country = await CountryService.GetCountryById(CountryId);
}
}
// Executes OnValidSubmit of EditForm above.
protected async Task CountriesSave()
{
if (CountryId == 0)
{
//Check for duplicates
int duplicates = await CountryService.CountCountriesByName(country.CountryName);
if (duplicates == 0)
{
//Count is zero, so duplicate won't be created - proceed with insert
var result = await CountryService.CountryInsert(country);
//If insert is successful, navigate back to Country List
if (result.IsSuccessStatusCode)
{
NavigationManager.NavigateTo("/countrylist");
}
//Otherwise warn the user the insert has failed with unknown error
else
{
await DialogService.AlertAsync("Unknown Error!", "Can't insert country.");
}
}
else
{
//Count is not zero, so Country Name already exists in the database.
//Warn the user, but keep input dialog open
string header = "Country Already Exists";
string warningMessage = "Sorry, you can't add this country again, it already exists.";
await DialogService.AlertAsync(warningMessage, header);
}
}
else
{
//Editing - Check for duplicates
int duplicates = await CountryService.CountCountriesByNameAndId(country.CountryName, country.CountryId);
if (duplicates == 0)
{
//Count is zero, so duplicate won't be created - proceed with update
var result = await CountryService.CountryUpdate(CountryId, country);
//If insert is successful, navigate back to Country List
if (result.IsSuccessStatusCode)
{
NavigationManager.NavigateTo("/countrylist");
}
//Otherwise warn the user the insert has failed with unknown error
else
{
await DialogService.AlertAsync("Unknown Error!", "Can't insert country.");
}
}
else
{
//Count is not zero, so Country Name already exists in the database.
//Warn the user, but keep input dialog open
string header = "Country Already Exists";
string warningMessage = "Sorry, you can't add this country again, it already exists.";
await DialogService.AlertAsync(warningMessage, header);
}
}
}
//Executes if user clicks the Cancel button.
void Cancel()
{
NavigationManager.NavigateTo("/countrylist");
}
}
@page "/countrylist"
@inject ICountryService CountryService
@inject NavigationManager NavigationManager
@inject SfDialogService DialogService
<PageTitle>Countries List</PageTitle>
<h3>Countries List</h3>
<SfGrid DataSource="@countries"
Toolbar="Toolbaritems">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Country" RowSelected="RowSelectHandler"></GridEvents>
<GridColumns>
<GridColumn Field="@nameof(Country.CountryId)"
HeaderText="Country ID"
TextAlign="@TextAlign.Left"
Width="20">
</GridColumn>
<GridColumn Field="@nameof(Country.CountryName)"
HeaderText="Country Name"
TextAlign="@TextAlign.Left"
Width="80">
</GridColumn>
</GridColumns>
</SfGrid>
@code {
private List<ItemModel> Toolbaritems = new List<ItemModel>(); //provides the list to populate the toolbar items
public List<Country>? countries;
private int CountryID = 0;
private string CountryName = string.Empty;
protected override async Task OnInitializedAsync()
{
//Add options for the custom toolbar
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" });
await RefreshCountriesGrid();
}
public async Task 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")
{
//Check that the user has selected a row
if (CountryID == 0)
{
await DialogService.AlertAsync("Please select a country.", "No Country Selected");
}
else
{
NavigationManager.NavigateTo($"/countriesaddedit/{CountryID}");
CountryID = 0;
}
}
if (args.Item.Text == "Delete")
{
//Check that the user has selected a row
if (CountryID == 0)
{
await DialogService.AlertAsync("Please select a country.", "No Country Selected");
}
else
{
//code for deleting
string dialogMessage = $"Are you sure you want to delete {CountryName}?";
bool isConfirm = await DialogService.ConfirmAsync(dialogMessage, "Delete Country");
if (isConfirm)
{
await CountryService.CountryDelete(CountryID);
await RefreshCountriesGrid();
CountryID = 0;
}
}
}
}
public void RowSelectHandler(RowSelectEventArgs<Country> args)
{
//{args.Data} returns the current selected record.
CountryID = args.Data.CountryId;
}
public async Task RefreshCountriesGrid()
{
await CountryService.GetCountries();
countries = new();
foreach (var country in CountryService.Countries)
countries.Add(country);
}
}