Proyek Blazor Server #1

CRUD untuk tabel tunggal menggunakan Dapper

Dapper dan Entity Framework Core adalah dua ORM (Object-Relational Mapping ‘pemetaan relasional-objek’) yang biasanya digunakan di .NET. Keduanya memetakan hasil kueri basis data ke kelas domain C# dan sebaliknya. Dapper disukai karena ringan, cepat, sederhana, mudah digunakan dan dipahami. Dapper sangat cocok bagi pemrogram yang terbiasa menggunakan SQL dan prosedur pada SQL Server.

CRUD harus ada dalam sebuah aplikasi basis data. Tulisan ini membahas operasi CRUD dalam aplikasi Blazor Server menggunakan Dapper untuk tabel tunggal.

Tampilan Halaman CRUD

Aplikasi yang dibangun menampilkan halaman CRUD untuk tabel Publisher.

• Pilih menu Publishers untuk menampilkan daftar penerbit.

• Halaman di atas menampilkan daftar penerbit, terurut berdasarkan Id, dari besar ke kecil. Dengan mengklik judul kolom Id, urutan berubah dari kecil ke besar. Hal yang sama berlaku untuk kolom lainnya.
• Tombol navigasi:
  ◄ ke halaman sebelumnya
  ► ke halaman berikutnya
  1, 2, 3, ... nomor halaman
• Untuk mencari publisher berdasarkan kriteria nama, ketik nama penerbit di kotak pencarian. Nama tidak harus lengkap.
• Untuk menambah data baru, klik Add new data
• Untuk mengubah data yang ada, klik Edit
• Untuk menghapus, klik Delete

Berikut adalah halaman untuk menambahkan Publisher.

Berikut ini adalah halaman untuk mengedit Publisher.

Instalasi SQL Server

• Pastikan SQL Server telah terpasang di komputer. SQL Server 2019 biasanya dipasangkan dengan SSMS (SQL Server Management Studio) versi 18.x. Sayangnya, SSMS 18.x bermasalah pada fitur diagram basis data. Sebaiknya gunakan versi yang lebih lama, SQL Server 2017 Developer yang dapat diunduh di sini.
• SSMS 18.x tidak dapat digunakan untuk SQL Server 2017. Gunakan SSMS 17.9.1 yang dapat diunduh di sini.

Release notes for (SSMS) - SQL Server Management Studio (SSMS)

Membuat Basis Data BookDB

• Buka SSMS.

• Pilih nama server, klik Connect

• Buka editor query dengan menekan Ctrl+N atau memilih menu:
  File|New|Query with Current Connection
• Copy skrip SQL berikut, paste ke editor query.

CREATE DATABASE [BookDB]
GO
USE [BookDB]
GO
CREATE TABLE [dbo].[Publisher](
       [Id]       [int] IDENTITY(1,1) NOT NULL,
       [Name]     [varchar](20) NOT NULL,
       [City]     [varchar](20),
       [Country]  [varchar](20),
       CONSTRAINT [pkPublisher] PRIMARY KEY ([Id])
)
GO
CREATE TABLE [dbo].[Author](
       [Id]    [smallint] NOT NULL,
       [FName] [varchar](20) NOT NULL,
       [LName] [varchar](20),
       [Phone] [varchar](16) DEFAULT ('UNKNOWN'),
       CONSTRAINT [pkAuthor] PRIMARY KEY ([Id])
)
GO
CREATE TABLE [dbo].[Book](
       [ISBN]      [bigint] NOT NULL,
       [Title]     [varchar](80) NOT NULL,
       [PubYear]   [smallint],
       [PurchDate] [date],
       [PubId]     [int],
       CONSTRAINT  [pkBook] PRIMARY KEY ([ISBN]),
       CONSTRAINT  [fkBookPub] FOREIGN KEY ([PubId])
                   REFERENCES  [dbo].[Publisher]([Id])
)
GO
CREATE TABLE [dbo].[BookAuthor](
       [ISBN]      [bigint] NOT NULL,
       [AuthorId]  [smallint] NOT NULL,
       [AuthorOrd] [tinyint],
       CONSTRAINT  [pkBookAuthor] PRIMARY KEY ([ISBN],[AuthorId]),
       CONSTRAINT  [fkBookAuthor_Book] FOREIGN KEY([ISBN])
                   REFERENCES [dbo].[Book] ([ISBN]),
       CONSTRAINT  [fkBookAuthor_Author] FOREIGN KEY ([AuthorId])
                   REFERENCES [dbo].[Author] ([Id])
)
GO

• Pilih menu: Query|Execute, atau tekan F5 untuk mengeksekusi skrip SQL dan menghasilkan basis data BookDB.
• Diagramnya adalah sebagai berikut.

Tabel Publisher: Entri Data dan Prosedur

Sebagai tahap awal, akan dibuat CRUD untuk tabel Publisher. Berikut adalah skrip untuk menambah data dan membuat prosedur menambah data baru dan prosedur mengubah satu data berdasarkan kriteria nilai primary key.

USE [BookDB]
GO
INSERT INTO Publisher([Name], [City], [Country]) 
       VALUES ('Unsri Press', 'Palembang', 'Indonesia'),
              ('Gramedia', 'Jakarta', 'Indonesia'),
              ('Apress', 'New York', 'USA'),
              ('UIGM Press', 'Palembang', 'Indonesia'),
              ('Packt', 'Birmingham', 'UK'),
              ('Rafah Press', 'Palembang', 'Indonesia'),
              ('O’Reilly','Sebastopol','USA'),
              ('Informatika', 'Bandung', 'Indonesia'),
              ('Pearson', 'London', 'UK'),
              ('Cengage', 'Boston', 'USA'),
              ('Noerfikri', 'Palembang', 'Indonesia')
GO
                      
CREATE PROCEDURE [dbo].[spAddPublisher]
                 @Name    varchar(20), 
                 @City    varchar(20), 
                 @Country varchar(20) AS
BEGIN    
    DECLARE @Id as SmallInt;
    INSERT INTO Publisher([Name], [City], [Country]) 
    VALUES (@Name, @City, @Country);
    SET    @Id = SCOPE_IDENTITY();   
    SELECT @Id AS pubId;   
END
GO
CREATE PROCEDURE [dbo].[spUpdatePublisher]
                 @Id      SmallInt, 
                 @Name    varchar(20), 
                 @city    varchar(20), 
                 @country varchar(20)
AS
    UPDATE Publisher 
    SET [Name]    = @Name, 
        [City]    = @City, 
        [Country] = @Country 
    WHERE [Id] = @Id
GO

• Pilih menu: Query|Execute, atau tekan F5 untuk mengeksekusi skrip di atas.

Membuat Proyek Blazor Server

• Buka Visual Studio 2019.
• Buat proyek Blazor Server dengan nama BookApp. Cara membuat proyek Blazor Server telah dibahas pada tulisan sebelumnya.

Model Hosting ASP.NET Core Blazor

Menghapus File

Karena tidak diperlukan, hapus file berikut.
⦁ Data/WeatherForecast.cs
⦁ Data/WeatherForecastService.cs
⦁ Pages/Counter.razor
⦁ Pages/FetchData.razor
⦁ Pages/SurveyPrompt.razor

Buka jendela Solution Explorer.

Untuk menghapus kelima file tersebut:
• Tekan kunci Ctrl sambil mengklik setiap file yang akan dihapus.
• Klik kanan salah satu file , misalnyaSurveyPrompt.razor
• Klik Delete

• Klik OK, kelima file dihapus permanen.

Menghapus Kode Sumber

Terkait dengan file yang dihapus di atas, kode dalam file lain yang terkait juga harus dihapus.

File Startup.cs, baris 42
services.AddSingleton<WeatherForecastService>(); 
File NavMenu.razor, baris 15 s.d 24
<li class="nav-item px-3">
<NavLink class="nav-link" href="counter">
<span class="oi oi-plus" aria-hidden="true"></span> Counter
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="fetchdata">
<span class="oi oi-list-rich" aria-hidden="true"></span> Fetch data
</NavLink>
</li>
File Index.razor, baris 7
<SurveyPrompt Title="How is Blazor working for you?" />

Membuat String Koneksi Basis Data

• Pilih menu: View|SQL Server Object Explorer.

• Klik kanan BookDB, klik Properties.

• Pada jendela Properties, copy string koneksi.

Data Source=PRIMARY-PC;Initial Catalog=BookDB;
Integrated Security=True;Connect Timeout=30;Encrypt=False;
TrustServerCertificate=False;ApplicationIntent=ReadWrite;
MultiSubnetFailover=False

• Buka jendela Solution Explorer.

• Klik appsettings.json untuk membuka file-nya.
• Tambahkan kode berikut ke file appsettings.json

• Supaya terkoneksi ke basis data BookDB di server PRIMARY-PC, paste string koneksi yang telah di-copy sebelumnya.
• Simpan file appsettings.json

Instalasi Paket

Untuk membuat CURD menggunakan Dapper, paket berikut harus terpasang.
1. Dapper
2. Microsoft.EntityFrameworkCore.SqlServer

Cek paket terpasang

• Gunakan Package Manager Console.

• Pilih menu: Tools|NuGet Package Manager|Package Manager Console.

• Ketikkan Get-Package⏎
• Gambar di atas menunjukkan belum ada paket yang terpasang.

Instalasi Dapper

• Berikut adalah salah satu cara menginstal paket.

• Pilih menu:
  Tools|NuGet Package Manager|Manage NuGet Packages Solution...
• Tampil jendela NuGet Solution.

• Klik Browse.
• Pada kotak Search, ketikkan Dapper.
• Dari daftar paket, pilih Dapper, klik Install.

• Klik OK.

Instalasi Microsoft.EntityFrameworkCore.SqlServer

• Lakukan hal yang sama untuk paket Microsoft.EntityFrameworkCore. SqlServer, atau gunakan cara lain via jendela Solution Explorer.

• Klik kanan Solution 'BookApp',
• Klik Manage NuGet Packages for Solution...
• Selanjutnya, sama seperti pada instalasi paket Dapper.

Daftar Paket Terpasang

• Selain melalui menu: Tools|NuGet Package Manager|Package Manager Console, daftar paket terpasang dapat dilihat via jendela Solution Explorer dengan memilih menu View|Solution Explorer

• Klik BookApp|Dependencies|Packages

File Pendukung Operasi CRUD Publisher

Operasi CRUD membutuhkan file entitas, interface, metode, razor dan modifikasi kode pada beberapa file yang sudah ada.

File Entitas: Publisher.cs

Kelas model atau entitas Publisher merupakan pemetaan tabel Publisher di basis data. File Publisher.cs diletakkan dalam folder Entities.

• Buka jendela Solution Explorer.

• Klik kanan BookApp, lalu klik Add|New Folder.
• Sebagai nama foldernya, ketikkan Entities.
• Selanjutnya, buat sebuah file kelas.

• Klik kanan Entities, lalu klik Add|Class.

• Ketikkan Publisher.cs sebagai nama filenya, klik Add.
• Klik Publisher.cs untuk membuka filenya, lalu copy dan paste kode berikut.

using System.ComponentModel.DataAnnotations;
namespace BookApp.Entities
{
    public class Publisher
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }
}

File Interface

File interface mendeklarasikan metode. Ada dua file interface, IDapperService.cs dan IPublisherService.cs. Keduanya diletakkan dalam folder Interfaces.

IDapperService.cs

• Buat folder baru dengan nama Interfaces.
• Buat file IDapperService.cs, lalu copy dan paste kode berikut.

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;namespace BookApp.Interfaces
{
   public interface IDapperService : IDisposable
   {
      DbConnection GetConnection();
      T Get<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure);
      List<T> GetAll<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure);
      int Execute(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure);
      T Insert<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure);
      T Update<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure);
   }
}

IPublisherService.cs

• Buat file IPulisherService.cs, lalu copy dan paste kode berikut.

using BookApp.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;namespace BookApp.Interfaces
{
   public interface IPublisherService
   {
      Task<int> Create(Publisher publisher);
      Task<int> Delete(int Id);
      Task<int> Count(string search);
      Task<int> Update(Publisher publisher);
      Task<Publisher> GetById(int Id);
      Task<List<Publisher>> ListAll(int skip, int take,
          string orderBy, string direction, string search);
   }
}

File Metode

File ini mengimplementasikan metode yang dideklarasikan dalam file interface. Ada dua file metode, DapperService.cs dan PublisherService.cs. Keduanya diletakkan dalam folder Data.

DapperService.cs

• Dalam folder Data, buat file DapperService.cs, lalu copy dan paste kode berikut.

using BookApp.Interfaces;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;namespace BookApp.Data
{
   public class DapperService : IDapperService
   {
      private readonly IConfiguration _config;
      public DapperService(IConfiguration config)
      {
        _config = config;
      }
      public DbConnection GetConnection()
      {
         return new SqlConnection
            (_config.GetConnectionString("DefaultConnection"));
      }
      public T Get<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure)
      {
         using IDbConnection db = new SqlConnection
            (_config.GetConnectionString("DefaultConnection"));
         return db.Query<T>(sp, parms,
            commandType: commandType).FirstOrDefault();
      }
      public List<T> GetAll<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure)
      {
         using IDbConnection db = new SqlConnection
            (_config.GetConnectionString("DefaultConnection"));
         return db.Query<T>(sp, parms,
            commandType: commandType).ToList();
      }
      public int Execute(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure)
      {
         using IDbConnection db = new SqlConnection
            (_config.GetConnectionString("DefaultConnection"));
         return db.Execute(sp, parms, commandType: commandType);
      }
      public T Insert<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure)
      {
         T result;
         using IDbConnection db = new SqlConnection
            (_config.GetConnectionString("DefaultConnection"));
         try
         {
            if (db.State == ConnectionState.Closed)
               db.Open();
            using var tran = db.BeginTransaction();
            try
            {
               result = db.Query<T>(sp, parms, commandType:
                  commandType, transaction: tran).FirstOrDefault();
               tran.Commit();
            }
            catch (Exception ex)
            {
               tran.Rollback();
               throw ex;
            }
         }
         catch (Exception ex)
         {
            throw ex;
         }
         finally
         {
            if (db.State == ConnectionState.Open)
               db.Close();
         }
         return result;
      }
      public T Update<T>(string sp, DynamicParameters parms,
         CommandType commandType = CommandType.StoredProcedure)
      {
         T result;
         using IDbConnection db = new SqlConnection
            (_config.GetConnectionString("DefaultConnection"));
         try
         {
            if (db.State == ConnectionState.Closed)
               db.Open();
            using var tran = db.BeginTransaction();
            try
            {
               result = db.Query<T>(sp, parms, commandType:
                  commandType, transaction: tran).FirstOrDefault();
               tran.Commit();
            }
            catch (Exception ex)
            {
               tran.Rollback();
               throw ex;
            }
         }
         catch (Exception ex)
         {
            throw ex;
         }
         finally
         {
            if (db.State == ConnectionState.Open)
               db.Close();
         }
         return result;
      }
      public void Dispose()
      {
      }
   }
}

PublisherService.cs

• Dalam folder Data, buat file PublisherService.cs, lalu copy dan paste kode berikut.

using BookApp.Interfaces;
using BookApp.Entities;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;namespace BookApp.Data
{
   public class PublisherService : IPublisherService
   {
      private readonly IDapperService _dapperService;
      public PublisherService(IDapperService dapperService)
      {
         this._dapperService = dapperService;
      }
      public Task<int> Create(Publisher publisher)
      {
         var dbPara = new DynamicParameters();
         dbPara.Add("Name", publisher.Name, DbType.String);
         dbPara.Add("City", publisher.City, DbType.String);
         dbPara.Add("Country", publisher.Country, DbType.String);
         var publisherId = Task.FromResult
            (_dapperService.Insert<int>("[dbo].[spAddPublisher]",
            dbPara, commandType: CommandType.StoredProcedure));
         return publisherId;
      }
      public Task<Publisher> GetById(int id)
      {
         var publisher = Task.FromResult
            (_dapperService.Get<Publisher> 
            ($"select * from [Publisher] where Id = {id}", null,
            commandType: CommandType.Text));
         return publisher;
      }
      public Task<int> Delete(int id)
      {
         var deletePublisher = Task.FromResult
            (_dapperService.Execute
            ($"Delete [Publisher] where Id = {id}", null,
            commandType: CommandType.Text));
         return deletePublisher;
      }
      public Task<int> Count(string search)
      {
         var totPublisher = Task.FromResult(_dapperService.Get<int>
            ($"select COUNT(*) from [Publisher]
            WHERE Name like '%{search}%'", null,
            commandType: CommandType.Text));
         return totPublisher;
      }
      public Task<List<Publisher>> ListAll (int skip, int take,
         string orderBy,string direction="DESC",string search = "")
      {
         var publishers = Task.FromResult
            (_dapperService.GetAll<Publisher>
            ($"SELECT * FROM [Publisher] WHERE Name like
            '%{search}%' ORDER BY {orderBy}
            {direction} OFFSET {skip} ROWS FETCH NEXT {take}
            ROWS ONLY; ", null, commandType: CommandType.Text));
         return publishers;
      }
      public Task<int> Update(Publisher publisher)
      {
         var dbPara = new DynamicParameters();
         dbPara.Add("Id", publisher.ID);
         dbPara.Add("Name", publisher.Name, DbType.String);
         dbPara.Add("City", publisher.City, DbType.String);
         dbPara.Add("Country", publisher.Country, DbType.String);
         var updatePublisher = Task.FromResult
            (_dapperService.Update<int>("[dbo].[spUpdatePublisher]",
            dbPara, commandType: CommandType.StoredProcedure));
         return updatePublisher;
      }
   }
}

AppContext.cs

Masih dalam folder Data, buat file AppContext.cs, lalu copy dan paste kode berikut.

using Microsoft.EntityFrameworkCore;namespace BookApp.Data
{
   public class AppContext : DbContext
   {
      public AppContext() { }
   public AppContext(DbContextOptions<AppContext> options) :
         base(options) { }
   }
}

File Komponen *.razor

Ada tiga file razor yang ditambahkan ke dalam folder Page, yaitu AddPublisher.razor, EditPublisher.razor, FetchPublisher.razor. Ketiganya berisi kode untuk menampilkan halaman CRUD.

AddPublisher.razor

@page "/addPublisher" 
@inject IPublisherService publisherService 
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager 
<h2> 
   Add Publisher 
</h2><div class="row">
   <div class="col-md-4">
      <form>
         <div class="form-group">
            <label for="Name" class="control-label">Name</label>
            <input for="Name" class="form-control"
            @bind-value="@publisher.Name" />
            <label for="City" class="control-label">City</label>
            <input for="City" class="form-control"
            @bind-value="@publisher.City" />
            <label for="Country"
               class="control-label">Country</label>
            <input for="Country" class="form-control"
            @bind-value="@publisher.Country" />
         </div>
         <div class="form-group">
            <button type="button" class="btn btn-primary"
            @onclick="() => CreatePublisher()"> Save </button> 
            <button type="button" class="btn btn-warning"
            @onclick="() => cancel()">Cancel</button>
         </div>
      </form>
   </div>
</div>@code {
   Publisher publisher = new Publisher();
   protected async Task CreatePublisher()
   {
      await publisherService.Create(publisher);
      navigationManager.NavigateTo("/publisherlist");
   }
   void cancel()
   {
      navigationManager.NavigateTo("/publisherlist");
   }
}

EditPublisher.razor

@page "/editPublisher/{id:int}"
@inject IPublisherService publisherService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h2>
   Edit Publisher
</h2>
<div class="row">
   <div class="col-md-4">
      <form>
         <div class="form-group">
            <label for="Name" class="control-label">Name</label>
            <input for="Name" class="form-control"
               @bind-value="@publisher.Name" />
            <label for="City" class="control-label">City</label>
            <input for="City" class="form-control"
               @bind-value="@publisher.City" />
            <label for="Country" class="control-label">
               Country</la
            <input for="Country" class="form-control"
               @bind-value="@publisher.Country" />           
         </div>
         <div class="form-group">
            <button type="button" class="btn btn-primary"
               @onclick="() => UpdatePublisher()"> Save </button> 
            <button type="button" class="btn btn-warning"
               @onclick="() => cancel()">Cancel</button>
         </div>
      </form>
   </div>
</div>
 
@code  {
 
   [Parameter]
   public int id { get; set; }
 
   Publisher publisher = new Publisher();
 
   protected override async Task OnInitializedAsync()
   {
      publisher = await
         publisherService.GetById(id);
   }
 
   protected async Task UpdatePublisher()
   {
      await publisherService.Update(publisher);
      navigationManager.NavigateTo("/publisherlist");
   }
 
   void cancel()
   {
      navigationManager.NavigateTo("/publisherlist");
   }
}

FetchPublisher.razor

@page "/publisherlist"
@inject IPublisherService publisherService
 
<link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0
/css/font-awesome.min.css" rel="stylesheet">
 
<style>
   .sort-th {
      cursor: pointer;
   }
 
   .fa {
      float: right;
   }
 
   .btn-custom {
      color: black;
      float: left;
      padding: 8px 16px;
      text-decoration: none;
      transition: background-color .3s;
      border: 2px solid #000;
      margin: 0px 5px 0px 5px;
   }
</style>
 
<div>
   <a class="btn btn-primary"
      href='/addPublisher'>Add new data</a>
</div>
<br />
 
@if (publishers == null)
{
   p><em>Loading...</em></p>
}
else
{
   <div class="row col-md-3 pull-right">
      <input type="text" id="txtSearch"
         placeholder="Search Names..."
         class="form-control" @bind="SearchTerm"
         @bind:event="oninput" />
   </div>
   <table class="table table-bordered table-hover">
      <thead>
         <tr>
            <th class="sort-th"
               @onclick="@(() => SortTable("Id"))">
               Id<span class="fa @(SetSortIcon("Id"))"></span>
            </th>
            <th class="sort-th"
               @onclick="@(() => SortTable("Name"))">Name
               <span class="fa @(SetSortIcon("Name"))"></span>
            </th>
            <th class="sort-th"
               @onclick="@(() => SortTable("City"))">City
               <span class="fa @(SetSortIcon("City"))"></span>
            </th>
            <th class="sort-th"
               @onclick="@(() => SortTable("Country"))">Country
               <span class="fa @(SetSortIcon("Country"))"></span>
            </th>
            <th>Action</th>
         </tr>
      </thead>
      <tbody>
         @if (publishers == null || publishers.Count == 0)
         {
            <tr>
               <td colspan="3">
                  No Records to display
               </td>
            </tr>
         }
         else
         {
            foreach (var publisher in publishers)
            {
               <tr>
                  <td>@publisher.Id</td>
                  <td>@publisher.Name</td>
                  <td>@publisher.City</td>
                  <td>@publisher.Country</td>
                  <td>
                     <a class="btn btn-primary"
                     href='/editPublisher/@publisher.Id'>
                      Edit</a> 
                     󠀠󠀠󠀠󠀠<a class="btn btn-warning" @onclick=
                     "() => DeletePublisher(publisher.Id)">
                      Delete </a>
                  </td>
               </tr>
            }
         } 
      </tbody>
   </table>
   <div class="pagination">
      <button class="btn btn-custom"
         @onclick=@(async ()=>await NavigateToPage ("previous"))>◀
      </button>
 
      @for (int i = startPage; i <= endPage; i++)
      {
         var currentPage = i;
         <button class="btn btn-custom pagebutton
            @(currentPage==curPage?"btn-info":"")"
            @onclick=@(async () =>await refreshRecords
            (currentPage))> @currentPage
         </button>
      }
 
      <button class="btn btn-custom"
         @onclick=@(async ()=>await NavigateToPage("next"))>▶
      </button>
 
   </div>
}@code {
   private string searchTerm;
   private string SearchTerm
   {
      get { return searchTerm; }
      set { searchTerm = value; FilterRecords(); }
   }
 
   List<Publisher> publishers;
   
   #region Pagination
 
   int totalPages;
   int totalRecords;
   int curPage;
   int pagerSize;
   int pageSize;
   int startPage;
   int endPage;
   string sortColumnName = "Id";
   string sortDir = "DESC";
 
   #endregion
 
   protected override async Task OnInitializedAsync()
   {
      //display total page buttons
      pagerSize = 3;
      pageSize = 5;
      curPage = 1;
      publishers = await publisherService.ListAll((curPage - 1)
      * pageSize, pageSize, sortColumnName, sortDir, searchTerm);
      totalRecords = await publisherService.Count(searchTerm);
      totalPages=(int)Math.Ceiling(totalRecords/(decimal)pageSize);
      SetPagerSize("forward");
   }
 
   protected async Task DeletePublisher(int id)
   {
      await publisherService.Delete(id);
      publishers = await publisherService.ListAll((curPage - 1)
      * pageSize, pageSize, sortColumnName, sortDir, searchTerm);
   }
 
   private bool isSortedAscending;
   private string activeSortColumn;
 
   private async Task<List<Publisher>>
           SortRecords(string columnName, string dir)
   {
      return await publisherService.ListAll((curPage - 1) *
      pageSize, pageSize, columnName, dir, searchTerm);
   }
 
   private async Task SortTable(string columnName)
   {
      if (columnName != activeSortColumn)
      {
         publishers = await SortRecords(columnName, "ASC");
         isSortedAscending = true;
         activeSortColumn = columnName;
      }
      else
      {
         if (isSortedAscending)
         {
            publishers = await SortRecords(columnName, "DESC");
         }
         else
         {
            publishers = await SortRecords(columnName, "ASC");
         }
 
         isSortedAscending = !isSortedAscending;
      }
      sortColumnName = columnName;
      sortDir = isSortedAscending ? "ASC" : "DESC";
   }
 
   private string SetSortIcon(string columnName)
   {
      if (activeSortColumn != columnName)
      {
         return string.Empty;
      }
      if (isSortedAscending)
      {
         return "fa-sort-up";
      }
      else
      {
         return "fa-sort-down";
      }
   }
 
   public async Task refreshRecords(int currentPage)
   {
      publishers = await publisherService.ListAll
         ((currentPage-1)* pageSize, pageSize, sortColumnName,
         sortDir, searchTerm);
      curPage = currentPage;
      this.StateHasChanged();
   }
 
   public void SetPagerSize(string direction)
   {
      if (direction == "forward" && endPage < totalPages)
      {
         startPage = endPage + 1;
         if (endPage + pagerSize < totalPages)
         {
            endPage = startPage + pagerSize - 1;
         }
         else
         {
             endPage = totalPages;
         }
         this.StateHasChanged();
      }
      else if (direction == "back" && startPage > 1)
      {
         endPage = startPage - 1;
         startPage = startPage - pagerSize;
      }
      else
      {
         startPage = 1;
         endPage = totalPages;
      }
   }
 
   public async Task NavigateToPage(string direction)
   {
      if (direction == "next")
      {
         if (curPage < totalPages)
         {
            if (curPage == endPage)
            {
               SetPagerSize("forward");
            }
            curPage += 1;
         }
      }
      else if (direction == "previous")
      {
         if (curPage > 1)
         {
            if (curPage == startPage)
            {
               SetPagerSize("back");
            }
            curPage -= 1;
         }
      }
      await refreshRecords(curPage);
   }
 
   public void FilterRecords()
   {
      endPage = 0;
      this.OnInitializedAsync().Wait();
   }
}

Modifikasi File

_Host.cshtml
Baris 13: <title>BookApp</title> diubah menjadi <title>LIBRARY</title>, sehingga judul web yang semula BookApp berubah menjadi LIBRARY.

NavMenu.razor
Modifikasi kodenya sehingga menjadi sebagai berikut.

<div class="top-row pl-4 navbar navbar-dark">
   <a class="navbar-brand" href="">Library</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="publisherlist">
            <span class="oi oi-plus" aria-hidden="true">
            </span> Publishers
         </NavLink>
      </li>       
   </ul>
</div>
 
@code {
   bool collapseNavMenu = true;
 
   string NavMenuCssClass => collapseNavMenu ? "collapse" : null;
 
   void ToggleNavMenu()
   {
      collapseNavMenu = !collapseNavMenu;
   }
}

_Imports.razor
Tambahkan namespace berikut.

@using BookApp.Data
@using BookApp.Interfaces
@using BookApp.Entities

Startup.cs
Tambahkan namespace berikut.

using BookApp.Interfaces;
using BookApp.Data;

Tambahkan service berikut.

services.AddDbContext<Data.AppContext>(options =>
   options.UseSqlServer
   (Configuration.GetConnectionString("DefaultConnection")));
//Publisher service
services.AddScoped<IPublisherService, PublisherService>();
//Register dapper in scope
services.AddScoped<IDapperService, DapperService>();

Berikut adalah struktur proyek secara keseluruhan.

Semoga berfaedah.

Referensi

step-by-step-tutorial

Dapper

Executing Stored Procedures With Dapper