Create A Blazor Server SPA With Dapper

In this post, we will see how to create a single page application in Blazor Server with Dapper ORM. We will see all the CRUD operations with Dapper using native SQL commands and stored procedures.

Introduction


Blazor is a new framework built by Microsoft for creating interactive client-side web UI with .NET codebase. We can write both client-side and server-side code in C#.NET itself. I have already written three articles about Blazor server on C# Corner. Please refer to below articles for more basics about Blazor framework.
Dapper is a micro ORM (Object Relational Mapper) which helps to map the native query output to a domain class. It is a high-performance data access system built by StackOverflow team and released as open source. If your project prefers writing stored procedures or writing raw SQL queries instead of using a full-fledged ORM tools like EntityFramework, then Dapper is a better choice for you. Using Dapper, it is very easy to execute an SQL query against database and get the result mapped to C# domain class.
 
We will create a single page application in Blazor with two entities City and Employee. We will see all CRUD actions with these City and Employee entities. We will use raw SQL queries in City and stored procedures in Employee. We can see all the actions step by step.

Create database, tables and stored procedures in SQL server


Please use below SQL script to create new database, tables and stored procedures. As I told earlier, we will use these stored procedures for fetching and storing employee data using Dapper.
  1. USE master  
  2. CREATE DATABASE SarathlalDb;  
  3.   
  4. GO  
  5.   
  6. USE SarathlalDb  
  7.   
  8. CREATE TABLE [dbo].[City] (  
  9.     [Id]    INT           IDENTITY (1, 1) NOT NULL,  
  10.     [Name]  NVARCHAR (250) NULL,  
  11.     [State] NVARCHAR (250) NULL,  
  12.     PRIMARY KEY CLUSTERED ([Id] ASC)  
  13. );  
  14.   
  15. GO  
  16.   
  17. CREATE TABLE [dbo].[Employees] (  
  18.     [Id]          INT            IDENTITY (1, 1) NOT NULL,  
  19.     [Name]        NVARCHAR (250) NULL,  
  20.     [Department]  NVARCHAR (250) NULL,  
  21.     [Designation] NVARCHAR (250) NULL,  
  22.     [Company]     NVARCHAR (250) NULL,  
  23.     [CityId]      INT            NULL,  
  24.     CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([Id] ASC)  
  25. );  
  26.   
  27. GO  
  28.   
  29. CREATE PROCEDURE [dbo].Add_Employee  
  30.     @Name NVARCHAR(250),  
  31.     @Department NVARCHAR(250),  
  32.     @Designation NVARCHAR(250),  
  33.     @Company NVARCHAR(250),  
  34.     @CityId INT  
  35. AS  
  36.     INSERT INTO dbo.Employees (Name,Department,Designation,Company,CityId)  
  37.     VALUES (@Name,@Department,@Designation,@Company,@CityId)         
  38.   
  39. GO  
  40.   
  41. CREATE PROCEDURE [dbo].Delete_Employee  
  42.     @Id INT  
  43. AS  
  44.     DELETE FROM dbo.Employees WHERE Id = @Id   
  45.   
  46. GO  
  47.   
  48. CREATE PROCEDURE [dbo].[Get_AllEmployees]  
  49. AS  
  50.     SELECT emp.*,c.Name CityName FROM dbo.Employees emp LEFT JOIN dbo.City c ON emp.CityId = c.Id ORDER BY emp.Name    
  51.   
  52. GO  
  53.   
  54. CREATE PROCEDURE [dbo].Get_SingleEmployee  
  55.     @Id INT  
  56. AS  
  57.     SELECT emp.*,c.Name CityName from dbo.Employees emp LEFT JOIN dbo.City c ON emp.CityId = c.Id WHERE emp.Id = @Id   
  58.   
  59. GO  
  60.   
  61. CREATE PROCEDURE [dbo].Update_Employee  
  62.     @Id INT,  
  63.     @Name VARCHAR(250),  
  64.     @Department VARCHAR(250),  
  65.     @Designation VARCHAR(250),  
  66.     @Company VARCHAR(250),  
  67.     @CityId INT  
  68. AS  
  69.     UPDATE dbo.Employees SET Name = @Name, Department = @Department, Designation = @Designation, Company = @Company, CityId = @CityId Where Id = @Id   
  70.   
  71. GO  

Create Blazor application in Visual Studio 2019


Open Visual Studio 2019 and choose Blazor server template to create new project. Make sure, you have chosen ASP.NET Core 3.0 template.
 
 
We must install below packages to perform database operations using Dapper.
  • “Dapper” and
  • “Microsoft.Data.SqlClient”
Microsoft.Data.SqlClient is a new package, which supports both .NET and .NET Core framework. You can refer to below Microsoft document for more details about this package.
We can create a new class “SqlConnectionConfiguration” to fetch SQL connection string from appsettings.json configuration file. We will create all C# classes and services under “Data” folder. 
 
SqlConnectionConfiguration.cs
  1. namespace BlazorDapperSPA.Data  
  2. {  
  3.     public class SqlConnectionConfiguration  
  4.     {  
  5.         public SqlConnectionConfiguration(string value) => Value = value;  
  6.         public string Value { get; }  
  7.     }  
  8. }  
We can add connection string in appsettings.json file.
 
appsettings.json
  1. {  
  2.   "Logging": {  
  3.     "LogLevel": {  
  4.       "Default""Information",  
  5.       "Microsoft""Warning",  
  6.       "Microsoft.Hosting.Lifetime""Information"  
  7.     }  
  8.   },  
  9.   "AllowedHosts""*",  
  10.   "ConnectionStrings": {  
  11.     "SqlDbContext""Server=MURUGAN;Database=SarathlalDb;Trusted_Connection=True;MultipleActiveResultSets=true"  
  12.   }  
  13. }  
Create “City” class with below properties.
 
City.cs
  1. namespace BlazorDapperSPA.Data  
  2. {  
  3.     public class City  
  4.     {  
  5.         public int Id { getset; }  
  6.         public string Name { getset; }  
  7.         public string State { getset; }  
  8.     }  
  9. }  
Create “Employee” class with below properties.
 
Employee.cs
  1. namespace BlazorDapperSPA.Data  
  2. {  
  3.     public class Employee  
  4.     {  
  5.         public int Id { getset; }  
  6.         public string Name { getset; }  
  7.         public string Department { getset; }  
  8.         public string Designation { getset; }  
  9.         public string Company { getset; }  
  10.         public string CityId { getset; }  
  11.     }  
  12. }  
We need an “EmployeeModel” class along with Employee class. Because, we need City name in employee list component. We will join Employee table and City table and fetch the City name from City table along with other Employee table details. We can inherit Employee class inside the EmployeeModel class to leverage existing properties from Employee class.
 
EmployeeModel.cs
  1. namespace BlazorDapperSPA.Data  
  2. {  
  3.     public class EmployeeModel : Employee  
  4.     {  
  5.         public string CityName { getset; }  
  6.     }  
  7. }  
We can create an “ICityService” interface and declare below method signatures.
 
ICityService.cs
  1. using System.Collections.Generic;  
  2. using System.Threading.Tasks;  
  3.   
  4. namespace BlazorDapperSPA.Data  
  5. {  
  6.     public interface ICityService  
  7.     {  
  8.         Task<IEnumerable<City>> GetCities();  
  9.         Task<bool> CreateCity(City city);  
  10.         Task<bool> EditCity(int id, City city);  
  11.         Task<City> SingleCity(int id);  
  12.         Task<bool> DeleteCity(int id);  
  13.     }  
  14. }  
We can inherit above interface in new class “CityService” and implement methods.
 
CityService.cs
  1. using Dapper;  
  2. using Microsoft.Data.SqlClient;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Data;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace BlazorDapperSPA.Data  
  9. {  
  10.     public class CityService : ICityService  
  11.     {  
  12.         private readonly SqlConnectionConfiguration _configuration;  
  13.         public CityService(SqlConnectionConfiguration configuration)  
  14.         {  
  15.             _configuration = configuration;  
  16.         }  
  17.         public async Task<bool> CreateCity(City city)  
  18.         {  
  19.             using (var conn = new SqlConnection(_configuration.Value))  
  20.             {  
  21.                 const string query = @"insert into dbo.City (Name,State) values (@Name,@State)";  
  22.                 if (conn.State == ConnectionState.Closed)  
  23.                     conn.Open();  
  24.                 try  
  25.                 {  
  26.                     await conn.ExecuteAsync(query, new { city.Name, city.State }, commandType: CommandType.Text);  
  27.                 }  
  28.                 catch (Exception ex)  
  29.                 {  
  30.                     throw ex;  
  31.                 }  
  32.                 finally  
  33.                 {  
  34.                     if (conn.State == ConnectionState.Open)  
  35.                         conn.Close();  
  36.                 }  
  37.             }  
  38.             return true;  
  39.         }  
  40.         public async Task<bool> DeleteCity(int id)  
  41.         {  
  42.             using (var conn = new SqlConnection(_configuration.Value))  
  43.             {  
  44.                 const string query = @"delete from dbo.City where Id=@Id";  
  45.                 if (conn.State == ConnectionState.Closed)  
  46.                     conn.Open();  
  47.                 try  
  48.                 {  
  49.                     await conn.ExecuteAsync(query, new { id }, commandType: CommandType.Text);  
  50.                 }  
  51.                 catch (Exception ex)  
  52.                 {  
  53.                     throw ex;  
  54.                 }  
  55.                 finally  
  56.                 {  
  57.                     if (conn.State == ConnectionState.Open)  
  58.                         conn.Close();  
  59.                 }  
  60.             }  
  61.             return true;  
  62.         }  
  63.         public async Task<bool> EditCity(int id, City city)  
  64.         {  
  65.             using (var conn = new SqlConnection(_configuration.Value))  
  66.             {  
  67.                 const string query = @"update dbo.City set Name = @Name, State = @State where Id=@Id";  
  68.                 if (conn.State == ConnectionState.Closed)  
  69.                     conn.Open();  
  70.                 try  
  71.                 {  
  72.                     await conn.ExecuteAsync(query, new { city.Name, city.State, id }, commandType: CommandType.Text);  
  73.                 }  
  74.                 catch (Exception ex)  
  75.                 {  
  76.                     throw ex;  
  77.                 }  
  78.                 finally  
  79.                 {  
  80.                     if (conn.State == ConnectionState.Open)  
  81.                         conn.Close();  
  82.                 }  
  83.             }  
  84.             return true;  
  85.         }  
  86.         public async Task<IEnumerable<City>> GetCities()  
  87.         {  
  88.             IEnumerable<City> cities;  
  89.             using (var conn = new SqlConnection(_configuration.Value))  
  90.             {  
  91.                 const string query = @"select * from dbo.City";  
  92.   
  93.                 if (conn.State == ConnectionState.Closed)  
  94.                     conn.Open();  
  95.                 try  
  96.                 {  
  97.                     cities = await conn.QueryAsync<City>(query);  
  98.   
  99.                 }  
  100.                 catch (Exception ex)  
  101.                 {  
  102.                     throw ex;  
  103.                 }  
  104.                 finally  
  105.                 {  
  106.                     if (conn.State == ConnectionState.Open)  
  107.                         conn.Close();  
  108.                 }  
  109.   
  110.             }  
  111.             return cities;  
  112.         }  
  113.         public async Task<City> SingleCity(int id)  
  114.         {  
  115.             City city = new City();  
  116.   
  117.             using (var conn = new SqlConnection(_configuration.Value))  
  118.             {  
  119.                 const string query = @"select * from dbo.City where Id =@Id";  
  120.   
  121.                 if (conn.State == ConnectionState.Closed)  
  122.                     conn.Open();  
  123.                 try  
  124.                 {  
  125.                     city = await conn.QueryFirstOrDefaultAsync<City>(query, new { id }, commandType: CommandType.Text);  
  126.                 }  
  127.                 catch (Exception ex)  
  128.                 {  
  129.                     throw ex;  
  130.                 }  
  131.                 finally  
  132.                 {  
  133.                     if (conn.State == ConnectionState.Open)  
  134.                         conn.Close();  
  135.                 }  
  136.             }  
  137.             return city;  
  138.         }  
  139.     }  
  140. }  
We have added all logic for City entity in above class. We have used native SQL queries with Dapper.
 
Create an “IEmployeeService” interface and declare below methods.
 
IEmployeeService.cs
  1. using System.Collections.Generic;  
  2. using System.Threading.Tasks;  
  3.   
  4. namespace BlazorDapperSPA.Data  
  5. {  
  6.     public interface IEmployeeService  
  7.     {  
  8.         Task<IEnumerable<EmployeeModel>> GetEmployees();  
  9.         Task<bool> CreateEmployee(Employee employee);  
  10.         Task<bool> EditEmployee(int id, EmployeeModel employee);  
  11.         Task<EmployeeModel> SingleEmployee(int id);  
  12.         Task<bool> DeleteEmployee(int id);  
  13.     }  
  14. }  
We can inherit above interface in new class “EmployeeService” and implement all methods.
 
EmployeeService.cs
  1. using Dapper;  
  2. using Microsoft.Data.SqlClient;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Data;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace BlazorDapperSPA.Data  
  9. {  
  10.     public class EmployeeService : IEmployeeService  
  11.     {  
  12.         private readonly SqlConnectionConfiguration _configuration;  
  13.         public EmployeeService(SqlConnectionConfiguration configuration)  
  14.         {  
  15.             _configuration = configuration;  
  16.         }  
  17.         public async Task<bool> CreateEmployee(Employee employee)  
  18.         {  
  19.             var parameters = new DynamicParameters();  
  20.             parameters.Add("Name", employee.Name, DbType.String);  
  21.             parameters.Add("Department", employee.Department, DbType.String);  
  22.             parameters.Add("Designation", employee.Designation, DbType.String);  
  23.             parameters.Add("Company", employee.Company, DbType.String);  
  24.             parameters.Add("CityId", employee.CityId, DbType.Int32);  
  25.   
  26.             using (var conn = new SqlConnection(_configuration.Value))  
  27.             {  
  28.                 if (conn.State == ConnectionState.Closed)  
  29.                     conn.Open();  
  30.                 try  
  31.                 {  
  32.                     await conn.ExecuteAsync("Add_Employee", parameters, commandType: CommandType.StoredProcedure);  
  33.                 }  
  34.                 catch (Exception ex)  
  35.                 {  
  36.                     throw ex;  
  37.                 }  
  38.                 finally  
  39.                 {  
  40.                     if (conn.State == ConnectionState.Open)  
  41.                         conn.Close();  
  42.                 }  
  43.             }  
  44.             return true;  
  45.         }  
  46.         public async Task<bool> DeleteEmployee(int id)  
  47.         {  
  48.             var parameters = new DynamicParameters();  
  49.             parameters.Add("Id", id, DbType.Int32);  
  50.   
  51.             using (var conn = new SqlConnection(_configuration.Value))  
  52.             {  
  53.   
  54.                 if (conn.State == ConnectionState.Closed)  
  55.                     conn.Open();  
  56.                 try  
  57.                 {  
  58.                     await conn.ExecuteAsync("Delete_Employee", parameters, commandType: CommandType.StoredProcedure);  
  59.                 }  
  60.                 catch (Exception ex)  
  61.                 {  
  62.                     throw ex;  
  63.                 }  
  64.                 finally  
  65.                 {  
  66.                     if (conn.State == ConnectionState.Open)  
  67.                         conn.Close();  
  68.                 }  
  69.             }  
  70.             return true;  
  71.         }  
  72.         public async Task<bool> EditEmployee(int id, EmployeeModel employee)  
  73.         {  
  74.             var parameters = new DynamicParameters();  
  75.             parameters.Add("Id", id, DbType.Int32);  
  76.             parameters.Add("Name", employee.Name, DbType.String);  
  77.             parameters.Add("Department", employee.Department, DbType.String);  
  78.             parameters.Add("Designation", employee.Designation, DbType.String);  
  79.             parameters.Add("Company", employee.Company, DbType.String);  
  80.             parameters.Add("CityId", employee.CityId, DbType.Int32);  
  81.   
  82.             using (var conn = new SqlConnection(_configuration.Value))  
  83.             {  
  84.   
  85.                 if (conn.State == ConnectionState.Closed)  
  86.                     conn.Open();  
  87.                 try  
  88.                 {  
  89.                     await conn.ExecuteAsync("Update_Employee", parameters, commandType: CommandType.StoredProcedure);  
  90.                 }  
  91.                 catch (Exception ex)  
  92.                 {  
  93.                     throw ex;  
  94.                 }  
  95.                 finally  
  96.                 {  
  97.                     if (conn.State == ConnectionState.Open)  
  98.                         conn.Close();  
  99.                 }  
  100.             }  
  101.             return true;  
  102.         }  
  103.         public async Task<IEnumerable<EmployeeModel>> GetEmployees()  
  104.         {  
  105.             IEnumerable<EmployeeModel> employees;  
  106.   
  107.             using (var conn = new SqlConnection(_configuration.Value))  
  108.             {  
  109.                 if (conn.State == ConnectionState.Closed)  
  110.                     conn.Open();  
  111.                 try  
  112.                 {  
  113.                     employees = await conn.QueryAsync<EmployeeModel>("Get_AllEmployees", commandType: CommandType.StoredProcedure);  
  114.                 }  
  115.                 catch (Exception ex)  
  116.                 {  
  117.                     throw ex;  
  118.                 }  
  119.                 finally  
  120.                 {  
  121.                     if (conn.State == ConnectionState.Open)  
  122.                         conn.Close();  
  123.                 }  
  124.             }  
  125.             return employees;  
  126.         }  
  127.         public async Task<EmployeeModel> SingleEmployee(int id)  
  128.         {  
  129.             var parameters = new DynamicParameters();  
  130.             parameters.Add("Id", id, DbType.Int32);  
  131.   
  132.             EmployeeModel employee = new EmployeeModel();  
  133.   
  134.             using (var conn = new SqlConnection(_configuration.Value))  
  135.             {  
  136.   
  137.                 if (conn.State == ConnectionState.Closed)  
  138.                     conn.Open();  
  139.                 try  
  140.                 {  
  141.                     employee = await conn.QueryFirstOrDefaultAsync<EmployeeModel>("Get_SingleEmployee", parameters, commandType: CommandType.StoredProcedure);  
  142.                 }  
  143.                 catch (Exception ex)  
  144.                 {  
  145.                     throw ex;  
  146.                 }  
  147.                 finally  
  148.                 {  
  149.                     if (conn.State == ConnectionState.Open)  
  150.                         conn.Close();  
  151.                 }  
  152.             }  
  153.             return employee;  
  154.         }  
  155.     }  
  156. }  
We have added all the logic for Employee entity in above class. We have used stored procedures for CURD operations with Dapper.
 
We can register the ICityService and IEmployeeService inside the “ConfigureServices” of Startup class.
 
We have enabled the detailed error for Blazor server application also.
 
ConfigureServices in Startup.cs class  
  1.             services.AddRazorPages();  
  2.             services.AddServerSideBlazor();  
  3.             services.AddSingleton<WeatherForecastService>();  
  4.   
  5.             services.AddScoped<IEmployeeService, EmployeeService>();  
  6.             services.AddScoped<ICityService, CityService>();  
  7.   
  8.             var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDbContext"));  
  9.             services.AddSingleton(sqlConnectionConfiguration);  
  10.   
  11.             services.AddServerSideBlazor(o => o.DetailedErrors = true);  

Create Blazor components for CRUD operations


We can create all components for City and Employee inside the “Pages” folder
 
ListCities.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/listcities"  
  4. @inject ICityService CityService  
  5.   
  6. <h2>City Details</h2>  
  7. <p>  
  8.     <a href="/addcity">Create New City</a>  
  9. </p>  
  10.   
  11. @if (cities == null)  
  12. {  
  13.     <img src="./basicloader.gif" />  
  14. }  
  15. else  
  16. {  
  17.     <table class='table'>  
  18.         <thead>  
  19.             <tr>  
  20.                 <th>Name</th>  
  21.                 <th>State</th>  
  22.             </tr>  
  23.         </thead>  
  24.         <tbody>  
  25.             @foreach (var city in cities)  
  26.             {  
  27.                 <tr>  
  28.                     <td>@city.Name</td>  
  29.                     <td>@city.State</td>  
  30.                     <td>  
  31.                         <a href='/editcity/@city.Id'>Edit</a>  
  32.                         <a href='/deletecity/@city.Id'>Delete</a>  
  33.                     </td>  
  34.                 </tr>  
  35.   
  36.             }  
  37.         </tbody>  
  38.     </table>  
  39. }  
  40.   
  41. @code {  
  42.     IEnumerable<City> cities;  
  43.   
  44.     protected override async Task OnInitializedAsync()  
  45.     {  
  46.         cities = await CityService.GetCities();  
  47.     }  
  48. }  
AddCity.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/addcity"  
  4. @inject NavigationManager NavigationManager  
  5. @inject ICityService CityService  
  6.   
  7. <h2>Create City</h2>  
  8. <hr />  
  9. <form>  
  10.     <div class="row">  
  11.         <div class="col-md-8">  
  12.             <div class="form-group">  
  13.                 <label for="Name" class="control-label">Name</label>  
  14.                 <input for="Name" class="form-control" @bind="@city.Name" />  
  15.             </div>  
  16.             <div class="form-group">  
  17.                 <label for="State" class="control-label">State</label>  
  18.                 <input for="State" class="form-control" @bind="@city.State" />  
  19.             </div>  
  20.         </div>  
  21.     </div>  
  22.     <div class="row">  
  23.         <div class="col-md-4">  
  24.             <div class="form-group">  
  25.                 <input type="button" class="btn btn-primary" @onclick="@CreateCity" value="Save" />  
  26.                 <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
  27.             </div>  
  28.         </div>  
  29.     </div>  
  30. </form>  
  31.   
  32. @code {  
  33.   
  34.     City city = new City();  
  35.   
  36.     protected async Task CreateCity()  
  37.     {  
  38.         await CityService.CreateCity(city);  
  39.         NavigationManager.NavigateTo("listcities");  
  40.     }  
  41.   
  42.     void Cancel()  
  43.     {  
  44.         NavigationManager.NavigateTo("listcities");  
  45.     }  
  46. }     
EditCity.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/editcity/{id:int}"  
  4. @inject NavigationManager NavigationManager  
  5. @inject ICityService CityService  
  6.   
  7. <h2>Edit City</h2>  
  8. <hr />  
  9. <form>  
  10.     <div class="row">  
  11.         <div class="col-md-8">  
  12.             <div class="form-group">  
  13.                 <label for="Name" class="control-label">Name</label>  
  14.                 <input for="Name" class="form-control" @bind="@city.Name" />  
  15.             </div>  
  16.             <div class="form-group">  
  17.                 <label for="State" class="control-label">State</label>  
  18.                 <input for="State" class="form-control" @bind="@city.State" />  
  19.             </div>  
  20.         </div>  
  21.     </div>  
  22.     <div class="row">  
  23.         <div class="form-group">  
  24.             <input type="button" class="btn btn-primary" @onclick="@UpdateCity" value="Update" />  
  25.             <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
  26.         </div>  
  27.     </div>  
  28. </form>  
  29.   
  30. @code {  
  31.   
  32.     [Parameter]  
  33.     public int id { getset; }  
  34.   
  35.     City city = new City();  
  36.   
  37.     protected override async Task OnInitializedAsync()  
  38.     {  
  39.         city = await CityService.SingleCity(id);  
  40.     }  
  41.   
  42.     protected async Task UpdateCity()  
  43.     {  
  44.         await CityService.EditCity(id, city);  
  45.         NavigationManager.NavigateTo("listcities");  
  46.     }  
  47.   
  48.     void Cancel()  
  49.     {  
  50.         NavigationManager.NavigateTo("listcities");  
  51.     }  
  52. }     
DeleteCity.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/deletecity/{id:int}"  
  4. @inject NavigationManager NavigationManager  
  5. @inject ICityService CityService  
  6.   
  7. <h2>Confirm Delete</h2>  
  8. <p>Are you sure you want to delete this City with Id :<b> @id</b></p>  
  9. <br />  
  10. <div class="col-md-4">  
  11.     <table class="table">  
  12.         <tr>  
  13.             <td>Name</td>  
  14.             <td>@city.Name</td>  
  15.         </tr>  
  16.         <tr>  
  17.             <td>State</td>  
  18.             <td>@city.State</td>  
  19.         </tr>  
  20.     </table>  
  21.     <div class="form-group">  
  22.         <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />  
  23.         <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />  
  24.     </div>  
  25. </div>  
  26.   
  27. @code {  
  28.   
  29.     [Parameter]  
  30.     public int id { getset; }  
  31.     City city = new City();  
  32.   
  33.     protected override async Task OnInitializedAsync()  
  34.     {  
  35.         city = await CityService.SingleCity(id);  
  36.     }  
  37.   
  38.     protected async Task Delete()  
  39.     {  
  40.         await CityService.DeleteCity(id);  
  41.         NavigationManager.NavigateTo("listcities");  
  42.     }  
  43.   
  44.     void Cancel()  
  45.     {  
  46.         NavigationManager.NavigateTo("listcities");  
  47.     }  
  48. }     
We can create component files for Employee CRUD actions
 
ListEmployees.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/listemployees"  
  4. @inject IEmployeeService EmployeeService  
  5.   
  6. <h2>Employee Details</h2>  
  7. <p>  
  8.     <a href="/addemployee">Create New Employee</a>  
  9. </p>  
  10. @if (employees == null)  
  11. {  
  12.     <img src="./basicloader.gif" />  
  13. }  
  14. else  
  15. {  
  16.     <table class='table'>  
  17.         <thead>  
  18.             <tr>  
  19.                 <th>Name</th>  
  20.                 <th>Department</th>  
  21.                 <th>Designation</th>  
  22.                 <th>Company</th>  
  23.                 <th>City</th>  
  24.             </tr>  
  25.         </thead>  
  26.         <tbody>  
  27.             @foreach (var employee in employees)  
  28.             {  
  29.                 <tr>  
  30.                     <td>@employee.Name</td>  
  31.                     <td>@employee.Department</td>  
  32.                     <td>@employee.Designation</td>  
  33.                     <td>@employee.Company</td>  
  34.                     <td>@employee.CityName</td>  
  35.                     <td>  
  36.                         <a href='/editemployee/@employee.Id'>Edit</a>  
  37.                         <a href='/deleteemployee/@employee.Id'>Delete</a>  
  38.                     </td>  
  39.                 </tr>  
  40.   
  41.             }  
  42.         </tbody>  
  43.     </table>  
  44. }  
  45.   
  46. @code {  
  47.     IEnumerable<EmployeeModel> employees;  
  48.   
  49.     protected override async Task OnInitializedAsync()  
  50.     {  
  51.         employees = await EmployeeService.GetEmployees();  
  52.     }  
  53. }     
AddEmployee.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/addemployee"  
  4. @inject NavigationManager NavigationManager  
  5. @inject IEmployeeService EmployeeService  
  6. @inject ICityService CityService  
  7.   
  8. <h2>Create Employee</h2>  
  9. <hr />  
  10. <form>  
  11.     <div class="row">  
  12.         <div class="col-md-8">  
  13.             <div class="form-group">  
  14.                 <label for="Name" class="control-label">Name</label>  
  15.                 <input for="Name" class="form-control" @bind="@employee.Name" />  
  16.             </div>  
  17.             <div class="form-group">  
  18.                 <label for="Department" class="control-label">Department</label>  
  19.                 <input for="Department" class="form-control" @bind="@employee.Department" />  
  20.             </div>  
  21.             <div class="form-group">  
  22.                 <label for="Designation" class="control-label">Designation</label>  
  23.                 <input for="Designation" class="form-control" @bind="@employee.Designation" />  
  24.             </div>  
  25.             <div class="form-group">  
  26.                 <label for="Company" class="control-label">Company</label>  
  27.                 <input for="Company" class="form-control" @bind="@employee.Company" />  
  28.             </div>  
  29.             <div class="form-group">  
  30.                 <label for="City" class="control-label">City</label>  
  31.                 <select for="City" class="form-control" @bind="@employee.CityId">  
  32.                     <option value="">-- Select City --</option>  
  33.                     @foreach (var city in cities)  
  34.                     {  
  35.                         <option value="@city.Id">@city.Name</option>  
  36.   
  37.                     }  
  38.                 </select>  
  39.             </div>  
  40.         </div>  
  41.     </div>  
  42.     <div class="row">  
  43.         <div class="col-md-4">  
  44.             <div class="form-group">  
  45.                 <input type="button" class="btn btn-primary" @onclick="@CreateEmployee" value="Save" />  
  46.                 <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
  47.             </div>  
  48.         </div>  
  49.     </div>  
  50. </form>  
  51.   
  52. @code {  
  53.   
  54.     Employee employee = new Employee();  
  55.     IEnumerable<City> cities = new List<City>();  
  56.   
  57.     protected override async Task OnInitializedAsync()  
  58.     {  
  59.         cities = await CityService.GetCities();  
  60.     }  
  61.   
  62.     protected async Task CreateEmployee()  
  63.     {  
  64.         await EmployeeService.CreateEmployee(employee);  
  65.         NavigationManager.NavigateTo("listemployees");  
  66.     }  
  67.   
  68.     void Cancel()  
  69.     {  
  70.         NavigationManager.NavigateTo("listemployees");  
  71.     }  
  72. }     
EditEmployee.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/editemployee/{id:int}"  
  4. @inject NavigationManager NavigationManager  
  5. @inject IEmployeeService EmployeeService  
  6. @inject ICityService CityService  
  7.   
  8. <h2>Edit Employee</h2>  
  9. <hr />  
  10. <form>  
  11.     <div class="row">  
  12.         <div class="col-md-8">  
  13.             <div class="form-group">  
  14.                 <label for="Name" class="control-label">Name</label>  
  15.                 <input for="Name" class="form-control" @bind="@employee.Name" />  
  16.             </div>  
  17.             <div class="form-group">  
  18.                 <label for="Department" class="control-label">Department</label>  
  19.                 <input for="Department" class="form-control" @bind="@employee.Department" />  
  20.             </div>  
  21.             <div class="form-group">  
  22.                 <label for="Designation" class="control-label">Designation</label>  
  23.                 <input for="Designation" class="form-control" @bind="@employee.Designation" />  
  24.             </div>  
  25.             <div class="form-group">  
  26.                 <label for="Company" class="control-label">Company</label>  
  27.                 <input for="Company" class="form-control" @bind="@employee.Company" />  
  28.             </div>  
  29.             <div class="form-group">  
  30.                 <label for="City" class="control-label">City</label>  
  31.                 <select for="City" class="form-control" @bind="@employee.CityId">  
  32.                     <option value="">-- Select City --</option>  
  33.                     @foreach (var city in cities)  
  34.                     {  
  35.                         <option value="@city.Id">@city.Name</option>  
  36.                     }  
  37.                 </select>  
  38.             </div>  
  39.         </div>  
  40.     </div>  
  41.     <div class="row">  
  42.         <div class="form-group">  
  43.             <input type="button" class="btn btn-primary" @onclick="@UpdateEmployee" value="Update" />  
  44.             <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
  45.         </div>  
  46.     </div>  
  47. </form>  
  48.   
  49. @code {  
  50.   
  51.     [Parameter]  
  52.     public int id { getset; }  
  53.   
  54.     EmployeeModel employee = new EmployeeModel();  
  55.     IEnumerable<City> cities = new List<City>();  
  56.   
  57.     protected override async Task OnInitializedAsync()  
  58.     {  
  59.         cities = await CityService.GetCities();  
  60.         employee = await EmployeeService.SingleEmployee(id);  
  61.     }  
  62.   
  63.     protected async Task UpdateEmployee()  
  64.     {  
  65.         await EmployeeService.EditEmployee(id, employee);  
  66.         NavigationManager.NavigateTo("listemployees");  
  67.     }  
  68.   
  69.     void Cancel()  
  70.     {  
  71.         NavigationManager.NavigateTo("listemployees");  
  72.     }  
  73. }     
DeleteEmployee.razor
  1. @using BlazorDapperSPA.Data  
  2.   
  3. @page "/deleteemployee/{id:int}"  
  4. @inject NavigationManager NavigationManager  
  5. @inject IEmployeeService EmployeeService  
  6.   
  7. <h2>Confirm Delete</h2>  
  8. <p>Are you sure you want to delete this Employee with Id :<b> @id</b></p>  
  9. <br />  
  10. <div class="col-md-4">  
  11.     <table class="table">  
  12.         <tr>  
  13.             <td>Name</td>  
  14.             <td>@employee.Name</td>  
  15.         </tr>  
  16.         <tr>  
  17.             <td>Department</td>  
  18.             <td>@employee.Department</td>  
  19.         </tr>  
  20.         <tr>  
  21.             <td>Designation</td>  
  22.             <td>@employee.Designation</td>  
  23.         </tr>  
  24.         <tr>  
  25.             <td>Company</td>  
  26.             <td>@employee.Company</td>  
  27.         </tr>  
  28.         <tr>  
  29.             <td>City</td>  
  30.             <td>@employee.CityName</td>  
  31.         </tr>  
  32.     </table>  
  33.     <div class="form-group">  
  34.         <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />  
  35.         <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />  
  36.     </div>  
  37. </div>  
  38.   
  39. @code {  
  40.   
  41.     [Parameter]  
  42.     public int id { getset; }  
  43.     EmployeeModel employee = new EmployeeModel();  
  44.   
  45.     protected override async Task OnInitializedAsync()  
  46.     {  
  47.         employee = await EmployeeService.SingleEmployee(id);  
  48.     }  
  49.   
  50.     protected async Task Delete()  
  51.     {  
  52.         await EmployeeService.DeleteEmployee(id);  
  53.         NavigationManager.NavigateTo("listemployees");  
  54.     }  
  55.   
  56.     void Cancel()  
  57.     {  
  58.         NavigationManager.NavigateTo("listemployees");  
  59.     }  
  60. }     
We can modify the “NavMenu” shared component with routing to City and Employee details.
 
NavMenu.razor
  1. <div class="top-row pl-4 navbar navbar-dark">  
  2.     <a class="navbar-brand" href="">Blazor with Dapper</a>  
  3.     <button class="navbar-toggler" @onclick="ToggleNavMenu">  
  4.         <span class="navbar-toggler-icon"></span>  
  5.     </button>  
  6. </div>  
  7.   
  8. <div class="@NavMenuCssClass" @onclick="ToggleNavMenu">  
  9.     <ul class="nav flex-column">  
  10.         <li class="nav-item px-3">  
  11.             <NavLink class="nav-link" href="" Match="NavLinkMatch.All">  
  12.                 <span class="oi oi-home" aria-hidden="true"></span> Home  
  13.             </NavLink>  
  14.         </li>  
  15.         <li class="nav-item px-3">  
  16.             <NavLink class="nav-link" href="listcities">  
  17.                 <span class="oi oi-list-rich" aria-hidden="true"></span> City data  
  18.             </NavLink>  
  19.         </li>  
  20.         <li class="nav-item px-3">  
  21.             <NavLink class="nav-link" href="listemployees">  
  22.                 <span class="oi oi-list-rich" aria-hidden="true"></span> Employee data  
  23.             </NavLink>  
  24.         </li>  
  25.     </ul>  
  26. </div>  
  27.   
  28. @code {  
  29.     bool collapseNavMenu = true;  
  30.   
  31.     string NavMenuCssClass => collapseNavMenu ? "collapse" : null;  
  32.   
  33.     void ToggleNavMenu()  
  34.     {  
  35.         collapseNavMenu = !collapseNavMenu;  
  36.     }  
  37. }  
We have completed the entire coding. We can run the application.
 
 
 
We can create a new City by clicking “City data” menu link and click “Create New City” hyper link.
 
 
 
After saving the City details, we can click “Employee data” menu link and click “Create New Employee” hyper link to create new employee data.
 
 
 
You can notice that, already saved city data is shown in the dropdown.
 
You can see the saved employee data in a grid with Edit and Delete hyper link.
 
 
You can perform other CRUD actions like Edit or Delete as well.

Conclusion


In this post, we have seen how to create a Blazor server application with two entities City and Employee. We have used Dapper ORM for database operations with SQL server. In City entity, we have used native SQL commands to retrieve and store data. While in Employee entity, we have used stored procedures to perform database operations. You can easily perform complex database operations with Dapper efficiently. Please give your valuable feedback about this post, so that I can try to improve the quality in upcoming posts.