Blazor Server Project #2: How to Implement a Dropdown List

On CRUD operation involves an N:1 (many-to-one) relationship using Dapper with SQL Server database.

This article is the second in a series covering the Blazor Server Project: (1) How to create a CRUD operation using Dapper (2) Building a dropdown list involves a 1:N relationship (3) How to implement a checkbox list involving an M:N relationships (4) Understanding URL routing and navigation (5) Creating and using page layout (6) How to create a reusable modal dialog component (7) Practical guide to making a master-detail page (8) Master-detail page using dynamic query (9) How to avoid SQL injection attacks (10) Hiding/showing HTML elements (11) Migrate to ASP.NET Core 6.0 (12) Installing ASP.NET Core Identity (13) Integrating Identity tables into the existing project database (14) Authentication and authorization (15) Role-based authorization (16) How to implement Google Authentication (17) Facebook Authentication and Authorization (18) How to configure Twitter Authentication (19) How to Customize Password Policy (20) Account Lockout Policy

These articles are for anyone who wants to learn how to build Blazor Server applications in a practical approach through some sample projects. It will be straightforward if you have some experience with C#, HTML, CSS, and SQL.

This article discusses how to implement a dropdown list on CRUD operation involving the two tables, Book table and Publishertable, with the following N:1 (many-to-one) relationship.

Below is a page of the book list involving Book table and Publishertable. The first four columns are book attributes, and the fifth column is the publisher attribute.

By default, the list is sorted in descending order by Purchase Date. You can change the order from descending to ascending or vice versa by clicking on the column headings. The same goes for the other columns.
Navigation button: ◄ go to the previous page ► go to the next page 1, 2, 3, ... page number
To search books based on title criterium, type a title in the search box. The title does not have to complete.
To add new data, click Add new data
To change existing data, click Edit
To delete, click Delete

Below is a page to add a book involving Book table and Publishertable. All data input is book attributes. Pay attention to the last data input, Publisher. It is input by selecting the options from the dropdown list of the Publisher.

The following is a page to edit a book involving Book and Publishertables. All data is book attributes. Pay attention to the last data, Publisher. It is updated by selecting the options from the dropdown list of the Publisher.

Book Table: Adding Data and Creating Stored Procedures

First of all, we need to write and execute SQL scripts for (1) adding data, (2) creating a stored procedure for adding new data, and (3) creating a stored procedure for updating data.

Open SSMS.

Select the server name, click Connect

Open a new query editor by pressing Ctrl+N or selecting menu: File| New|Query with Current Connection
Copy the scripts, and paste them into the query editor.

USE [BookDB]
GO

INSERT INTO dbo.Book(ISBN,PubYear,PurchDate,Title,PubId)
VALUES (9789791339957, 2013, '2019-10-01', 'Pranata Sosial', 6),
       (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-09-30',
        'Beginning Database Programming Using ASP.NET Core 3', 3),
       (9781484231258, 2018, '2018-11-25',
        'Expert Apache Cassandra Administration', 3),
       (9781789619768, 2020, '2020-03-25',
        'Modern Web Development with ASP.NET Core 3', 5),
       (9789793053585, 2009, NULL,'Mengenal Pola Huruf Arab',12),
       (9781492056812, 2020, '2020-06-30',
        'Programming C# 8.0', 7),
       (9781783989201, 2015, '2017-07-23',
        'Learning Apache Cassandra', 5),
       (9781484259276, 2020, '2020-08-07',
        'Microsoft Blazor: Building Web App in .NET', 3),
       (9786020338682, 2017, '2018-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

Select menu: Query| Execute or press the F5 key to execute the script above.

Files Supporting CRUD Operation

Besides the SQL script above, CRUD operation needs files of entities, interfaces, implementation of interfaces, razor components, and code modification of some existing files.

Entity Files

There are two entity files, Book.cs and BookPub.cs, placed in the Entities folder.

Book.cs The Book.cs maps the Books table in the database.

On the Solution Explorer window, right-click Entities, then click Add|Class
Type Book.csas the file name, click Add.
Click Book.cs to open the file, then copy and paste the following code.

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 The BookPub.cs is a mapping of the joining Book table and Publisher table. The BookPub.cs inherits properties from Book.cs. Copy and paste the following code.

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; }
    }
}

Interface File

IBookService.cs

IBookService.cs declares book methods, placed in the Interfaces folder.
In the Interfaces folder, create the IBookService.cs file, then copy and paste the following code.

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);
    }
}

IPublisherService.cs

IPublisherService.cs declares publisher methods, placed in the Interfaces folder.
Add the following code into IPublisherService.cs.

Task<List<Publisher>> FetchAll();

So, the complete IPublisherService.cs is as follows.

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);
        Task<List<Publisher>> FetchAll();
    }
}

Method File

BookService.cs

BookService.cs implements the methods declared in the IBookService.cs file, placed in the Data folder.
In the Data folder, create the BookService.cs file, then copy and paste the following code.

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;
        }
    }
}

PublisherService.cs

PublisherService.cs implements the methods declared in the IPublisherService.cs file, placed in the Data folder.
Add the following code into PublisherService.cs.

    public Task<List<Publisher>> FetchAll()
    {
        var publishers = Task.FromResult
            (_dapperService.GetAll<Publisher>
            ($"SELECT * FROM [Publisher] ORDER BY Name; ",
            null, commandType: CommandType.Text));
            return publishers;
    }

Component Files *. Razor

There are three razor files added to the folder Page, that is AddBook.razor, EditBook.razor, and FetchBook.razor. All three contain code for I/O of the CRUD page.

AddBook.razor

Use it to create data for a new book.

@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 disabled selected hidden>
                       [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

Use it to update the data of a book.

@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">
               @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

Use it to display a list of books.

@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;

   #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();
   }
}

Code Modification

File NavMenu.razor Modify the code, so it becomes as follows.

<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 Add the following service.

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

The following is the overall project structure.

I hope it’s helpful.

The next article discusses implementing a checkbox list on CRUD operation involving an M:N (many-to-many) relationship.

Blazor Server Project #3

How to implement a checkbox list on CRUD operation involves an M:N (many-to-many) relationship.

medium.com

References

step-by-step-tutorial

Dapper is an object-relational mapping (ORM) for the .NET platform. It provides a framework for mapping an…

dapper-tutorial.net

Dapper

Dapper is a simple object mapper for .NET and owns the title of King of Micro ORM in terms of speed and is virtually as…