CRUD Using Blazor, Entity Framework Core And Dapper

In the previous post, I wrote about Getting Started with Blazor, If you are new, I recommend please you read that post first.

Introduction

In this post, we are going to learn CRUD (Create, Read, Update and Delete) operations using Blazor and Entity Framework Core 3.0 in Visual Studio 2019. We will use the dapper to perform CRUD operations.

Prerequisites

  • Install the .NET Core 2.1 SDK from here.
  • Install the latest preview of Visual Studio 2017 (15.7) from here or Visual Studio 2019 from here.
  • Install ASP.NET Core Blazor Language Services extension from here.

Create a new project

Open Visual Studio 2019, and select “Create a new project”.

Select “Blazor App”.

Click on the “Next” button and set the proper Name and path for the project.

On the next page, select the app type. I’ll select the “Blazor Server App” for this post.

Now, we have successfully created a Blazor project. Press the “F5” key to run and you can see the below screen.

Create a database, table and store procedure for application.

Copy below script and paste in your SQL server.

USE [TheCodeHubs]
GO
/****** Object:  Table [dbo].[Article]    Script Date: 11/7/2019 3:06:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Article](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Title] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Add_Article]    
    @Title NVARCHAR(250)   
    
      
AS    
    BEGIN    
 DECLARE @Id as BIGINT  
        INSERT  INTO [Article]    
                (Title
             )    
        VALUES  ( @Title       
             );   
    SET @Id = SCOPE_IDENTITY();   
        SELECT  @Id AS ArticleID;    
    END;    
  
 
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Update_Article] 
  @Id INT,   
    @Title NVARCHAR(250)   
AS    
    BEGIN    

  UPDATE [Article] SET Title = @Title WHERE ID = @Id 
             
    END;    
  
 
GO
USE [master]
GO
ALTER DATABASE [TheCodeHubs] SET  READ_WRITE 
GO

Add a connection string into the appsettings.json file.

Here is the code of the appsettings.json file.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=.;Integrated Security=SSPI;Initial Catalog=TheCodeHubs;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Install NuGet packages.

We need to install the below packages.

  • Microsoft.EntityFrameworkCore.SqlServer

  • Microsoft.EntityFrameworkCore.SqlServer.Design

  • Microsoft.EntityFrameworkCore.Tools

  • Dapper

Create Dapper Class and Interface

I’m creating two new folders namely Concrete and Contracts (folder creation are optional).

In the Contracts folder, add a new interface namely “IDapperManager” and copy the below code and paste in that class.

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace BlazorCRUD.Contracts
{
    public interface IDapperManager : 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);
    }
}

In the Concrete folder, add a new class namely “DapperManager” and copy the below code and paste in that class.

using BlazorCRUD.Contracts;
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 BlazorCRUD.Concrete
{
    public class DapperManager : IDapperManager
    {
        private readonly IConfiguration _config;
        public DapperManager(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()
        {
            throw new NotImplementedException();
        }
    }
}

DapperManager class will help us to interact with the database.

Create a Context class

Create a new folder namely “DataAccess” and add one class “AppContext” which extends from the DbContext class.

Copy the below code and paste inside the “AppContext” class.

using Microsoft.EntityFrameworkCore;

namespace BlazorCRUD.DataAccess
{
    public class AppContext : DbContext
    {
        public AppContext() { }

        public AppContext(DbContextOptions<AppContext> options) : base(options) { }
    }
}

Create another folder namely “Entities” and add an Article class.

Copy the below code and paste it into the “Article” class.

using System.ComponentModel.DataAnnotations;

namespace BlazorCRUD.Entities
{
    public class Article
    {
        [Key]
        public int ID { get; set; }
        public string Title { get; set; }
    }
}

Create another folder namely “Models” and add an ArticleModel class.

Copy the below code and paste it into the “ArticleModel” class.

namespace BlazorCRUD.Models
{
    public class ArticleModel
    {
        public int ID { get; set; }
        public string Title { get; set; }
    }
}

Add a new “IArticleManager” interface inside the Contracts folder which we created earlier. Below is the code of the interface.

using BlazorCRUD.Entities;
using System.Collections.Generic;

namespace BlazorCRUD.Contracts
{
    public interface IArticleManager
    {
        Task<int> Create(Article article);
        Task<int> Delete(int Id);
        Task<int> Update(Article article);
        Task<Article> GetById(int Id);
        Task<List<Article>> ListAll();
    }
}

Add a new “ArticleManager” class inside the Concrete folder which we created earlier. Below is the code of the class.

using BlazorCRUD.Contracts;
using BlazorCRUD.Entities;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BlazorCRUD.Concrete
{
    public class ArticleManager : IArticleManager
    {
        private readonly IDapperManager _dapperManager;

        public ArticleManager(IDapperManager dapperManager)
        {
            this._dapperManager = dapperManager;
        }

        public Task<int> Create(Article article)
        {
            var dbPara = new DynamicParameters();
            dbPara.Add("Title", article.Title, DbType.String);
            var articleId = Task.FromResult(_dapperManager.Insert<int>("[dbo].[SP_Add_Article]",
                            dbPara,
                            commandType: CommandType.StoredProcedure));
            return articleId;
        }

        public Task<Article> GetById(int id)
        {
            var article = Task.FromResult(_dapperManager.Get<Article>($"select * from [Article] where ID = {id}", null,
                    commandType: CommandType.Text));
            return article;
        }

        public Task<int> Delete(int id)
        {
            var deleteArticle = Task.FromResult(_dapperManager.Execute($"Delete [Article] where ID = {id}", null,
                    commandType: CommandType.Text));
            return deleteArticle;
        }

        public Task<List<Article>> ListAll()
        {
            var articles = Task.FromResult(_dapperManager.GetAll<Article>("select * from [Article]", null, commandType: CommandType.Text));
            return articles;

        }

        public Task<int> Update(Article article)
            {
                var dbPara = new DynamicParameters();
                dbPara.Add("Id", article.ID);
                dbPara.Add("Title", article.Title, DbType.String);

                var updateArticle = Task.FromResult(_dapperManager.Update<int>("[dbo].[SP_Update_Article]",
                                dbPara,
                                commandType: CommandType.StoredProcedure));
                return updateArticle;
            }
    }
}

Now, we need to add a connection string and register our services into the startup class.

  • Add connection string inside the ConfigureServices method.
services.AddDbContext<DataAccess.AppContext>(options =>
                          options.UseSqlServer(
                              Configuration.GetConnectionString("DefaultConnection")));
  • Register service inside the ConfigureServices method.
//Article service  
 services.AddScoped<IArticleManager, ArticleManager>();

//Register dapper in scope  
 services.AddScoped<IDapperManager, DapperManager>();

It’s now time to add a razor component for Articles.

  • Details Page

Let’s create a page to list the articles from the table. To create that right-click on the “Pages” folder > Add >  New Item >  and search for “Razor Component” and select it,set name as “FetchArticle.razor”.

Below is the code for the “FetchArticle.razor” component.

@page "/articlelist"

@using BlazorCRUD.Entities
@using BlazorCRUD.Contracts
@inject IArticleManager articleManager

<div>
    <a class="btn btn-primary" href='/addArticle'>Add</a>
</div>
<br />

@if (articleModel == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var article in articleModel)
            {
                <tr>
                    <td>@article.ID</td>
                    <td>@article.Title</td>
                    <td>
                        <a class="btn btn-primary" href='/editArticle/@article.ID'>Edit</a>  |
                        <a class="btn btn-danger" @onclick="() => DeleteArticle(article.ID)">Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
}


@code {
    List<Article> articleModel;
    Article articleEntity = new Article();

    protected override async Task OnInitializedAsync()
    {
        articleModel = await articleManager.ListAll();
    }


    protected async Task DeleteArticle(int id)
    {
        await articleManager.Delete(id);
        articleModel = await articleManager.ListAll();
    }

}
  • Add Page

Add new Razor component for Add Record, namely “AddArticle.razor”.Below is the code for the “AddArticle.razor” component.

@page "/addArticle"
@using BlazorCRUD.Entities
@using BlazorCRUD.Contracts
@inject IArticleManager articleManager
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager
<h1>
    Add Article
</h1>

<div class="row">
    <div class="col-md-4">
        <form>
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind-value="@article.Title" />
            </div>
            <div class="form-group">
                <button type="button" class="btn btn-primary" @onclick="() => CreateArticle()">Save</button>
                <button type="button" class="btn btn-danger" @onclick="() => cancel()">Cancel</button>
            </div>
        </form>
    </div>
</div>

@code {

    Article article = new Article();

    protected async Task CreateArticle()
    {
        await articleManager.Create(article);
        navigationManager.NavigateTo("/articlelist");
    }

    void cancel()
    {
        navigationManager.NavigateTo("/articlelist");
    }
}
  • Edit Page

Add new Razor component for Edit Record, namely “EditArticle.razor”.Below is the code for “EditArticle.razor” component.

@page "/editArticle/{ID}"
@using BlazorCRUD.Entities
@using BlazorCRUD.Contracts
@inject IArticleManager articleManager
@inject Microsoft.AspNetCore.Components.NavigationManager navigationManager

    <div class="row">
        <div class="col-md-4">
            <form>
                <div class="form-group">
                    <label for="Name" class="control-label">Name</label>
                    <input for="Name" class="form-control" @bind-value="@articleEntity.Title" />
                    <input type="hidden"  @bind-value="@articleEntity.ID" />
                </div>
                <div class="form-group">
                    <button type="button" class="btn btn-primary" @onclick="() => UpdateArticle()">Save</button>
                    <button type="button" class="btn btn-danger" @onclick="() => cancel()">Cancel</button>
                </div>
            </form>
        </div>
    </div>

@code  {

    [Parameter]
    public string ID { get; set; }

    Article articleEntity = new Article();

    protected override async Task OnInitializedAsync()
    {
       articleEntity = await articleManager.GetById(Convert.ToInt32(ID));
    }

    protected async Task UpdateArticle()
    {
        await articleManager.Update(articleEntity);
        navigationManager.NavigateTo("/articlelist");
    }

    void cancel()
    {
        navigationManager.NavigateTo("/articlelist");
    }
}
  • Adding Link to Navigation menu

    Update “NavMenu.razor” file for the menu.

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">BlazorCRUD</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="articlelist">
                <span class="oi oi-plus" aria-hidden="true"></span> Articles
            </NavLink>
        </li>       
    </ul>
</div>

@code {
    bool collapseNavMenu = true;

    string NavMenuCssClass => collapseNavMenu ? "collapse" : null;

    void ToggleNavMenu()
    {
        collapseNavMenu = !collapseNavMenu;
    }
}

At the end, your solution looks like this.

Output

You can download code from here. you also can perform sorting on the table to do this visit here.

If you want to learn File Upload in Blazor please visit here.

16 Comments

  1. Jose

    the project is on github?

    0
    0
    Reply
  2. Dj Spy

    I will not deny that dapper is very fast and is awesome, *if* you know how to make it work for complex situations.

    Is it possible to show how to use dapper where you have a scenario with at least 3 different relation tables, most demos I find is mostly with 2, and that is fairly easy, but once you go higher I find that it very rare to find articles about it? So in defence of dapper, it would be awesome to actually show a complex example where you use dapper to retrieve and save data where you have more than 2 relations.

    For example:
    Table Employee
    Table VacationRequest
    Table Approver (that actually is a many to many to Employee since an Approver is also an Employee)

    See where I’m going, real-life complex examples, with EF Core it is quite easy to do, but I just can’t get it working with Dapper and that is a shame.

    0
    0
    Reply
  3. Hany

    Hi … whats is the use of appcontext class here. dapper do all work with connectionstring directly >>

    0
    0
    Reply
    1. If anyone wants to use that instead of dapper. nothing else

      0
      0
      Reply
  4. Andres

    Hi Faisal! Great stuff and very interesting. Just one question, in this particular example, I can’t see the use of EF AppContext :DbContext. All I see is that Dapper do everything? Please enlighten me and thank you in advance!

    0
    0
    Reply
    1. Thanks for reading, and ya you can use any method dapper or context, depends on us.

      0
      0
      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories