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
  1. CREATE TABLE [dbo].[Video] (
  2. [VideoID] INT IDENTITY (1, 1) NOT NULL,
  3. [Title] VARCHAR (128) NULL,
  4. [DatePublished] DATE NULL,
  5. [IsActive] BIT CONSTRAINT [DF_Video_IsActive] DEFAULT ((1)) NULL,
  6. CONSTRAINT [PK_Video] PRIMARY KEY CLUSTERED ([VideoID] ASC)
  7. );
Step 4
Now let's create classes to manage Data
  1. using System;
  2. // This is essentially a model for one row in the Video table.
  3. namespace BlazorDapperCRUD.Data
  4. {
  5. public class Video
  6. {
  7. public int VideoID { get; set; }
  8. public string Title { get; set; }
  9. public DateTime DatePublished { get; set; }
  10. public bool IsActive { get; set; }
  11. }
  12. }
  1. namespace BlazorDapperCRUD.Data
  2. {
  3. // Connection to SQL Server database, used within Data subfolder.
  4. public class SqlConnectionConfiguration
  5. {
  6. public SqlConnectionConfiguration(string value) => Value = value;
  7. public string Value { get; }
  8. }
  10. }
Step 5
Now let's create a service class and interface for crud operations
  1. using Dapper;
  2. using Microsoft.Data.SqlClient;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Data;
  6. using System.Linq;
  7. using System.Threading.Tasks;
  9. namespace BlazorDapperCRUD.Data
  10. {
  11. public class VideoService : IVideoService
  12. {
  13. // Database connection
  14. private readonly SqlConnectionConfiguration _configuration;
  15. public VideoService(SqlConnectionConfiguration configuration)
  16. {
  17. _configuration = configuration;
  18. }
  20. // Add (create) a Video table row (SQL Insert)
  21. public async Task<bool> VideoInsert(Video video)
  22. {
  23. using (var conn = new SqlConnection(_configuration.Value))
  24. {
  25. var parameters = new DynamicParameters();
  26. parameters.Add("Title", video.Title, DbType.String);
  27. parameters.Add("DatePublished", video.DatePublished, DbType.Date);
  28. parameters.Add("IsActive", video.IsActive, DbType.Boolean);
  29. // Stored procedure method
  30. await conn.ExecuteAsync("spVideo_Insert", parameters, commandType: CommandType.StoredProcedure);
  31. }
  32. return true;
  33. }
  35. // Get a list of video rows (SQL Select)
  36. public async Task<IEnumerable<Video>> VideoList()
  37. {
  38. IEnumerable<Video> videos;
  39. using (var conn = new SqlConnection(_configuration.Value))
  40. {
  41. videos = await conn.QueryAsync<Video>("spVideo_GetAll", commandType: CommandType.StoredProcedure);
  42. }
  43. return videos;
  45. }
  47. // Get one video based on its VideoID (SQL Select)
  48. public async Task<Video> Video_GetOne(int id)
  49. {
  50. Video video = new Video();
  51. var parameters = new DynamicParameters();
  52. parameters.Add("Id", id, DbType.Int32);
  53. using (var conn = new SqlConnection(_configuration.Value))
  54. {
  55. video = await conn.QueryFirstOrDefaultAsync<Video>("spVideo_GetOne", parameters, commandType: CommandType.StoredProcedure);
  56. }
  57. return video;
  58. }
  60. // Update one Video row based on its VideoID (SQL Update)
  61. public async Task<bool> VideoUpdate(Video video)
  62. {
  63. using (var conn = new SqlConnection(_configuration.Value))
  64. {
  65. var parameters = new DynamicParameters();
  66. parameters.Add("VideoID", video.VideoID, DbType.Int32);
  67. parameters.Add("Title", video.Title, DbType.String);
  68. parameters.Add("DatePublished", video.DatePublished, DbType.Date);
  69. parameters.Add("IsActive", video.IsActive, DbType.Boolean);
  70. await conn.ExecuteAsync("spVideo_Update", parameters, commandType: CommandType.StoredProcedure);
  71. }
  72. return true;
  73. }
  75. // Physically delete one Video row based on its VideoID (SQL Delete)
  76. public async Task<bool> VideoDelete(int id)
  77. {
  78. var parameters = new DynamicParameters();
  79. parameters.Add("Id", id, DbType.Int32);
  80. using (var conn = new SqlConnection(_configuration.Value))
  81. {
  82. await conn.ExecuteAsync("spVideo_Delete", parameters, commandType: CommandType.StoredProcedure);
  83. }
  84. return true;
  85. }
  86. }
  87. }
Step 6
Now let's add web pages to perform CRUD operations

  1. @using BlazorDapperCRUD.Data
  2. @page "/videoaddedit/{id:int}"
  3. @inject IVideoService VideoService
  4. @inject NavigationManager NavigationManager
  6. @*Okay to use DataAnnotationsValidator and ValidationSummary here, if you like. See...
  7. https://docs.microsoft.com/en-us/aspnet/core/blazor/forms-validation?view=aspnetcore-3.1*@
  9. <h1>@pagetitle</h1>
  11. <EditForm Model="@video" OnValidSubmit="@VideoSave">
  12. <table class="editform">
  13. <tr>
  14. <td>Video Title:</td>
  15. <td><input type="text" @bind="video.Title" required /></td>
  16. </tr>
  17. <tr>
  18. <td>Date Published:</td>
  19. <td><input type="date" @bind="video.DatePublished" required min="1900-01-01" max="2050-12-31" /></td>
  20. </tr>
  21. <tr>
  22. <td>Is Active:</td>
  23. <td><input type="checkbox" @bind="video.IsActive" /></td>
  24. </tr>
  25. <tr>
  26. <td colspan="2" style="text-align:center">
  27. <input type="submit" value="@buttontext" />
  28. <input type="button" value="Cancel" @onclick="@Cancel" />
  29. </td>
  30. </tr>
  31. </table>
  34. </EditForm>
  36. @code {
  37. // Create a new, empty Video object
  38. Video video = new Video();
  40. [Parameter]
  41. public int id { get; set; }
  43. // Set default page title and button text
  44. public string pagetitle = "Add a Video";
  45. public string buttontext = "Add";
  47. //Executes on page open, set defaults on page.
  48. protected override async Task OnInitializedAsync()
  49. {
  50. // ============ If the passed-in id is zero, assume new Video.
  51. if (id == 0)
  52. {
  53. DateTime defaultdate = new DateTime(2000, 12, 31);
  54. video.DatePublished = defaultdate;
  55. video.IsActive = true;
  56. }
  57. else
  58. {
  59. video = await VideoService.Video_GetOne(id);
  60. // Change page title and button text since this is an edit.
  61. pagetitle = "Edit Video";
  62. buttontext = "Update";
  63. }
  64. }
  66. protected async Task VideoSave()
  67. {
  68. if (video.VideoID == 0)
  69. {
  70. // Insert if id is zero.
  71. await VideoService.VideoInsert(video);
  72. }
  73. else
  74. {
  75. // Update if id not 0
  76. await VideoService.VideoUpdate(video);
  77. }
  78. NavigationManager.NavigateTo("/videolist");
  79. }
  80. void Cancel()
  81. {
  82. NavigationManager.NavigateTo("/videolist");
  83. }
  84. }

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.