Proyek Blazor Server #2

CRUD menggunakan Dapper untuk tabel yang merujuk ke tabel lain

CRUD (Create, Read, Update, Delete) merupakan fitur yang harus ada pada aplikasi yang menggunakan basis data. Di artikel sebelumnya, kita membahas operasi CRUD untuk satu tabel, yaitu tabel Publisher.

Proyek Blazor Server #1

Artikel ini membahas operasi CRUD yang melibatkan dua tabel, tabel Book dan Publisher, dengan relationship N:1 (banyak-ke-satu) sebagai berikut.

Tampilan Halaman CRUD

Berikut adalah halaman daftar buku yang melibatkan tabel Book dan Publisher. Empat kolom pertama adalah kolom tabel Book, dan kolom kelima adalah kolom tabel Publisher.

• Daftar buku diurutkan mengecil (descending) berdasarkan Purchase Date. Dengan mengklik judul kolom Purchase Date, urutan berubah menjadi membesar (ascending). Hal yang sama berlaku untuk kolom lain.
• Tombol navigasi:
  ◄ ke halaman sebelumnya
  ► ke halaman berikutnya
  1, 2, 3, ... nomor halaman
• Untuk mencari buku berdasarkan kriteria judul, ketikkan judul di kotak Search. Judul tidak harus lengkap.
• Untuk menambahkan data baru, klik Add new data
• Untuk mengubah data yang ada, klik Edit
• Untuk menghapus, klik Delete

Berikut adalah halaman untuk menambahkan data buku yang melibatkan tabel Book dan Publisher. Semua input data adalah atribut buku. Perhatikan input data terakhir, Publisher. Atribut terakhir buku tersebut dimasukkan dengan memilih dari daftar opsi Publisher yang diambil dari tabel Publisher.

Berikut adalah halaman untuk mengedit buku yang melibatkan tabel Book dan Publisher. Semua data adalah atribut buku. Perhatikan data terakhir, Publisher. Atribut terakhir buku ini diperbarui dengan memilih dari daftar opsi penerbit yang diambil dari tabel Publisher.

Tabel Book: Menambah Data dan Membuat Prosedur

Terlebih dahulu, kita perlu mengetikkan dan menjalankan skrip SQL untuk
(1) menambahkan data,
(2) membuat prosedur untuk menambahkan data baru, dan
(3) membuat prosedur untuk memperbarui data.

• Buka SSMS.

• Pilih nama server, klik Connect

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

USE [BookDB]
GO
INSERT INTO dbo.Book(ISBN,PubYear,PurchDate,Title,PubId)
VALUES (9789791339957, 2013, '2019-10-01', 'Pranata Sosial', 6),xxxx
       (9781292061184, 2015, '2018-02-12', 'Database Systems',9),
       (9786024474348, 2019, '2020-08-17', 
        'Desain Basis Data Akademik Perguruan Tinggi', 11),
       (9781305576766, 2015, '2018-12-31',
        'NoSQL Web Development with Apache Cassandra', 10),
       (9781484255087, 2019, '2019-10-01',
        'Beginning Database Programming Using ASP.NET Core 3', 3),
       (9781484231258, 2018, '2019-11-25',
        'Expert Apache Cassandra Administration', 3),
       (9781789619768, 2020, '2020-03-25',
        'Modern Web Development with ASP.NET Core 3', 5),
       (9781492056812, 2020, '2020-06-30',
        'Programming C# 8.0', 7),
       (9781783989201, 2015, '2020-07-23',
        'Learning Apache Cassandra', 5),
       (9781484259276, 2020, '2020-08-07',
        'Microsoft Blazor: Building Web App in .NET', 3),
       (9786020338682, 2017, '2020-08-10', 'Disruption', 2)
GO
CREATE PROCEDURE [dbo].[spAddBook]
                 @ISBN      bigint,
                 @Title     varchar(80),
                 @PubYear   smallint,
                 @PurchDate date,
                 @PubId     int
AS
BEGIN
    INSERT INTO dbo.Book(ISBN,Title,PubYear,PurchDate,PubId)
    VALUES (@ISBN, @Title, @PubYear, @PurchDate, @PubId)
    SELECT @ISBN AS bookId;
END
GO
CREATE PROCEDURE [dbo].[spUpdateBook]
                 @ISBN      bigint,
                 @Title     varchar(80),
                 @PubYear   smallint,
                 @PurchDate date,
                 @PubId     int
AS
    UPDATE Book
    SET [Title]     = @Title,
        [PubYear]   = @PubYear,
        [PurchDate] = @PurchDate,
        [PubId]     = @PubId
    WHERE [ISBN] = @ISBN
GO

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

File Pendukung Operasi CRUD

Selain skrip SQL di atas, operasi CRUD membutuhkan file entitas, interface, implementasi interface, komponen blazor, dan modifikasi kode beberapa file yang ada.

File Entitas

Ada dua file entitas, Book.cs dan BookPub.cs, ditempatkan di dalam folder Entities.

Book.cs
File Book.cs merupakan pemetaan tabel Book dalam basis data.

• Pada jendela Solution Explorer, klik kanan Entities, lalu klik Add|Class
• Ketik Book.cssebagai nama file, klik Add.
• Klik Book.csuntuk membuka file, lalu copy dan paste kode berikut.

using System;
using System.ComponentModel.DataAnnotations;
namespace BookApp.Entities
{
    public class Book
    {
        [Key]
        public long ISBN { get; set; }
        public string Title { get; set; }
        public short PubYear { get; set; }
        public DateTime PurchDate { get; set; }
        public int PubId { get; set; }
    }
}

BookPub.cs
File BookPub.cs merupakan pemetaan dari tabel Book yang dijoinkan dengan dan tabel Publisher. Properti BookPub.csmewarisi properti Book.cs. Copy dan paste kode berikut.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BookApp.Entities
{
    public class BookPub: Book
    {
        public string PubName { get; set; }
    }
}

File Interface: IBookService.cs

• IBookService.cs mendeklarasikan metode buku, ditempatkan di folder Interfaces.
• Dalam folder Interfaces tersebut, buat file IBookService.cs, lalu copy dan paste kode berikut.

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

File Metode: BookService.cs

• BookService.csmengimplementasikan metode yang dideklarasikan dalam file IBookService.cs, ditempatkan dalam folder Data.
• Dalam folder Data tersebut, buat file BookService.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 BookService : IBookService
    {
        private readonly IDapperService _dapperService;
        public BookService(IDapperService dapperService)
        {
            this._dapperService = dapperService;
        }
        public Task<long> Create(Book book)
        {
            var dbPara = new DynamicParameters();
            dbPara.Add("ISBN", book.ISBN, DbType.Int64);
            dbPara.Add("Title", book.Title, DbType.String);
            dbPara.Add("PubYear", book.PubYear, DbType.Int16);
            dbPara.Add("PurchDate", book.PurchDate, DbType.Date);
            dbPara.Add("PubId", book.PubId, DbType.Int32);
            var bookId = Task.FromResult(_dapperService.Insert<long>
                ("[dbo].[spAddBook]",dbPara, commandType:
                CommandType.StoredProcedure));
            return bookId;
        }
        public Task<Book> GetById(long id)
        {
            var book = Task.FromResult(_dapperService.Get<Book>
                ($"select * from [Book] where ISBN = {id}",
                null,commandType: CommandType.Text));
            return book;
        }
        public Task<int> Delete(long id)
        {
        var deleteBook = Task.FromResult(_dapperService.
            Execute($"Delete [Book] where ISBN = {id}",
            null,commandType: CommandType.Text));
        return deleteBook;
        }
        public Task<int> Count(string search)
        {
            var totBook = Task.FromResult(_dapperService.Get<int>
                ($"select COUNT(*) from Book WHERE Title like
                '%{search}%'",null,commandType: CommandType.Text));
            return totBook;
        }
        public Task<List<BookPub>> ListAll(int skip, int take,
               string orderBy, string direction = "DESC",
               string search = "")
        {
            var books = Task.FromResult(_dapperService.
                GetAll<BookPub>($"SELECT b.*, p.Name PubName FROM
                Book b LEFT OUTER JOIN Publisher p ON
                b.PubId=p.Id WHERE Title like '%{search}%'
                ORDER BY {orderBy} {direction} OFFSET {skip}
                ROWS FETCH NEXT {take} ROWS ONLY;",null,
                commandType: CommandType.Text));
            return books;
        }
        public Task<int> Update(Book book)
        {
            var dbPara = new DynamicParameters();
            dbPara.Add("ISBN", book.ISBN, DbType.Int64);
            dbPara.Add("Title", book.Title, DbType.String);
            dbPara.Add("PubYear", book.PubYear, DbType.Int16);
            dbPara.Add("PurchDate", book.PurchDate, DbType.Date);
            dbPara.Add("PubId", book.PubId, DbType.Int32);
            var updateBook = Task.FromResult(_dapperService.
                Update<int>("[dbo].[spUpdateBook]",dbPara,
                commandType:CommandType.StoredProcedure));
            return updateBook;
        }
    }
}

File Komponen *. razor

Ada tiga file razor ditambahkan ke dalam folder Page, yaitu AddBook.razor, EditBook.razor, dan FetchBook.razor. Ketiganya berisi kode untuk I / O halaman CRUD.

AddBook.razor, digunakan untuk membuat data buku baru.

@page "/addBook"
@inject IBookService bookService
@inject IPublisherService publisherService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h3>
   Add Book
</h3>
<form>
   <div class="row">
      <div class="col-md-8">
         <div class="form-group">
            <label for="ISBN" class="control-label">ISBN</label>
            <input for="ISBN" class="form-control"
               @bind="@book.ISBN" onfocus="this.value=''" />
         </div>
         <div class="form-group">
            <label for="Title" class="control-label">Title</label>
            <input for="Title" class="form-control"
               @bind="@book.Title" />
         </div>
         <div class="form-group">
            <label for="PubYear" class="control-label">
               Publication Year</label>
            <input for="PubYear" class="form-control"
               @bind="@book.PubYear" onfocus="this.value=''" />
         </div>
         <div class="form-group">
            <label for="PurchDate" class="control-label">
               Purchase Date</label>
            <input type="date" class="form-control"
               @bind="@book.PurchDate"  onfocus="this.value=''" />
         </div>
         <div class="form-group">
            <label for="Publisher" class="control-label">
               Publisher</label>
            <select for="Publisher" class="form-control"
                    @bind="@book.PubId">
               <option value=0 selected>[Select Publisher]</option>
               @foreach (var publisher in publishers)
               {
                  <option value="@publisher.Id">
                      @publisher.Name</option>
               }
            </select>
         </div>
      </div>
   </div>
   <div class="row">
      <div class="col-md-4">
         <div class="form-group">
             <button type="button" class="btn btn-primary"
                @onclick="() => CreateBook()"> Save </button>
             <button type="button" class="btn btn-warning"
                @onclick="() => cancel()">Cancel</button>
         </div>
      </div>
   </div>
</form>
@code {
   Book book = new Book();
   List<Publisher> publishers = new List<Publisher>();
   protected override async Task OnInitializedAsync()
   {
      book.ISBN = 1234567890123;
      book.PubYear = (short)DateTime.Now.Year;
      book.PurchDate = DateTime.Now;
      publishers = await publisherService.FetchAll();
   }
   protected async Task CreateBook()
   {
      await bookService.Create(book);
      navigationManager.NavigateTo("/booklist");
   }
   void cancel()
   {
      navigationManager.NavigateTo("/booklist");
   }
}

EditBook.razor, digunakan untuk memperbarui data sebuah buku.

@page "/editBook/{isbn:long}"
@inject IBookService bookService
@inject IPublisherService publisherService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h3>
   Edit Book
</h3>
<form>
   <div class="row">
      <div class="col-md-8">
         <div class="form-group">
            <label for="ISBN" class="control-label">ISBN</label>
            <input for="ISBN" class="form-control"
               @bind="@book.ISBN" />
         </div>
         <div class="form-group">
            <label for="Title" class="control-label">Title</label>
            <input for="Title" class="form-control"
               @bind="@book.Title" />
         </div>
         <div class="form-group">
            <label for="PubYear" class="control-label">
               Publication Year</label>
            <input for="PubYear" class="form-control"
               @bind="@book.PubYear" />
         </div>
         <div class="form-group">
            <label for="PurchDate" class="control-label">
               Purchase Date</label>
            <input type="date"  class="form-control"
               @bind="@book.PurchDate" />
         </div>
         <div class="form-group">
            <label for="Publisher" class="control-label">
               Publisher</label>
            <select for="Publisher" class="form-control"
                  @bind="@book.PubId">
               <option value=0 >[Select Publisher]</option>
               @foreach (var publisher in publishers)
               {
                  <option value="@publisher.Id">
                     @publisher.Name</option>
               }
            </select>
         </div>
      </div>
   </div>
   <div class="row">
      <div class="col-md-4">
         <div class="form-group">
            <button type="button" class="btn btn-primary"
               @onclick="() => UpdateBook()"> Save </button>
            <button type="button" class="btn btn-warning"
               @onclick="() => cancel()">Cancel</button>
         </div>
      </div>
   </div>
</form>
@code {
   [Parameter]
   public string isbn { get; set; }
   Book book = new Book();
   List<Publisher> publishers = new List<Publisher>();
   protected override async Task OnInitializedAsync()
   {
      book = await bookService.GetById(isbn);
      publishers = await publisherService.FetchAll();
   }
   protected async Task UpdateBook()
   {
      await bookService.Update(book);
      navigationManager.NavigateTo("/booklist");
   }
   void cancel()
   {
      navigationManager.NavigateTo("/booklist");
   }
}

FetchBook.razor, digunakan untuk menampilkan daftar buku.

@page "/booklist"
@inject IBookService bookService
<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='/addBook'>Add new data</a>
</div>
@if (bookModel == 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("ISBN"))">
               I S B N
               <span class="fa @(SetSortIcon("ISBN"))"></span>
            </th>
            <th class="sort-th"
                  @onclick="@(() => SortTable("Title"))">
               T i t l e
               <span class="fa @(SetSortIcon("Title"))"></span>
            </th>
            <th class="sort-th"
                  @onclick="@(() => SortTable("PubYear"))">
               Pub.<br />Year
               <span class="fa @(SetSortIcon("PubYear"))"></span>
            </th>
            <th class="sort-th"
                  @onclick="@(() => SortTable("PurchDate"))">
               Purchase<br />Date
               <span class="fa @(SetSortIcon("PurchDate"))"></span>
            </th>
            <th class="sort-th"
                  @onclick="@(() => SortTable("PubName"))">
               Publisher
               <span class="fa @(SetSortIcon("PubName"))"></span>
            </th>
            <th>Action</th>
         </tr>
      </thead>
      <tbody>
         @if (bookModel == null || bookModel.Count == 0)
         {
            <tr>
               <td colspan="3">
                  No Records to display
               </td>
            </tr>
         }
         else
         {
            foreach (var book in bookModel)
            {
               <tr>
                  <td>@book.ISBN</td>
                  <td>@book.Title</td>
                  <td>@book.PubYear</td>
                  <td>@book.PurchDate.ToShortDateString()</td>
                  <td>@book.PubName</td>
                  <td>
                     <a class="btn btn-primary"
                        href='/editBook/@book.ISBN'> Edit </a>
                     <a class="btn btn-warning"
                        @onclick="() => DeleteBook(book.ISBN)">
                        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<BookPub> bookModel;
   BookPub bookEntity = new BookPub();
   #region Pagination
   int totalPages;
   int totalRecords;
   int curPage;
   int pagerSize;
   int pageSize;
   int startPage;
   int endPage;
   string sortColumnName = "PurchDate";
   string sortDir = "DESC";
   #endregion
   protected override async Task OnInitializedAsync()
   {
      //display total page buttons
      pagerSize = 3;
      pageSize = 5;
      curPage = 1;
      bookModel = await bookService.ListAll((curPage - 1) *
         pageSize, pageSize, sortColumnName, sortDir, searchTerm);
      totalRecords = await bookService.Count(searchTerm);
      totalPages =(int)Math.Ceiling(totalRecords/(decimal)pageSize);
      SetPagerSize("forward");
   }
   protected async Task DeleteBook(long id)
   {
      await bookService.Delete(id);
      bookModel = await bookService.ListAll((curPage - 1) *
         pageSize, pageSize, sortColumnName, sortDir, searchTerm);
   }
   private bool isSortedAscending;
   private string activeSortColumn;
   private async Task<List<BookPub>>
      SortRecords(string columnName, string dir)
   {
      return await bookService.ListAll((curPage - 1) * pageSize,
         pageSize, columnName, dir, searchTerm);
   }
   private async Task SortTable(string columnName)
   {
      if (columnName != activeSortColumn)
      {
         bookModel = await SortRecords(columnName, "ASC");
         isSortedAscending = true;
         activeSortColumn = columnName;
      }
      else
      {
         if (isSortedAscending)
         {
            bookModel = await SortRecords(columnName, "DESC");
         }
         else
         {
            bookModel = 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)
   {
      bookModel = await bookService.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 Kode

File NavMenu.razor
Ubah 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="booklist">
            <span class="oi oi-book"
               aria-hidden="true"></span> Books
         </NavLink>
      </li>
      <li class="nav-item px-3">
         <NavLink class="nav-link" href="publisherlist">
            <span class="oi oi-list-rich"
               aria-hidden="true"></span> Publishers
         </NavLink>
      </li>
   </ul>
</div>
@code {
   private bool collapseNavMenu = true;
   private string NavMenuCssClass => collapseNavMenu ?
      "collapse" : null;
   private void ToggleNavMenu()
   {
      collapseNavMenu = !collapseNavMenu;
   }
}

Startup.cs
Tambahkan layanan berikut.

//Book service
services.AddScoped<IBookService, BookService>();

Berikut adalah struktur proyek secara keseluruhan.

Referensi

step-by-step-tutorial

Dapper

Executing Stored Procedures With Dapper