Create A Blazor Server SPA With Dapper

Introduction

Blazor is a new framework built by Microsoft for creating interactive client-side web UI with a .NET codebase. We can write both client-side and server-side code in C#.NET itself. I have already written three articles about the Blazor server on C# Corner. Please refer to the below articles for more basics about the Blazor framework.

Dapper is a micro ORM (Object Relational Mapper) that helps to map the native query output to a domain class. It is a high-performance data access system built by the StackOverflow team and released as open source. If your project prefers writing stored procedures or writing raw SQL queries instead of using 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 a database and get the result mapped to the 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 below to create a new database, tables, and stored procedures. As I told you earlier, we will use these stored procedures for fetching and storing employee data using Dapper.

USE master;
CREATE DATABASE SarathlalDb;
GO
USE SarathlalDb;
CREATE TABLE [dbo].[City] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (250) NULL,
    [State] NVARCHAR (250) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE TABLE [dbo].[Employees] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (250) NULL,
    [Department] NVARCHAR (250) NULL,
    [Designation] NVARCHAR (250) NULL,
    [Company] NVARCHAR (250) NULL,
    [CityId] INT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE PROCEDURE [dbo].Add_Employee
    @Name NVARCHAR(250),
    @Department NVARCHAR(250),
    @Designation NVARCHAR(250),
    @Company NVARCHAR(250),
    @CityId INT
AS
    INSERT INTO dbo.Employees (Name, Department, Designation, Company, CityId)
    VALUES (@Name, @Department, @Designation, @Company, @CityId)
GO
CREATE PROCEDURE [dbo].Delete_Employee
    @Id INT
AS
    DELETE FROM dbo.Employees WHERE Id = @Id
GO
CREATE PROCEDURE [dbo].[Get_AllEmployees]
AS
    SELECT emp.*, c.Name CityName FROM dbo.Employees emp LEFT JOIN dbo.City c ON emp.CityId = c.Id ORDER BY emp.Name
GO
CREATE PROCEDURE [dbo].Get_SingleEmployee
    @Id INT
AS
    SELECT emp.*, c.Name CityName FROM dbo.Employees emp LEFT JOIN dbo.City c ON emp.CityId = c.Id WHERE emp.Id = @Id
GO
CREATE PROCEDURE [dbo].Update_Employee
    @Id INT,
    @Name VARCHAR(250),
    @Department VARCHAR(250),
    @Designation VARCHAR(250),
    @Company VARCHAR(250),
    @CityId INT
AS
    UPDATE dbo.Employees SET Name = @Name, Department = @Department, Designation = @Designation, Company = @Company, CityId = @CityId WHERE Id = @Id
GO

Create Blazor application in Visual Studio 2019

Open Visual Studio 2019 and choose the Blazor server template to create a new project. Make sure, you have chosen ASP.NET Core 3.0 template.

Create a new Blazor app

We must install the 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 below for more details about this package.

Introducing the new Microsoft.Data.SqlClient

We can create a new class “SqlConnectionConfiguration” to fetch the SQL connection string from the appsettings.json configuration file. We will create all C# classes and services under the “Data” folder.

SqlConnectionConfiguration.cs

namespace BlazorDapperSPA.Data
{
    public class SqlConnectionConfiguration
    {
        public SqlConnectionConfiguration(string value) => Value = value;
        public string Value { get; }
    }
}

We can add a connection string in the appsettings.json file.

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "SqlDbContext": "Server=MURUGAN;Database=SarathlalDb;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

Create a “City” class with the below properties.

City. cs

namespace BlazorDapperSPA.Data
{
    public class City
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string State { get; set; }
    }
}

Create an “Employee” class with the below properties.

Employee.cs

namespace BlazorDapperSPA.Data
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Department { get; set; }
        public string Designation { get; set; }
        public string Company { get; set; }
        public string CityId { get; set; }
    }
}

We need an “EmployeeModel” class along with an Employee class. Because we need the City name in the employee list component. We will join the Employee table and City table and fetch the City name from the City table along with other Employee table details. We can inherit the Employee class inside the EmployeeModel class to leverage existing properties from the Employee class.

EmployeeModel.cs

namespace BlazorDapperSPA.Data  
{  
    public class EmployeeModel : Employee  
    {  
        public string CityName { get; set; }  
    }  
}

We can create an “ICityService” interface and declare the below method signatures.

ICityService.cs

using System.Collections.Generic;  
using System.Threading.Tasks;  
namespace BlazorDapperSPA.Data  
{  
    public interface ICityService  
    {  
        Task<IEnumerable<City>> GetCities();  
        Task<bool> CreateCity(City city);  
        Task<bool> EditCity(int id, City city);  
        Task<City> SingleCity(int id);  
        Task<bool> DeleteCity(int id);  
    }  
}

We can inherit the above interface in the new class “CityService” and implement methods.

CityService.cs

using Dapper;  
using Microsoft.Data.SqlClient;  
using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Threading.Tasks;    
namespace BlazorDapperSPA.Data  
{  
    public class CityService : ICityService  
    {  
        private readonly SqlConnectionConfiguration _configuration;  
        public CityService(SqlConnectionConfiguration configuration)  
        {  
            _configuration = configuration;  
        }  
        public async Task<bool> CreateCity(City city)  
        {  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                const string query = @"insert into dbo.City (Name,State) values (@Name,@State)";  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    await conn.ExecuteAsync(query, new { city.Name, city.State }, commandType: CommandType.Text);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return true;  
        }  
        public async Task<bool> DeleteCity(int id)  
        {  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                const string query = @"delete from dbo.City where Id=@Id";  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    await conn.ExecuteAsync(query, new { id }, commandType: CommandType.Text);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return true;  
        }  
        public async Task<bool> EditCity(int id, City city)  
        {  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                const string query = @"update dbo.City set Name = @Name, State = @State where Id=@Id";  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    await conn.ExecuteAsync(query, new { city.Name, city.State, id }, commandType: CommandType.Text);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return true;  
        }  
        public async Task<IEnumerable<City>> GetCities()  
        {  
            IEnumerable<City> cities;  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                const string query = @"select * from dbo.City";  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    cities = await conn.QueryAsync<City>(query);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return cities;  
        }  
        public async Task<City> SingleCity(int id)  
        {  
            City city = new City();  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                const string query = @"select * from dbo.City where Id =@Id";  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    city = await conn.QueryFirstOrDefaultAsync<City>(query, new { id }, commandType: CommandType.Text);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return city;  
        }  
    }  
}

We have added all logic for the City entity in the above class. We have used native SQL queries with Dapper.

Create an “IEmployeeService” interface and declare the below methods.

IEmployeeService.cs

using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorDapperSPA.Data
{
    public interface IEmployeeService
    {
        Task<IEnumerable<EmployeeModel>> GetEmployees();
        Task<bool> CreateEmployee(Employee employee);
        Task<bool> EditEmployee(int id, EmployeeModel employee);
        Task<EmployeeModel> SingleEmployee(int id);
        Task<bool> DeleteEmployee(int id);
    }
}

We can inherit the above interface in the new class “EmployeeService” and implement all methods.

EmployeeService.cs

using Dapper;  
using Microsoft.Data.SqlClient;  
using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Threading.Tasks;    
namespace BlazorDapperSPA.Data  
{  
    public class EmployeeService : IEmployeeService  
    {  
        private readonly SqlConnectionConfiguration _configuration;  
        public EmployeeService(SqlConnectionConfiguration configuration)  
        {  
            _configuration = configuration;  
        }  
        public async Task<bool> CreateEmployee(Employee employee)  
        {  
            var parameters = new DynamicParameters();  
            parameters.Add("Name", employee.Name, DbType.String);  
            parameters.Add("Department", employee.Department, DbType.String);  
            parameters.Add("Designation", employee.Designation, DbType.String);  
            parameters.Add("Company", employee.Company, DbType.String);  
            parameters.Add("CityId", employee.CityId, DbType.Int32);  
  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    await conn.ExecuteAsync("Add_Employee", parameters, commandType: CommandType.StoredProcedure);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return true;  
        }  
        public async Task<bool> DeleteEmployee(int id)  
        {  
            var parameters = new DynamicParameters();  
            parameters.Add("Id", id, DbType.Int32);  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    await conn.ExecuteAsync("Delete_Employee", parameters, commandType: CommandType.StoredProcedure);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return true;  
        }  
        public async Task<bool> EditEmployee(int id, EmployeeModel employee)  
        {  
            var parameters = new DynamicParameters();  
            parameters.Add("Id", id, DbType.Int32);  
            parameters.Add("Name", employee.Name, DbType.String);  
            parameters.Add("Department", employee.Department, DbType.String);  
            parameters.Add("Designation", employee.Designation, DbType.String);  
            parameters.Add("Company", employee.Company, DbType.String);  
            parameters.Add("CityId", employee.CityId, DbType.Int32);  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    await conn.ExecuteAsync("Update_Employee", parameters, commandType: CommandType.StoredProcedure);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return true;  
        }  
        public async Task<IEnumerable<EmployeeModel>> GetEmployees()  
        {  
            IEnumerable<EmployeeModel> employees;  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    employees = await conn.QueryAsync<EmployeeModel>("Get_AllEmployees", commandType: CommandType.StoredProcedure);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return employees;  
        }  
        public async Task<EmployeeModel> SingleEmployee(int id)  
        {  
            var parameters = new DynamicParameters();  
            parameters.Add("Id", id, DbType.Int32);  
            EmployeeModel employee = new EmployeeModel();  
            using (var conn = new SqlConnection(_configuration.Value))  
            {  
                if (conn.State == ConnectionState.Closed)  
                    conn.Open();  
                try  
                {  
                    employee = await conn.QueryFirstOrDefaultAsync<EmployeeModel>("Get_SingleEmployee", parameters, commandType: CommandType.StoredProcedure);  
                }  
                catch (Exception ex)  
                {  
                    throw ex;  
                }  
                finally  
                {  
                    if (conn.State == ConnectionState.Open)  
                        conn.Close();  
                }  
            }  
            return employee;  
        }  
    }  
}

We have added all the logic for the Employee entity in the above class. We have used stored procedures for CURD operations with Dapper.

We can register the ICityService and IEmployeeService inside the “ConfigureServices” of the Startup class.

We have enabled the detailed error for the Blazor server application also.

ConfigureServices in Startup.cs class

services.AddRazorPages();
services.AddServerSideBlazor();
services.AddSingleton<WeatherForecastService>();
services.AddScoped<IEmployeeService, EmployeeService>();
services.AddScoped<ICityService, CityService>();
var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDbContext"));
services.AddSingleton(sqlConnectionConfiguration);
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

@using BlazorDapperSPA.Data
@page "/listcities"
@inject ICityService CityService
<h2>City Details</h2>
<p>
    <a href="/addcity">Create New City</a>
</p>
@if (cities == null)
{
    <img src="./basicloader.gif" />
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>State</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var city in cities)
            {
                <tr>
                    <td>@city.Name</td>
                    <td>@city.State</td>
                    <td>
                        <a href='/editcity/@city.Id'>Edit</a>
                        <a href='/deletecity/@city.Id'>Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
}
@code {
    IEnumerable<City> cities;
    protected override async Task OnInitializedAsync()
    {
        cities = await CityService.GetCities();
    }
}

AddCity.razor

@using BlazorDapperSPA.Data
@page "/addcity"
@inject NavigationManager NavigationManager
@inject ICityService CityService
<h2>Create City</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@city.Name" />
            </div>
            <div class="form-group">
                <label for="State" class="control-label">State</label>
                <input for="State" class="form-control" @bind="@city.State" />
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-4">
            <div class="form-group">
                <input type="button" class="btn btn-primary" @onclick="@CreateCity" value="Save"/>
                <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />
            </div>
        </div>
    </div>
</form>
@code {
    City city = new City();
    protected async Task CreateCity()
    {
        await CityService.CreateCity(city);
        NavigationManager.NavigateTo("listcities");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listcities");
    }
}

EditCity.razor

@using BlazorDapperSPA.Data
@page "/editcity/{id:int}"
@inject NavigationManager NavigationManager
@inject ICityService CityService
<h2>Edit City</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@city.Name" />
            </div>
            <div class="form-group">
                <label for="State" class="control-label">State</label>
                <input for="State" class="form-control" @bind="@city.State" />
            </div>
        </div>
    </div>
    <div class="row">
        <div class="form-group">
            <input type="button" class="btn btn-primary" @onclick="@UpdateCity" value="Update" />
            <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />
        </div>
    </div>
</form>
@code {
    [Parameter]
    public int id { get; set; }
    City city = new City();
    protected override async Task OnInitializedAsync()
    {
        city = await CityService.SingleCity(id);
    }
    protected async Task UpdateCity()
    {
        await CityService.EditCity(id, city);
        NavigationManager.NavigateTo("listcities");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listcities");
    }
}

DeleteCity.razor

@using BlazorDapperSPA.Data
@page "/deletecity/{id:int}"
@inject NavigationManager NavigationManager
@inject ICityService CityService
<h2>Confirm Delete</h2>
<p>Are you sure you want to delete this City with Id: <b>@id</b></p>
<br />
<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@city.Name</td>
        </tr>
        <tr>
            <td>State</td>
            <td>@city.State</td>
        </tr>
    </table>
    <div class="form-group">
        <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />
        <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />
    </div>
</div>
@code {
    [Parameter]
    public int id { get; set; }
    City city = new City();
    protected override async Task OnInitializedAsync()
    {
        city = await CityService.SingleCity(id);
    }
    protected async Task Delete()
    {
        await CityService.DeleteCity(id);
        NavigationManager.NavigateTo("listcities");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("listcities");
    }
}

We can create component files for Employee CRUD actions

ListEmployees.razor

@using BlazorDapperSPA.Data  
@page "/listemployees"  
@inject IEmployeeService EmployeeService  
<h2>Employee Details</h2>  
<p>  
    <a href="/addemployee">Create New Employee</a>  
</p>  
@if (employees == null)  
{  
    <img src="./basicloader.gif" />  
}  
else  
{  
    <table class='table'>  
        <thead>  
            <tr>  
                <th>Name</th>  
                <th>Department</th>  
                <th>Designation</th>  
                <th>Company</th>  
                <th>City</th>  
            </tr>  
        </thead>  
        <tbody>  
            @foreach (var employee in employees)  
            {  
                <tr>  
                    <td>@employee.Name</td>  
                    <td>@employee.Department</td>  
                    <td>@employee.Designation</td>  
                    <td>@employee.Company</td>  
                    <td>@employee.CityName</td>  
                    <td>  
                        <a href='/editemployee/@employee.Id'>Edit</a>  
                        <a href='/deleteemployee/@employee.Id'>Delete</a>  
                    </td>  
                </tr>  
  
            }  
        </tbody>  
    </table>  
}  
@code {  
    IEnumerable<EmployeeModel> employees;  
    protected override async Task OnInitializedAsync()  
    {  
        employees = await EmployeeService.GetEmployees();  
    }  
}

AddEmployee.razor

@using BlazorDapperSPA.Data  
@page "/addemployee"  
@inject NavigationManager NavigationManager  
@inject IEmployeeService EmployeeService  
@inject ICityService CityService  
<h2>Create Employee</h2>  
<hr />  
<form>  
    <div class="row">  
        <div class="col-md-8">  
            <div class="form-group">  
                <label for="Name" class="control-label">Name</label>  
                <input for="Name" class="form-control" @bind="@employee.Name" />  
            </div>  
            <div class="form-group">  
                <label for="Department" class="control-label">Department</label>  
                <input for="Department" class="form-control" @bind="@employee.Department" />  
            </div>  
            <div class="form-group">  
                <label for="Designation" class="control-label">Designation</label>  
                <input for="Designation" class="form-control" @bind="@employee.Designation" />  
            </div>  
            <div class="form-group">  
                <label for="Company" class="control-label">Company</label>  
                <input for="Company" class="form-control" @bind="@employee.Company" />  
            </div>  
            <div class="form-group">  
                <label for="City" class="control-label">City</label>  
                <select for="City" class="form-control" @bind="@employee.CityId">  
                    <option value="">-- Select City --</option>  
                    @foreach (var city in cities)  
                    {  
                        <option value="@city.Id">@city.Name</option>  
                    }  
                </select>  
            </div>  
        </div>  
    </div>  
    <div class="row">  
        <div class="col-md-4">  
            <div class="form-group">  
                <input type="button" class="btn btn-primary" @onclick="@CreateEmployee" value="Save" />  
                <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
            </div>  
        </div>  
    </div>  
</form>  
@code {  
    Employee employee = new Employee();  
    IEnumerable<City> cities = new List<City>();  
    protected override async Task OnInitializedAsync()  
    {  
        cities = await CityService.GetCities();  
    }  
    protected async Task CreateEmployee()  
    {  
        await EmployeeService.CreateEmployee(employee);  
        NavigationManager.NavigateTo("listemployees");  
    }  
    void Cancel()  
    {  
        NavigationManager.NavigateTo("listemployees");  
    }  
}

EditEmployee.razor

@using BlazorDapperSPA.Data  
@page "/editemployee/{id:int}"  
@inject NavigationManager NavigationManager  
@inject IEmployeeService EmployeeService  
@inject ICityService CityService  
<h2>Edit Employee</h2>  
<hr />  
<form>  
    <div class="row">  
        <div class="col-md-8">  
            <div class="form-group">  
                <label for="Name" class="control-label">Name</label>  
                <input for="Name" class="form-control" @bind="@employee.Name" />  
            </div>  
            <div class="form-group">  
                <label for="Department" class="control-label">Department</label>  
                <input for="Department" class="form-control" @bind="@employee.Department" />  
            </div>  
            <div class="form-group">  
                <label for="Designation" class="control-label">Designation</label>  
                <input for="Designation" class="form-control" @bind="@employee.Designation" />  
            </div>  
            <div class="form-group">  
                <label for="Company" class="control-label">Company</label>  
                <input for="Company" class="form-control" @bind="@employee.Company" />  
            </div>  
            <div class="form-group">  
                <label for="City" class="control-label">City</label>  
                <select for="City" class="form-control" @bind="@employee.CityId">  
                    <option value="">-- Select City --</option>  
                    @foreach (var city in cities)  
                    {  
                        <option value="@city.Id">@city.Name</option>  
                    }  
                </select>  
            </div>  
        </div>  
    </div>  
    <div class="row">  
        <div class="form-group">  
            <input type="button" class="btn btn-primary" @onclick="@UpdateEmployee" value="Update" />  
            <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
        </div>  
    </div>  
</form>  
@code {  
    [Parameter]  
    public int id { get; set; }  
    EmployeeModel employee = new EmployeeModel();  
    IEnumerable<City> cities = new List<City>();  
    protected override async Task OnInitializedAsync()  
    {  
        cities = await CityService.GetCities();  
        employee = await EmployeeService.SingleEmployee(id);  
    }  
    protected async Task UpdateEmployee()  
    {  
        await EmployeeService.EditEmployee(id, employee);  
        NavigationManager.NavigateTo("listemployees");  
    }  
    void Cancel()  
    {  
        NavigationManager.NavigateTo("listemployees");  
    }  
}

DeleteEmployee.razor

@using BlazorDapperSPA.Data   
@page "/deleteemployee/{id:int}"  
@inject NavigationManager NavigationManager  
@inject IEmployeeService EmployeeService  
<h2>Confirm Delete</h2>  
<p>Are you sure you want to delete this Employee with Id :<b> @id</b></p>  
<br />  
<div class="col-md-4">  
    <table class="table">  
        <tr>  
            <td>Name</td>  
            <td>@employee.Name</td>  
        </tr>  
        <tr>  
            <td>Department</td>  
            <td>@employee.Department</td>  
        </tr>  
        <tr>  
            <td>Designation</td>  
            <td>@employee.Designation</td>  
        </tr>  
        <tr>  
            <td>Company</td>  
            <td>@employee.Company</td>  
        </tr>  
        <tr>  
            <td>City</td>  
            <td>@employee.CityName</td>  
        </tr>  
    </table>  
    <div class="form-group">  
        <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />  
        <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />  
    </div>  
</div>  
@code {  
    [Parameter]  
    public int id { get; set; }  
    EmployeeModel employee = new EmployeeModel();  
    protected override async Task OnInitializedAsync()  
    {  
        employee = await EmployeeService.SingleEmployee(id);  
    }  
    protected async Task Delete()  
    {  
        await EmployeeService.DeleteEmployee(id);  
        NavigationManager.NavigateTo("listemployees");  
    }  
    void Cancel()  
    {  
        NavigationManager.NavigateTo("listemployees");  
    }  
}

We can modify the “NavMenu” shared component with routing to City and Employee details.

NavMenu.razor

<div class="top-row pl-4 navbar navbar-dark">  
    <a class="navbar-brand" href="">Blazor with Dapper</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> Home  
            </NavLink>  
        </li>  
        <li class="nav-item px-3">  
            <NavLink class="nav-link" href="listcities">  
                <span class="oi oi-list-rich" aria-hidden="true"></span> City data  
            </NavLink>  
        </li>  
        <li class="nav-item px-3">  
            <NavLink class="nav-link" href="listemployees">  
                <span class="oi oi-list-rich" aria-hidden="true"></span> Employee data  
            </NavLink>  
        </li>  
    </ul>  
</div>  
@code {  
    bool collapseNavMenu = true;  
    string NavMenuCssClass => collapseNavMenu ? "collapse" : null;  
    void ToggleNavMenu()  
    {  
        collapseNavMenu = !collapseNavMenu;  
    }  
}

We have completed the entire coding. We can run the application.

Home

We can create a new City by clicking the “City data” menu link and clicking the “Create New City” hyperlink.

Create city

After saving the City details, we can click the “Employee data” menu link and click the “Create New Employee” hyperlink to create new employee data.

Create employee

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 hyperlinks.

Employee data

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 an SQL server. In the City entity, we have used native SQL commands to retrieve and store data. While in the 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 of upcoming posts.

21 Comments

Join the conversation! Your thoughts help the community grow.

Maurice Aboagye5y ago
Hello Sarathlal, Thanx for this enlightening Example. Please Add an IMAGE or PICTURE COLUMN to the EMPLOYEES Table and help us do CRUD operations with it..
o i6y ago
How could i add pagination to the get function in the service? When you're working with large datasets it's pretty important.
- Guest User6y ago
  Hi. pagination is beyond the scope of this article which perfectly demonstrates to us developers who want to use SPs + Dapper with the correct repository/services model. And with a typical FK relationship. Pagination would be essential for larger datasets but is covered elsewhere :-)
Steev6y ago
I had to add 'selected="@[email protected]"' into EditEmployee.razor so it shows the selected City when editing an Employee. Everything else works perfectly. Well done!
Steev6y ago
This is really brilliant! Thank you Sir! One point though, don't ever use select * in a stored procedure. If someone adds or removes columns from the table the shape of your result set will change.
Inzi Bhai6y ago
Hi, Can you point also how we can avoid SQL Injection in Dapper? or best practices in your example just to male it complete
- Sarathlal Saseendran6y ago
  Hi Inzi, thanks for your feedback. This is a basic article with dapper and blazor. As per your request, will write more articles on dapper with best practices soon. Will update you accordingly.
Inzi Bhai6y ago
Good Example, I am completely new to Blazor and prefer SP over EF and this is the example where i can star, One question i would like to ask is how can i validate form as i dont see any validation. could you add validation to this example to make it complete...
- Sarathlal Saseendran6y ago
  There are many good articles available for validations
Chittaranjan Swain6y ago
Very nice article.
- Sarathlal Saseendran6y ago
  Thank you very much Chittaranjan Swain
aifaz khan6y ago
Good example
- Sarathlal Saseendran6y ago
  Thank you very much aifaz khan
Sourav Kumar Das7y ago
Nice useful Article Sir.
- Sarathlal Saseendran7y ago
  Thank you very much Sourav Kumar Das
Geo J Thachankary7y ago
Nice article.
- Sarathlal Saseendran7y ago
  Thanks a lot Geo J Thachankary
Sundaram Subramanian7y ago
If my project does not need EF, then I can choose Dapper ?. Is that point ?. I just confused in this, please advice !
- Sarathlal Saseendran7y ago
  Dear Sundar, In my own experience, EF is not good for an enterprise application with more stored procedures and functions. We can use Dapper in those applications. We have experienced a huge performance improvement in Dapper. EF is more suitable for small application which is more developer friendly. I always prefer Dapper over EF for performance first applications. Hope, this will clear your confusions. Dapper is developed by Stackoverflow team and it is used in their website. You can witness the performance of Dapper in their site itself.
Farhan Ahmed7y ago
Great work...
- Sarathlal Saseendran7y ago
  Thank you very much Farhan Ahmed