Data Classes

Within the Visual Studio project, for each table, we need to add classes for the following:

• Model
• Service
• Interface

Countries

In the following be aware that I have changed Alan Simpson's code slightly.  I have re-named 'List' to 'GetAll', I have removed the underline in 'Get_All' and 'Get_One' in code (but not the SQL) and have deleted both search procedures.

Model Class

Create a new class called 'Countries.cs' (data model) and copy and paste the following code, replacing the Visual Studio generated code.

// This is the model for one row in the database table.
using System;
using System.ComponentModel.DataAnnotations;
namespace BlazorCountries.Data
{
    public class Countries
    {
        [Required]
        public int CountryId { get; set; }
        [StringLength(50)]
        public string CountryName { get; set; }
    }
}

Service Class

Create a new class called 'CountriesService.cs' (data service) and copy and paste the following code, replacing the Visual Studio generated code.

// This is the service for the Countries 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 CountriesService : ICountriesService
    {
        // Database connection
        private readonly SqlConnectionConfiguration _configuration;
        public CountriesService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }

        // Add (create) a Countries table row (SQL Insert)        
        public async Task<bool> CountriesInsert(Countries countries)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("CountryName", countries.CountryName, DbType.String);

                // Stored procedure method
                await conn.ExecuteAsync("spCountries_Insert", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }

        // Get a list of countries rows (SQL Select)
        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;
        }

        // Get one country based on its CountriesID (SQL Select)
        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;
        }
        
        // Update one Countries row based on its CountriesID (SQL Update)
        public async Task<bool> CountriesUpdate(Countries countries)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("CountryId", countries.CountryId, DbType.Int32);

                parameters.Add("CountryName", countries.CountryName, DbType.String);

                await conn.ExecuteAsync("spCountries_Update", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }

        // Physically delete one Countries row based on its CountriesID (SQL Delete)
        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;
        }
    }
}

Interface Class

Create a new class called 'ICountriesService.cs' (interface) and copy and paste the following code, replacing the Visual Studio generated code.

// This is the Countries Interface
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<bool> CountriesInsert(Countries countries);
        Task<IEnumerable<Countries>> CountriesGetAll();
        Task<Countries> CountriesGetOne(int CountryId);
        Task<bool> CountriesUpdate(Countries countries);
        Task<bool> CountriesDelete(int CountryId);
    }
}

Cities

Model Class

Create a new class called 'Cities.cs' (data model) and copy and paste the following code, replacing the Visual Studio generated code.

// This is the model for one row in the database table.
using System;
using System.ComponentModel.DataAnnotations;

namespace BlazorCountries.Data
{
    public class Cities
    {
	[Required]
    public int CityId { get; set; }
    [StringLength(50)]
    public string CityName { get; set; }
    public int CountryId { get; set; }
    public int CityPopulation { get; set; }
    }
}

Service Class

Create a new class called 'CitiesService.cs' (data service) and copy and paste the following code, replacing the Visual Studio generated code.

// 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;
        }
    }
}

Interface Class

Create a new class called 'ICitiesService.cs' (data service) and copy and paste the following code, replacing the Visual Studio generated code.

// 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<Cities> CitiesGetOne(int CityId);
        Task<bool> CitiesUpdate(Cities cities);
        Task<bool> CitiesDelete(int CityId);
    }
}

Startup.cs

Insert the following code into Startup.cs in the 'public void ConfigureServices(IServiceCollection services)' block.  A suitable place would be below the 'services.AddServerSideBlazor();' line.

services.AddScoped<ICountriesService, CountriesService>();
services.AddScoped<ICitiesService, CitiesService>();

Housekeeping

As we don't require the default sample pages added when the project was created they can be deleted.

• Expand the Data folder in Solution Explorer and delete the two classes associated with WeatherForecast.
• Expand the Pages folder and delete 'FetchData.razor' and 'Counter.razor'.
  

Build

To check that all copying and pasting has been carried out correctly, build the application to ensure there are no errors.