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.

17 Comments

  1. Behrad Zarei

    Hi Faisal,It was very helpful, Thanks

    0
    0
    Reply
  2. Adil Hassan

    Hello I am running into this error Severity Code Description Project File Line Suppression State
    Error CS1061 ‘List’ does not contain a definition for ‘ListAll’ and no accessible extension method ‘ListAll’ accepting a first argument of type ‘List’ could be found (are you missing a

    0
    0
    Reply
    1. Faisal Pathan

      Please verify your code with this artcile, I think somewhere, you are passing single object insted of List

      0
      0
      Reply
  3. Tarik

    Very Awesome and clear explanation.
    Thank you very much.

    0
    0
    Reply
    1. Faisal Pathan

      Thanks for feedback

      0
      0
      Reply
  4. Maurice

    THANKS FOR FEEDBACK FAISAL. IT IS CLASSIC

    0
    0
    Reply
    1. Faisal Pathan

      Thanks for read!

      0
      0
      Reply
  5. Maurice

    Thanx Faisal. Very Brillant Article.

    Pls can you extend the article to add sorting, paging and filtering and the possibility of Exporting the results to Excel?

    0
    0
    Reply
    1. Faisal Pathan

      Thanks for the feedback!

      Sure I’ll implement and post here, keep in touch 🙂

      0
      0
      Reply
    2. Faisal Pathan

      Hi, Sorting logic is added please visit “https://www.thecodehubs.com/sorting-table-in-blazor/”

      0
      0
      Reply

Submit a Comment

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

Subscribe

Select Categories