Proyek Blazor Server #3
Implementasi checkbox list pada operasi CRUD melibatkan M:N relationship menggunakan Dapper
CRUD (Create, Read, Update, Delete) merupakan fitur yang harus ada pada aplikasi yang menggunakan basis data. Dalam dua artikel sebelumnya, telah dibahas bagaimana penerapan operasi CRUD untuk:
(1) tabel tunggal
(2) dua tabel dengan N:1 (banyak ke satu) relationship.
Artikel ini berfokus pada penerapan checkbox list pada operasi CRUD yang melibatkan relationship M:N (banyak-ke-banyak) Author-write-Book, seperti yang ditunjukkan pada diagram ER berikut.
Relationship Author-write-Book menghasilkan tiga tabel, yaitu Book
, BookAuthor
, dan Author
.
Saya memodifikasi hampir semua kode proyek sebelumnya, Proyek Blazor Server #1 dan Proyek Blazor Server #2, sehingga tampilan komponen halaman jauh lebih rapi, dan nama metode lebih konsisten.
Tinjawan Sekilas Halaman CRUD
Daftar Buku
Berikut adalah tangkapan layar daftar buku yang melibatkan relationship M:N (Author-write-Book) dan relationship 1:N (Publisher-publish-book). Untuk relationship1:N, silakan baca artikel sebelumnya di sini.
Author
di kolom ketiga menunjukkan relationship M:N. Seorang penulis dapat menulis banyak buku; misalnya, David M. Kroenke menulis dua buku. Sebaliknya, satu buku boleh jadi ditulis oleh banyak penulis; misalnya, Database Processing ditulis oleh dua penulis.- Daftar diurutkan menurun berdasarkan
Purchase Date
. Dengan mengklik judul kolomPurchase Date
, urutan berubah dari mengecil menjadi membesar. Hal yang sama berlaku untuk kolom lain. - Tombol navigasi:
◄
pergi ke halaman sebelumnya►
pergi ke halaman berikutnya1
,2
,3
, ... nomor halaman - Untuk mencari buku berdasarkan kriteria judul, ketikkan judul di kotak pencarian. Judul tidak harus lengkap.
- Untuk menambahkan data buku baru, klik
Add new book
. - Untuk mengubah data yang ada, klik
Edit
. - Untuk menghapus, klik
Delete
.
Menambahkan Data Buku
Berikut adalah contoh halaman untuk menambahkan data buku. Belum ada input data untuk penulis. Data penulis dapat diinput setelah buku disimpan.
- Klik
Save
, daftar kotak centang penulis muncul.
- Masukkan urutan penulis dan klik kotak centang, seperti yang ditunjukkan pada gambar di atas.
- Buku ditulis oleh tiga penulis. Penulis ketiga belum ada dalam daftar, jadi klik tombol
Add author
.
- Klik
Save
, penulis baru muncul dalam daftar.
- Sebagaimana terlihat pada gambar di atas, masukkan urutan penulis ketiga dan klik kotak centang.
Mengedit Data Buku
Berikut adalah contoh halaman untuk mengedit data sebuah buku. Anda dapat mengedit field apa pun termasuk primary key ISBN
. Tersedia daftar kotak centang untuk mengedit penulis buku.
Membuat Basis Data BookDB
Pertama-tama, kita perlu menulis dan menjalankan skrip SQL untuk membuat basis data BookDB
, tabel, data, prosedur, dan fungsi.
- Buka SQL Server Management Studio.
- Copy skrip di bawah ini, paste ke editor query.
CREATE DATABASE BookDB
GOUSE BookDB
GOCREATE TABLE Publisher (
[Id] [Int] IDENTITY(0,1),
[Name] [VarChar](20),
[City] [VarChar](20),
[Country] [VarChar](20),
CONSTRAINT [pkPublisher] PRIMARY KEY ([Id])
)
GOCREATE TABLE Author(
[Id] [Int] IDENTITY(1,1),
[FName] [VarChar](20),
[LName] [VarChar](20),
[Phone] [VarChar](16),
CONSTRAINT [pkAuthor] PRIMARY KEY ([Id])
)
GOCREATE TABLE Book(
[ISBN] [BigInt],
[Title] [VarChar](80),
[PubYear] [SmallInt],
[PurchDate] [Date],
[PubId] [Int] DEFAULT 0,
CONSTRAINT [pkBook] PRIMARY KEY ([ISBN]),
CONSTRAINT [fkBookPub] FOREIGN KEY ([PubId])
REFERENCES [dbo].[Publisher]([Id])
ON UPDATE CASCADE ON DELETE SET DEFAULT
)
GOCREATE TABLE BookAuthor(
[ISBN] [BigInt],
[AuthorId] [Int],
[AuthorOrd] [TinyInt],
CONSTRAINT [pkBookAuthor] PRIMARY KEY ([ISBN],[AuthorId]),
CONSTRAINT [fkBookAuthor_Book] FOREIGN KEY([ISBN])
REFERENCES [dbo].[Book] ([ISBN])
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT [fkBookAuthor_Author] FOREIGN KEY ([AuthorId])
REFERENCES [dbo].[Author] ([Id])
ON UPDATE CASCADE ON DELETE CASCADE
)
GOINSERT INTO Publisher ([Name], [City], [Country])
VALUES ('Unknown', NULL, NULL),
('Unsri Press', 'Palembang', 'Indonesia'),
('Gramedia', 'Jakarta', 'Indonesia'),
('Apress', 'New York', 'USA'),
('UIGM Press', 'Palemban g', 'Indonesia'),
('Packt', 'Birmingham', 'UK'),
('Rafah Press', 'Palembang', 'Indonesia'),
('O’Reilly','Sebastopol','USA'),
('Informatika', 'Bandung', 'Indonesia'),
('Pearson', 'London', 'UK'),
('Cengage', 'Boston', 'USA'),
('Noerfikri', 'Palembang', 'Indonesia'),
('Iris Press','Bandung','Indonesia')
GOINSERT INTO Book (ISBN, PubYear, PurchDate, Title, PubId)
VALUES (9789791339957, 2013, '2019-02-12', 'Pranata Sosial', 6),
(9780134802749, 2018, '2020-10-14',
'Database Processing', 9),
(9786024474348, 2019, '2019-10-01',
'Desain Basis Data Akademik Perguruan Tinggi', 11),
(9781305576766, 2015, '2018-12-31',
'NoSQL Web Development with Apache Cassandra', 10),
(9780135191767, 2020, '2020-06-30',
'Using MIS',9),
(9781789619768, 2020, '2020-08-31',
'Modern Web Development with ASP.NET Core 3', 5),
(9789793053585, 2009, '2015-03-25',
'Mengenal Pola Huruf Arab',12),
(9781492056812, 2020, '2020-09-27', 'Programming C# 8.0',7),
(9781783989201, 2015, '2017-07-23',
'Learning Apache Cassandra', 5),
(9781484259276, 2020, '2020-10-17',
'Microsoft Blazor: Building Web App in .NET', 3),
(9786020338682, 2017, '2018-08-10', 'Disruption', 2)
GOINSERT INTO Author ([FName], [LName], [Phone])
VALUES ('Rhenald', 'Kasali', 'Unknown'),
('David M.', 'Kroenke ', 'Unknown'),
('M.', 'Ramadhan', '081122334455'),
('Randall J.', 'Boyle', 'Unknown'),
('Deepak', 'Vohra', 'Unknown'),
('Hamidah', 'Akil', '08123456789'),
('David','Auer','Unknown'),
('Ricardo', 'Peres', 'Unknown'),
('Ian', 'Griffiths', 'Unknown'),
('Mat', 'Brown', 'Unknown'),
('Peter', 'Himschoot', 'Unknown')
GOINSERT INTO BookAuthor ([ISBN],[AuthorId],[AuthorOrd])
VALUES (9780134802749, 2, 1),(9780134802749, 7, 2),
(9789791339957, 6, 1),(9781789619768, 8, 1),
(9781305576766, 5, 1),(9781492056812, 9, 1),
(9780135191767, 2, 1),(9780135191767, 4, 2),
(9786020338682, 1, 1),(9789793053585, 3, 1),
(9786024474348, 3, 1),(9781783989201, 10, 1),
(9781484259276, 11, 1)
GOCREATE PROCEDURE dbo.spAddPublisher
@Name VarChar(20),
@City VarChar(20),
@Country VarChar(20)
AS
BEGIN
DECLARE @Id Int;
INSERT INTO Publisher([Name], [City], [Country])
VALUES (@Name, @City, @Country);
SET @Id = SCOPE_IDENTITY();
END
GOCREATE PROCEDURE dbo.spUpdatePublisher
@Id Int,
@Name VarChar(20),
@City VarChar(20),
@Country VarChar(20)
AS
UPDATE Publisher
SET [Name] = @Name,
[City] = @City,
[Country] = @Country
WHERE [Id] = @Id
GOCREATE PROCEDURE dbo.spAddBook
@ISBN Bigint,
@Title VarChar(80),
@PubYear SmallInt,
@PurchDate Date,
@PubId Int
AS
INSERT INTO dbo.Book (ISBN, Title, PubYear, PurchDate, PubId)
VALUES (@ISBN, @Title, @PubYear, @PurchDate, @PubId)
GOCREATE PROCEDURE dbo.spUpdateBook
@ISBN Bigint,
@Title VarChar(80),
@PubYear SmallInt,
@PurchDate Date,
@PubId Int,
@Pk Bigint
AS
UPDATE Book
SET [ISBN] = @ISBN,
[Title] = @Title,
[PubYear] = @PubYear,
[PurchDate] = @PurchDate,
[PubId] = @PubId
WHERE [ISBN] = @Pk
GOCREATE PROCEDURE dbo.spAddAuthor
@FName VarChar(20),
@LName VarChar(20),
@Phone VarChar(20)
AS
BEGIN
DECLARE @Id as Int;
INSERT INTO Author([FName], [LName], [Phone])
VALUES (@FName, @LName, @Phone);
SET @Id = SCOPE_IDENTITY();
END
GOCREATE PROCEDURE dbo.spUpdateAuthor
@Id Int,
@FName VarChar(20),
@LName VarChar(20),
@Phone VarChar(20)
AS
UPDATE Author
SET [FName] = @FName,
[LName] = @LName,
[Phone] = @Phone
WHERE [Id] = @Id
GOCREATE PROCEDURE dbo.spAddBookAuthor
@ISBN BigInt,
@AuthorId Int,
@AuthorOrd TinyInt
AS
INSERT INTO [dbo].[BookAuthor]([ISBN],[AuthorId],[AuthorOrd])
VALUES (@ISBN, @AuthorId, @AuthorOrd)
GOCREATE PROCEDURE dbo.spUpdateBookAuthor
@ISBN BigInt,
@AuthorId Int,
@AuthorOrd TinyInt
AS
UPDATE BookAuthor
SET [AuthorOrd] = @AuthorOrd
WHERE [ISBN] = @ISBN and AuthorId = @AuthorId
GOCREATE FUNCTION dbo.AuthorOfBook (@ISBN BigInt) RETURNS Table AS
RETURN
SELECT ISBN, AuthorId AuId, AuthorOrd
FROM BookAuthor WHERE ISBN=@ISBN
GOCREATE FUNCTION dbo.BookAuthorName (@ISBN BigInt) Returns Table AS
RETURN
SELECT ISBN, Id AuthorId, AuthorOrd, FName + ' ' + LName AuthorName
FROM Author LEFT OUTER JOIN dbo.AuthorOfBook (@ISBN) On Id = AuId
- Pilih menu:
Query
|Execute
, atau tekanF5
untuk menjalankan skrip di atas. - Perhatikan dua fungsi terakhir di atas. Fungsi
BookAuthorName
memanggil fungsiAuthorOfBook
. KomponenAddBook.razor
menampilkan daftar kotak centang penulis buku dengan memanggilTask<List<BookAuthorName>>
diBookAuthorService.cs
yang menggunakan fungsiBookAuthorName
. Dengan demikian pengguna dapat memilih satu atau lebih penulis buku dari daftar. Hal yang sama juga berlaku untukEditBook.razor
.
Membuat Proyek Blazor Server
1. Buka Visual Studio 2019, buat proyek Blazor Server bernama BookApp
.
2. Hapus lima file yang tidak diperlukan.
3. Hapus baris kode di file terkait yang dihapus.
4. Buat string koneksi basis data.
5. Instal paket Dapper dan Microsoft.EntityFrameworkCore.SqlServer.
Untuk lebih jelasnya silahkan baca artikel sebelumnya di sini.
Berikut ini adalah struktur proyek secara keseluruhan.
File Pendukung Operasi CRUD
Selain skrip SQL di atas, operasi CRUD membutuhkan file entitas, antarmuka, implementasi antarmuka, komponen razor, dan modifikasi kode beberapa file yang ada.
File Entitas
- Ada enam kelas dalam folder
Entities
:Author.cs
,Book.cs
,BookAuthor.cs
,Publisher.cs
,BookAuthorName.cs
, danBookAuPub.cs
, - Empat kelas entitas —
Author.cs
,Book.cs
,BookAuthor.cs
, danPublisher.cs
— memetakan tabel basis data dengan nama yang sama. - Kelas entitas
BookAuthorName.cs
memetakan dua tabel basis data yang dijoinkan,Author
danBookAuthor
. - Kelas entitas
BookAuPub.cs
memetakan empat tabel basis data yang dijoinkan:Book
,Publisher
,BookAuthor
, andAuthor
.
Berikut adalah daftar kodenya.
Author.cs
using System.ComponentModel.DataAnnotations;namespace BookApp.Entities
{
public class Author
{
[Key]
public int Id { get; set; }
public string FName { get; set; }
public string LName { get; set; }
public string Phone { get; set; }
}
}
Book.cs
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; }
}
}
BookAuthor.cs
using System.ComponentModel.DataAnnotations;namespace BookApp.Entities
{
public class BookAuthor
{
[Key]
public long ISBN { get; set; }
public int AuthorId { get; set; }
public byte? AuthorOrd { get; set; }
}
}
Publisher.cs
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; }
}
}
BookAuthorName.cs
namespace BookApp.Entities
{
public class BookAuthorName : BookAuthor
{
public string AuthorName { get; set; }
}
}
BookAuPub.cs
namespace BookApp.Entities
{
public class BookAuPub : Book
{
public string AuthorName { get; set; }
public string PubName { get; set; }
}
}
File Antarmuka
File antarmuka mendeklarasikan metode. Ada lima file antarmuka: IAuthorService.cs
, IBookService.cs
, IBookAuthorService.cs
, IDapperService.cs
, dan IPublisherService.cs
. File-file tersebut ada di dalam folder Interfaces
.
IAuthorService.cs
using BookApp.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;namespace BookApp.Interfaces
{
public interface IAuthorService
{
Task<int> Create(Author author);
Task<Author> ReadByPk(int Id);
Task<int> Update(Author author);
Task<int> Delete(int Id);
Task<int> Count(string search);
Task<List<Author>> ListAll(int skip,
int take,
string orderBy,
string direction,
string search);
}
}
IBookService.cs
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<Book> ReadByPk(long isbn);
Task<int> Update(Book book, long pk);
Task<int> Delete(long id);
Task<int> Count(string search);
Task<List<BookAuPub>> ListAll(int skip,
int take,
string orderBy,
string direction,
string search);
}
}
IBookAuthorService.cs
using BookApp.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;namespace BookApp.Interfaces
{
public interface IBookAuthorService
{
Task<int> Create(BookAuthorName bookAuthorNamer);
Task<int> Delete(long isbn, int authorId);
Task<List<BookAuthorName>> FetchAll(long isbn);
}
}
IDapperService.cs
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
using BookApp.Entities;
using System.Collections.Generic;
using System.Threading.Tasks;namespace BookApp.Interfaces
{
public interface IPublisherService
{
Task<int> Create(Publisher publisher);
Task<Publisher> ReadByPk(int Id);
Task<int> Update(Publisher publisher);
Task<int> Delete(int Id);
Task<int> Count(string search);
Task<List<Publisher>> FetchAll();
Task<List<Publisher>> ListAll(int skip,
int take,
string orderBy,
string direction,
string search);
}
}
File Metode
- File metode mengimplementasikan metode yang dideklarasikan dalam file antarmuka.
- Ada lima file metode:
AuthorService.cs
,BookService.cs
,BookAuthorService.cs
,DapperService.cs
, danPublisherService.cs
. - Ada juga file
AppContext.cs
yang berisi informasi dan konfigurasi untuk mengakses basis data. - Keenam file tersebut ada di dalam folder
Data
.
AuthorService.cs
AuthorService.cs
mengimplementasikan metode yang dideklarasikan dalam fileIAuthorService.cs
.
using BookApp.Interfaces;
using BookApp.Entities;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;namespace BookApp.Data
{
public class AuthorService : IAuthorService
{
private readonly IDapperService _dapperService; public AuthorService(IDapperService dapperService)
{
this._dapperService = dapperService;
} public Task<int> Create(Author author)
{
var dbPara = new DynamicParameters();
dbPara.Add("FName", author.FName, DbType.String);
dbPara.Add("LName", author.LName, DbType.String);
dbPara.Add("Phone", author.Phone, DbType.String);
var authorId = Task.FromResult(_dapperService.Insert<int>
("[dbo].[spAddAuthor]", dbPara,
commandType: CommandType.StoredProcedure));
return authorId;
} public Task<Author> ReadByPk(int id)
{
var author = Task.FromResult(_dapperService.Get<Author>
($"select * from [Author] where Id = {id}", null,
commandType: CommandType.Text));
return author;
} public Task<int> Update(Author author)
{
var dbPara = new DynamicParameters();
dbPara.Add("Id", author.Id);
dbPara.Add("FName", author.FName, DbType.String);
dbPara.Add("LName", author.LName, DbType.String);
dbPara.Add("Phone", author.Phone, DbType.String);
var updateAuthor = Task.FromResult(_dapperService.
Update<int>("[dbo].[spUpdateAuthor]", dbPara,
commandType: CommandType.StoredProcedure));
return updateAuthor;
} public Task<int> Delete(int id)
{
var deleteAuthor = Task.FromResult(_dapperService.Execute
($"Delete [Author] where Id = {id}", null,
commandType: CommandType.Text));
return deleteAuthor;
} public Task<int> Count(string search)
{
var totAuthor = Task.FromResult(_dapperService.Get<int>
($"select COUNT(*) from [Author] WHERE LName like " +
$"'%{search}%'", null, commandType: CommandType.Text));
return totAuthor;
} public Task<List<Author>> ListAll (int skip, int take,
string orderBy, string direction = "DESC",
string search = "")
{
var authors = Task.FromResult(_dapperService.GetAll<Author>
($"SELECT * FROM [Author] WHERE LName like " +
$"'%{search}%' ORDER BY {orderBy} {direction} " +
$"OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY; ",
null, commandType: CommandType.Text));
return authors;
}
}
}
BookService.cs
BookService.cs
mengimplementasikan metode yang dideklarasikan dalam fileIBookService.cs
.
using BookApp.Interfaces;
using BookApp.Entities;
using Dapper;
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> ReadByPk(long isbn)
{
var book = Task.FromResult(_dapperService.Get<Book>
($"select * from [Book] where ISBN = {isbn}",
null, commandType: CommandType.Text));
return book;
} public Task<int> Update(Book book, long pk)
{
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);
dbPara.Add("Pk", pk, DbType.Int64);
var updateBook = Task.FromResult(_dapperService.
Update<int>("[dbo].[spUpdateBook]", dbPara,
commandType: CommandType.StoredProcedure));
return updateBook;
} 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<BookAuPub>> ListAll (int skip,
int take, string orderBy, string direction = "DESC",
string search = "")
{
var books = Task.FromResult(_dapperService.
GetAll<BookAuPub>($"SELECT B.*, FName + ' ' + LName " +
$"AuthorName, P.Name PubName FROM Book B LEFT OUTER JOIN" +
$" Publisher P ON B.PubId=P.Id LEFT OUTER JOIN " +
$"BookAuthor BA ON B.ISBN = BA.ISBN LEFT OUTER JOIN " +
$"Author A ON BA.AuthorId = A.Id " +
$"WHERE Title like '%{search}%' ORDER BY {orderBy} " +
$"{direction} OFFSET {skip} ROWS FETCH NEXT {take} " +
$"ROWS ONLY;", null, commandType: CommandType.Text));
return books;
}
}
}
BookAuthorService.cs
BookAuthorService.cs
mengimplementasikan metode yang dideklarasikan dalam fileIBookAuthorService.cs
.
using BookApp.Interfaces;
using BookApp.Entities;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;namespace BookApp.Data
{
public class BookAuthorService : IBookAuthorService
{
private readonly IDapperService _dapperService; public BookAuthorService(IDapperService dapperService)
{
this._dapperService = dapperService;
} public Task<int> Create(BookAuthorName bookAuthorName)
{
var dbPara = new DynamicParameters();
dbPara.Add("ISBN", bookAuthorName.ISBN, DbType.Int64);
dbPara.Add("AuthorId", bookAuthorName.AuthorId,
DbType.Int32);
dbPara.Add("AuthorOrd", bookAuthorName.AuthorOrd,
DbType.Byte);
var bookAuthorId = Task.FromResult(_dapperService.
Insert<int>("[dbo].[spAddBookAuthor]", dbPara,
commandType: CommandType.StoredProcedure));
return bookAuthorId;
} public Task<int> Delete(long isbn, int authorId)
{
var deleteBookAuthor = Task.FromResult(_dapperService.
Execute($"Delete [BookAuthor] where ISBN = {isbn} " +
$"and AuthorId = {authorId}", null,
commandType: CommandType.Text));
return deleteBookAuthor;
} public Task<List<BookAuthorName>> FetchAll(long isbn)
{
var bookAuthorNames = Task.FromResult(_dapperService.GetAll
<BookAuthorName>($"select * from BookAuthorName " +
$"({isbn}) order by AuthorName; ", null,
commandType: CommandType.Text));
return bookAuthorNames;
}
}
}
DapperService.cs
DapperService.cs
mengimplementasikan metode yang dideklarasikan dalam fileIDapperService.cs
.
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
PublisherService.cs
mengimplementasikan metode yang dideklarasikan dalam fileIPublisherService.cs
.
using BookApp.Interfaces;
using BookApp.Entities;
using Dapper;
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> ReadByPk(int id)
{
var publisher = Task.FromResult(_dapperService.Get
<Publisher>($"select * from [Publisher] where Id = {id}",
null, commandType: CommandType.Text));
return publisher;
} 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;
} 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>> FetchAll()
{
var publishers = Task.FromResult
(_dapperService.GetAll<Publisher>
($"SELECT * FROM [Publisher] ORDER BY Name; ",
null, commandType: CommandType.Text));
return publishers;
} 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;
}
}
}
File Komponen *. razor
Ada sembilan file razor di dalam folder Page
: AddAuthor.razor
, AddBook.razor
, AddPublisher.razor
,EditAuthor.razor
,EditBook.razor
, EditPublisher.razor
, ListAuthor.razor
, ListBook.razor
, dan ListPublisher.razor
. Kesembilan file razor berisi kode I/O.
AddAuthor.razor
- Menampilkan halaman untuk menambah data pengarang.
- Dapat dipanggil dari:
(1) menu (NavMenu.razor
), or
(2) halaman daftar buku(ListBook.razor
)
@page "/addAuthor/{isbnDummy:long}"
@inject IAuthorService authorService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h3>
Add Author
</h3><form>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tbody>
<tr>
<td>
<label for="FName" class="control-label">
First Name
</label>
</td>
<td>
<input for="FName" class="form-control"
@bind="@author.FName" />
</td>
</tr>
<tr>
<td>
<label for="LName" class="control-label">
Last Name
</label>
</td>
<td>
<input for="LName" class="form-control"
@bind="@author.LName" />
</td>
</tr>
<tr>
<td>
<label for="Phone" class="control-label">
Phone
</label>
</td>
<td>
<input for="Phone" class="form-control"
@bind="@author.Phone" />
</td>
</tr>
<tr>
<td></td>
<td>
<br />
<button type="button" class="btn btn-primary"
@onclick="() => CreateAuthor()">
 Save 
</button> 
<button type="button" class="btn btn-warning"
@onclick="() => Cancel()">
  Cancel  
</button>
</td>
</tr>
</tbody>
</table>
</form>@code { [Parameter]
public long isbnDummy { get; set; } Author author = new Author(); protected async Task CreateAuthor()
{
await authorService.Create(author);
Cancel();
} void Cancel()
{
if (isbnDummy > 0)
navigationManager.NavigateTo("/editBook/" + isbnDummy);
else
navigationManager.NavigateTo("/listAuthor");
}
}
AddBook.razor
- Digunakan untuk menambahkan data sebuah buku.
- Perhatikan teks tebal pada kode di bawah ini. Daftar kotak centang penulis muncul jika dan hanya jika data buku baru telah disimpan.
- Daftar tersebut terdiri atas tiga kolom. Yang pertama adalah urutan penulis buku, yang kedua adalah kotak centang, dan yang ketiga adalah nama penulis.
- Perubahan pada kotak centang akan memicu metode
CheckChanged
pada blok@Code
C#. Centang kotak untuk memasukkan penulis buku atau hapus centang untuk mengeluarkannya.
@page "/addBook"
@inject IBookService bookService
@inject IPublisherService publisherService
@inject IBookAuthorService bookAuthorService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h3>
Add Book
</h3><form>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tbody>
<tr>
<td><label for="ISBN" class="control-label">ISBN</label>
</td>
<td><input for="ISBN" class="form-control"
@bind="@book.ISBN" /></td>
</tr>
<tr>
<td><label for="Title" class="control-label">
Title</label></td>
<td><input for="Title" class="form-control"
@bind="@book.Title" /></td>
</tr>
@if (@hasAdded)
{
<tr>
<td><label for="Authors" class="control-label">
Authors</label></td>
<td><label style="width: 95px;">
<u><i><b>Sequence</b></i></u></label>
<label><u><i><b>Full Name</b></i></u></label><br/>
@foreach (var bookAuthorName in bookAuthorNames)
{
<input type="number"
@bind="@bookAuthorName.AuthorOrd"
@bind:event="oninput"
style="width: 75px;" min="0" max="5" />
@if (@bookAuthorName.ISBN > 0)
{
<input name="AreChecked" type="checkbox"
value="@bookAuthorName.AuthorId" checked
@onchange = "eventArgs => { CheckChanged
(bookAuthorName, eventArgs.Value);}" />
}
else
{
<input name="AreChecked" type="checkbox"
value="@bookAuthorName.AuthorId"
@onchange="eventArgs => { CheckChanged
(bookAuthorName, eventArgs.Value);}" />
}
<label for="AuthorName" style="width: 150px;">
@bookAuthorName.AuthorName</label><br />
}
</td>
<td>
 
<a class="btn btn-primary"
href='/addAuthor/@book.ISBN'>
 Add author 
</a><br />
</td>
</tr>
}
<tr>
<td><label for="PubYear" class="control-label">
Publication Year</label></td>
<td><input for="PubYear" class="form-control"
@bind="@book.PubYear" /></td>
</tr>
<tr>
<td><label for="PurchDate" class="control-label">
Purchase Date</label></td>
<td><input type="date" class="form-control"
@bind="@book.PurchDate" /></td>
</tr>
<tr>
<td><label for="Publisher" class="control-label">
Publisher</label></td>
<td>
<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>
</td>
<td>
 
<a class="btn btn-primary"
href='/addPublisher/@book.ISBN'>Add publisher</a>
</td>
</tr>
<tr>
<td></td>
<td>
<br />
<button type="button" class="btn btn-primary"
@onclick="() => CreateBook()">
 Save 
</button> 
<button type="button" class="btn btn-warning"
@onclick="() => Cancel()">
  Cancel  
</button>
</td>
</tr>
</tbody>
</table>
</form>@code { Book book = new Book();
BookAuthorName bookAuthorName = new BookAuthorName();
List<Publisher> publishers = new List<Publisher>();
List<BookAuthorName>bookAuthorNames = new List<BookAuthorName>();
List<Book> books = new List<Book>();
bool hasAdded = false; protected override async Task OnInitializedAsync()
{
//book.PubYear = (short)DateTime.Now.Year;
book.PurchDate = DateTime.Now;
publishers = await publisherService.FetchAll();
} protected async Task CreateBook()
{
if (hasAdded)
{
navigationManager.NavigateTo("/listBook");
}
else
{
await bookService.Create(book);
bookAuthorNames = await bookAuthorService.FetchAll(0);
hasAdded = !hasAdded;
}
}protected async Task CheckChanged(BookAuthorName bookAuthorName,
object checkValue)
{
long isbn = 0;
if (book.ISBN > isbn)
{
isbn = (long)book.ISBN;
bookAuthorNames = await bookAuthorService.FetchAll(isbn);
if ((bool)checkValue)
{
// insert
bookAuthorName.ISBN = isbn;
await bookAuthorService.Create(bookAuthorName);
}
else
{
//delete
bookAuthorName.AuthorOrd = 0;
await bookAuthorService.Delete
(isbn, bookAuthorName.AuthorId);
}
bookAuthorNames = await bookAuthorService.FetchAll(isbn);
}
} void Cancel()
{
navigationManager.NavigateTo("/listBook");
}
}
AddPublisher.razor
- Merupakan komponen halaman untuk menambahkan penulis.
- Dapat dipanggil dari:
(1) menu (NavMenu.razor
), or
(2) halaman daftar buku(ListBook.razor
)
@page "/addPublisher/{isbnDummy:long}"
@inject IPublisherService publisherService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h3>
Add Publisher
</h3><form>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tbody>
<tr>
<td>
<label for="Name" class="control-label">Name</label>
</td>
<td>
<input for="Name" class="form-control"
@bind="@publisher.Name" />
</td>
</tr>
<tr>
<td>
<label for="City" class="control-label">City</label>
</td>
<td>
<input for="City" class="form-control"
@bind="@publisher.City" />
</td>
</tr>
<tr>
<td>
<label for="Country" class="control-label">
Country
</label>
</td>
<td>
<input for="Country" class="form-control"
@bind="@publisher.Country" />
</td>
</tr>
<tr>
<td></td>
<td>
<br />
<button type="button" class="btn btn-primary"
@onclick="() => CreatePublisher()">
 Save 
</button> 
<button type="button" class="btn btn-warning"
@onclick="() => Cancel()">
  Cancel  
</button>
</td>
</tr>
</tbody>
</table>
</form>@code {
[Parameter]
public long isbnDummy { get; set; } Publisher publisher = new Publisher(); protected async Task CreatePublisher()
{
await publisherService.Create(publisher);
Cancel();
} void Cancel()
{
if (isbnDummy > 0)
navigationManager.NavigateTo("/editBook/" + isbnDummy);
else
navigationManager.NavigateTo("/listPublisher");
}
}
EditAuthor.razor
- Komponen halaman untuk menambah data penulis.
@page "/editAuthor/{id:int}"
@inject IAuthorService authorService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h2>
Edit Author
</h2><form>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tbody>
<tr>
<td>
<label for="FName" class="control-label">
First Name
</label>
</td>
<td>
<input for="FName" class="form-control"
@bind="@author.FName" />
</td>
</tr>
<tr>
<td>
<label for="LName" class="control-label">
Last Name
</label>
</td>
<td>
<input for="LName" class="form-control"
@bind="@author.LName" />
</td>
</tr>
<tr>
<td>
<label for="Phone" class="control-label">
Phone
</label>
</td>
<td>
<input for="Phone" class="form-control"
@bind="@author.Phone" />
</td>
</tr>
<tr>
<td></td>
<td>
<br />
<button type="button" class="btn btn-primary"
@onclick="() => UpdateAuthor()">
 Save 
</button> 
<button type="button" class="btn btn-warning"
@onclick="() => Cancel()">
  Cancel  
</button>
</td>
</tr>
</tbody>
</table>
</form>@code { [Parameter]
public int id { get; set; } Author author = new Author(); protected override async Task OnInitializedAsync()
{
author = await authorService.ReadByPk(id);
} protected async Task UpdateAuthor()
{
await authorService.Update(author);
navigationManager.NavigateTo("/listAuthor");
} void Cancel()
{
navigationManager.NavigateTo("/listAuthor");
}
}
EditBook.razor
- Perhatikan teks tebal pada kode di bawah ini. Kode tersebut untuk menampilkan daftar kotak centang penulis buku.
- Daftar tersebut terdiri atas tiga kolom. Yang pertama adalah urutan penulis buku, yang kedua adalah kotak centang, dan yang ketiga adalah nama penulis.
- Perubahan pada kotak centang akan memicu metode
CheckChanged
di blok@Code
C#. Centang kotak untuk menyertakan penulis buku atau hapus centang untuk mengeluarkannya.
@page "/editBook/{isbn:long}"
@inject IBookService bookService
@inject IPublisherService publisherService
@inject IBookAuthorService bookAuthorService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h3>
Edit Book
</h3><form>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tbody>
<tr>
<td>
<label for="ISBN" class="control-label">
ISBN
</label>
</td>
<td><input for="ISBN" class="form-control"
@bind="@book.ISBN" /></td>
</tr>
<tr>
<td><label for="Title" class="control-label">
Title</label></td>
<td><input for="Title" class="form-control"
@bind="@book.Title" /></td>
</tr>
<tr>
<td><label for="Authors" class="control-label">
Authors</label></td>
<td>
<label style="width: 95px;">
<u><i><b>Sequence</b></i></u></label>
<label><u><i><b>Full Name</b></i></u></label><br />
@foreach (var bookAuthorName in bookAuthorNames)
{
<input type="number"
@bind="@bookAuthorName.AuthorOrd"
@bind:event="oninput"
style="width: 75px;" min="0" max="5" />
@if (@bookAuthorName.ISBN > 0)
{
<input name="AreChecked" type="checkbox"
value="@bookAuthorName.AuthorId" checked
@onchange="eventArgs => { CheckChanged
(bookAuthorName, eventArgs.Value);}" />
}
else
{
<input name="AreChecked" type="checkbox"
value="@bookAuthorName.AuthorId"
@onchange="eventArgs => { CheckChanged
(bookAuthorName, eventArgs.Value);}" />
}
<label for="AuthorName" style="width: 150px;">
@bookAuthorName.AuthorName</label><br />
}
</td>
<td>
 
<a class="btn btn-primary"
href='/addAuthor/@book.ISBN'>
 Add author </a><br />
</td>
</tr>
<tr>
<td><label for="PubYear" class="control-label">
Publication Year</label></td>
<td><input for="PubYear" class="form-control"
@bind="@book.PubYear" /></td>
</tr>
<tr>
<td><label for="PurchDate" class="control-label">
Purchase Date</label></td>
<td><input type="date" class="form-control"
@bind="@book.PurchDate" /></td>
</tr>
<tr>
<td><label for="Publisher" class="control-label">
Publisher</label></td>
<td>
<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>
</td>
<td>
 
<a class="btn btn-primary"
href='/addPublisher/@book.ISBN'>Add publisher</a>
</td>
</tr>
<tr>
<td></td>
<td>
<br />
<button type="button" class="btn btn-primary"
@onclick="() => UpdateBook()">
 Save 
</button> 
<button type="button" class="btn btn-warning"
@onclick="() => Cancel()">
  Cancel  
</button>
</td>
</tr>
</tbody>
</table>
</form>@code { [Parameter] public long isbn { get; set; } Book book = new Book();
BookAuthorName bookAuthorName = new BookAuthorName();
List<Publisher> publishers = new List<Publisher>();
List<BookAuthorName>bookAuthorNames = new List<BookAuthorName>(); protected override async Task OnInitializedAsync()
{
book = await bookService.ReadByPk(isbn);
bookAuthorNames = await bookAuthorService.FetchAll(isbn);
publishers = await publisherService.FetchAll();
} protected async Task CheckChanged(BookAuthorName bookAuthorName,
object checkValue)
{
if ((bool)checkValue)
{
// insert
bookAuthorName.ISBN = isbn;
await bookAuthorService.Create(bookAuthorName);
}
else
{
// delete
bookAuthorName.AuthorOrd = null;
await bookAuthorService.Delete
(isbn, bookAuthorName.AuthorId);
}
bookAuthorNames = await bookAuthorService.FetchAll(isbn);
} protected async Task UpdateBook()
{
await bookService.Update(book, isbn);
navigationManager.NavigateTo("/listBook");
} void Cancel()
{
navigationManager.NavigateTo("/listBook");
}
}
EditPublisher.razor
- Merupakan komponen untuk mengedit data penerbit.
@page "/editPublisher/{id:int}"
@inject IPublisherService publisherService
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h2>
Edit Publisher
</h2><form>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tbody>
<tr>
<td>
<label for="Name" class="control-label">
Name
</label>
</td>
<td>
<input for="Name" class="form-control"
@bind="@publisher.Name" />
</td>
</tr>
<tr>
<td>
<label for="City" class="control-label">
City
</label>
</td>
<td>
<input for="City" class="form-control"
@bind="@publisher.City" />
</td>
</tr>
<tr>
<td>
<label for="Country" class="control-label">
Country
</label>
</td>
<td>
<input for="Country" class="form-control"
@bind="@publisher.Country" />
</td>
</tr>
<tr>
<td></td>
<td>
<br />
<button type="button" class="btn btn-primary"
@onclick="() => UpdatePublisher()">
 Save 
</button> 
<button type="button" class="btn btn-warning"
@onclick="() => Cancel()">
  Cancel  
</button>
</td>
</tr>
</tbody>
</table>
</form>@code { [Parameter]
public int id { get; set; } Publisher publisher = new Publisher(); protected override async Task OnInitializedAsync()
{
publisher = await publisherService.ReadByPk(id);
} protected async Task UpdatePublisher()
{
await publisherService.Update(publisher);
navigationManager.NavigateTo("/listPublisher");
} void Cancel()
{
navigationManager.NavigateTo("/listPublisher");
}
}
ListAuthor.razor
- Komponen halaman menampilkan daftar penulis
@page "/listAuthor"
@inject IAuthorService authorService<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><a class="btn btn-primary" href='/addAuthor/0'>Add new author</a>@if (authors == 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("FName"))">First Name
<span class="fa @(SetSortIcon("FName"))"></span>
</th>
<th class="sort-th"
@onclick="@(() => SortTable("LName"))">Last Name
<span class="fa @(SetSortIcon("LName"))"></span>
</th>
<th class="sort-th"
@onclick="@(() => SortTable("Phone"))">Phone
<span class="fa @(SetSortIcon("Phone"))"></span>
</th>
<th>Action</th>
</tr>
</thead>
<tbody>
@if (authors == null || authors.Count == 0)
{
<tr>
<td colspan="3">
No Records to display
</td>
</tr>
}
else
{
foreach (var author in authors)
{
<tr>
<td><hr style="padding:0px; margin:0px;">
@author.Id
</td>
<td><hr style="padding:0px; margin:0px;">
@author.FName
</td>
<td><hr style="padding:0px; margin:0px;">
@author.LName
</td>
<td><hr style="padding:0px; margin:0px;">
@author.Phone
</td>
<td><hr style="padding:0px; margin:0px;">
<a class="btn btn-primary"
href='/editAuthor/@author.Id'>
 Edit </a> 
<a class="btn btn-warning" @onclick="() =>
DeleteAuthor(author.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<Author> authors; #region Pagination int totalPages;
int totalRecords;
int curPage;
int pagerSize;
int pageSize;
int startPage;
int endPage;
string sortColumnName = "Id";
string sortDir = "ASC"; #endregion protected override async Task OnInitializedAsync()
{
//display total page buttons
pagerSize = 3;
pageSize = 5;
curPage = 1;
authors = await authorService.ListAll((curPage - 1) *
pageSize, pageSize, sortColumnName, sortDir, searchTerm);
totalRecords = await authorService.Count(searchTerm);
totalPages = (int)Math.Ceiling
(totalRecords / (decimal)pageSize);
SetPagerSize("forward");
} protected async Task DeleteAuthor(int id)
{
await authorService.Delete(id);
authors = await authorService.ListAll((curPage - 1) *
pageSize, pageSize, sortColumnName, sortDir, searchTerm);
} private bool isSortedAscending;
private string activeSortColumn; private async Task<List<Author>>
SortRecords(string columnName, string dir)
{
return await authorService.ListAll((curPage - 1) * pageSize,
pageSize, columnName, dir, searchTerm);
} private async Task SortTable(string columnName)
{
if (columnName != activeSortColumn)
{
authors = await SortRecords(columnName, "ASC");
isSortedAscending = true;
activeSortColumn = columnName;
}
else
{
if (isSortedAscending)
{
authors = await SortRecords(columnName, "DESC");
}
else
{
authors = 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)
{
authors = await authorService.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();
}
}
ListBook.razor
- Menampilkan daftar buku termasuk penulis dan penerbitnya.
@page "/listBook"
@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><a class="btn btn-primary" href='/addBook'>Add new book</a>@if (books == null)
{
<p><em>Loading...</em></p>
}
else
{
<div class="row col-md-3 pull-right">
<input type="text" id="txtSearch"
placeholder="Search Titles..." 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("AuthorName"))">Author
<span class="fa @(SetSortIcon("AuthorName"))"></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 (books == null || books.Count == 0)
{
<tr>
<td colspan="3">
No Records to display
</td>
</tr>
}
else
{
long prevISBN = 0;
foreach (var book in books)
{
if (@book.ISBN != prevISBN)
{
<tr>
<td><hr style="padding:0px; margin:0px;">
@book.ISBN</td>
<td><hr style="padding:0px; margin:0px;">
@book.Title</td>
<td><hr style="padding:0px; margin:0px;">
@book.AuthorName</td>
<td><hr style="padding:0px; margin:0px;">
@book.PubYear</td>
<td><hr style="padding:0px; margin:0px;">
@book.PurchDate.ToShortDateString()</td>
<td><hr style="padding:0px; margin:0px;">
@book.PubName</td>
<td><hr style="padding:0px; margin:0px;">
<a class="btn btn-primary"
href='/editBook/@book.ISBN'>
 Edit </a> 
<a class="btn btn-warning" @onclick="() =>
DeleteBook((long)book.ISBN)">Delete</a>
</td>
</tr>
}
else
{
<tr>
<td></td>
<td></td>
<td><hr style="padding:0px; margin:0px;">
@book.AuthorName</td>
</tr>
}
prevISBN = (long)@book.ISBN;
}
}
</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<BookAuPub> books; #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 = 8;
curPage = 1;
books = 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 isbn)
{
await bookService.Delete(isbn);
books = await bookService.ListAll((curPage - 1) * pageSize,
pageSize, sortColumnName, sortDir, searchTerm);
} private bool isSortedAscending;
private string activeSortColumn; private async Task<List<BookAuPub>> 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)
{
books = await SortRecords(columnName, "ASC");
isSortedAscending = true;
activeSortColumn = columnName;
}
else
{
if (isSortedAscending)
{
books = await SortRecords(columnName, "DESC");
}
else
{
books = 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)
{
books = 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();
}
}
ListPublisher.razor
- Komponen halaman menampilkan daftar penerbit.
@page "/listPublisher"
@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><a class="btn btn-primary" href='/addPublisher/0'>
Add new publisher</a>@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> <hr style="padding:0px; margin:0px;">
@publisher.Id
</td>
<td><hr style="padding:0px; margin:0px;">
@publisher.Name
</td>
<td><hr style="padding:0px; margin:0px;">
@publisher.City
</td>
<td><hr style="padding:0px; margin:0px;">
@publisher.Country
</td>
<td><hr style="padding:0px; margin:0px;">
<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 = "ASC"; #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 Kode
File NavMenu.razor
Modifikasi kodenya sehingga menjadi seperti 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="listBook">
<span class="oi oi-book" aria-hidden="true"></span>
Books
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="listPublisher">
<span class="oi oi-list-rich" aria-hidden="true"></span>
Publishers
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="listAuthor">
<span class="oi oi-list-rich" aria-hidden="true"></span>
Authors
</NavLink>
</li>
</ul>
</div>@code {
private bool collapseNavMenu = true; private string NavMenuCssClass =>
collapseNavMenu ? "collapse" : null; private void ToggleNavMenu()
{
collapseNavMenu = !collapseNavMenu;
}
}
_Imports.razor
Tambah yang berikut namespace .
@using BookApp
@using BookApp.Shared
@using BookApp.Data
@using BookApp.Interfaces
@using BookApp.Entities
Startup.cs
Tambahkan namespace berikut.
using BookApp.Interfaces;
using BookApp.Data;
Tambahkan layanan berikut .
//BookAuthor service
services.AddScoped<IBookAuthorService, BookAuthorService>();
//Author service
services.AddScoped<IAuthorService, AuthorService>();
//Publisher service
services.AddScoped<IPublisherService, PublisherService>();
//Book service
services.AddScoped<IBookService, BookService>();
//Register dapper in scope
services.AddScoped<IDapperService, DapperService>();
Mungkin ada cara yang lebih baik untuk mengimplementasikan daftar kotak centang pada operasi CRUD yang melibatkan hubungan M: N (banyak ke banyak). Tolong beritahu saya jika Anda mengetahuinya.
Tulisan berikutnya akan membahas tentang perutean dan navigasi URL.