Create An App Using Microsoft Blazor, Dapper, And .NET Core

Introduction

This article will teach you how to use Microsoft's Blazor Stack Overflow's Dapper Micro-ORM to rapidly develop modern data-driven asynchronous websites. Many people prefer Dapper to Entity Framework because it's simple, lightweight, and doesn't hide the SQL code from you. 

 

Step 1

 

Let's create the new project as below,

 

Name the project 

Select .Net Core 3.1 and click on create 

Step 2

 

Now let's install the Nuget packages

Step 3

 

Now let's create a database and tables

 

CREATE TABLE [dbo].[Video] (
    [VideoID]       INT           IDENTITY (1, 1) NOT NULL,
    [Title]         VARCHAR (128) NULL,
    [DatePublished] DATE          NULL,
    [IsActive]      BIT           CONSTRAINT [DF_Video_IsActive] DEFAULT ((1)) NULL,
    CONSTRAINT [PK_Video] PRIMARY KEY CLUSTERED ([VideoID] ASC)
);

Step 4

 

Now let's create classes to manage Data

using System;
// This is essentially a model for one row in the Video table.
namespace BlazorDapperCRUD.Data
{
    public class Video
    {
        public int VideoID { get; set; }
        public string Title { get; set; }
        public DateTime DatePublished { get; set; }
        public bool IsActive { get; set; }
    }
}

namespace BlazorDapperCRUD.Data
{
    // Connection to SQL Server database, used within Data subfolder.
    public class SqlConnectionConfiguration
    {
        public SqlConnectionConfiguration(string value) => Value = value;
        public string Value { get; }
    }

}

Step 5

 

Now let's create a service class and interface for crud operations

using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace BlazorDapperCRUD.Data
{
    public class VideoService : IVideoService
    {
        // Database connection
        private readonly SqlConnectionConfiguration _configuration;
        public VideoService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }

        // Add (create) a Video table row (SQL Insert)
        public async Task<bool> VideoInsert(Video video)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("Title", video.Title, DbType.String);
                parameters.Add("DatePublished", video.DatePublished, DbType.Date);
                parameters.Add("IsActive", video.IsActive, DbType.Boolean);
                // Stored procedure method
                await conn.ExecuteAsync("spVideo_Insert", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }

        // Get a list of video rows (SQL Select)
        public async Task<IEnumerable<Video>> VideoList()
        {
            IEnumerable<Video> videos;
            using (var conn = new SqlConnection(_configuration.Value))
            {
                videos = await conn.QueryAsync<Video>("spVideo_GetAll", commandType: CommandType.StoredProcedure);
            }
            return videos;

        }

        // Get one video based on its VideoID (SQL Select)
        public async Task<Video> Video_GetOne(int id)
        {
            Video video = new Video();
            var parameters = new DynamicParameters();
            parameters.Add("Id", id, DbType.Int32);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                video = await conn.QueryFirstOrDefaultAsync<Video>("spVideo_GetOne", parameters, commandType: CommandType.StoredProcedure);
            }
            return video;
        }

        // Update one Video row based on its VideoID (SQL Update)
        public async Task<bool> VideoUpdate(Video video)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("VideoID", video.VideoID, DbType.Int32);
                parameters.Add("Title", video.Title, DbType.String);
                parameters.Add("DatePublished", video.DatePublished, DbType.Date);
                parameters.Add("IsActive", video.IsActive, DbType.Boolean);
                await conn.ExecuteAsync("spVideo_Update", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }

        // Physically delete one Video row based on its VideoID (SQL Delete)
        public async Task<bool> VideoDelete(int id)
        {
            var parameters = new DynamicParameters();
            parameters.Add("Id", id, DbType.Int32);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                await conn.ExecuteAsync("spVideo_Delete", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
    }
}

Step 6

 

Now let's add web pages to perform CRUD operations 

   

@using BlazorDapperCRUD.Data
@page "/videoaddedit/{id:int}"
@inject IVideoService VideoService
@inject NavigationManager NavigationManager

@*Okay to use DataAnnotationsValidator and ValidationSummary here, if you like. See...
https://docs.microsoft.com/en-us/aspnet/core/blazor/forms-validation?view=aspnetcore-3.1*@

<h1>@pagetitle</h1>

<EditForm Model="@video" OnValidSubmit="@VideoSave">
    <table class="editform">
        <tr>
            <td>Video Title:</td>
            <td><input type="text" @bind="video.Title" required /></td>
        </tr>
        <tr>
            <td>Date Published:</td>
            <td><input type="date" @bind="video.DatePublished" required min="1900-01-01" max="2050-12-31" /></td>
        </tr>
        <tr>
            <td>Is Active:</td>
            <td><input type="checkbox" @bind="video.IsActive" /></td>
        </tr>
        <tr>
            <td colspan="2" style="text-align:center">
                <input type="submit" value="@buttontext" />
                <input type="button" value="Cancel" @onclick="@Cancel" />
            </td>
        </tr>
    </table>


</EditForm>

@code {
    // Create a new, empty Video object
    Video video = new Video();

    [Parameter]
    public int id { get; set; }

    // Set default page title and button text
    public string pagetitle = "Add a Video";
    public string buttontext = "Add";

    //Executes on page open, set defaults on page.
    protected override async Task OnInitializedAsync()
    {
        // ============ If the passed-in id is zero, assume new Video.
        if (id == 0)
        {
            DateTime defaultdate = new DateTime(2000, 12, 31);
            video.DatePublished = defaultdate;
            video.IsActive = true;
        }
        else
        {
            video = await VideoService.Video_GetOne(id);
            // Change page title and button text since this is an edit.
            pagetitle = "Edit Video";
            buttontext = "Update";
        }
    }

    protected async Task VideoSave()
    {
        if (video.VideoID == 0)
        {
            // Insert if id is zero.
            await VideoService.VideoInsert(video);
        }
        else
        {
            // Update if id not 0
            await VideoService.VideoUpdate(video);
        }
        NavigationManager.NavigateTo("/videolist");
    }
    void Cancel()
    {
        NavigationManager.NavigateTo("/videolist");
    }
}

Conclusion 

In this article, we discussed how to work with Blazor and Dapper using .Net core. I hope you all enjoyed reading this and learned from it. For better understanding download the source code.