CRUD Operation with Dapper Using ASP.NET Core

Introduction

Using Dapper for CRUD operations in ASP.NET Core is straightforward and efficient. Dapper is a micro ORM that provides simple methods to execute SQL queries and map results to objects. Here's a basic example of how you can perform CRUD operations with Dapper in ASP.NET Core: Demo Code here.

Prerequisites

• Visual Studio is the latest version with the ASP.NET and web development workload.
• .NET SDK latest version (.NET 8.0)
• SQL SERVER latest

Creating database table

Launch SQL Server, select Database, and New Database.

CREATE TABLE [dbo].[Products] (
    [ProductId]          UNIQUEIDENTIFIER NOT NULL,
    [ProductName]        NVARCHAR (100)   NULL,
    [Price]              DECIMAL (18)     NULL,
    [ProdcutDescription] NVARCHAR (MAX)   NULL,
    [CreatedOn]          DATETIME         NULL,
    [UpdateOn]           DATETIME         NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([ProductId] ASC)
);

Creating a New Project in Visual Studio

• Launch Visual Studio and select Create a New Project.

Create a Web App

• Start Visual Studio and select Create a New Project.
• In the Create a new project dialog, select ASP.NET Core Web App (Model-View-Controller) > Next.
  

Configuring Your New Project

• In the Configure your new project dialog, enter CurdOperationWithDapperNetCoreMVC_Demo for the Project name.
• It's important to name the projectCurdOperationWithDapperNetCoreMVC_Demo. Capitalization needs to match each namespace when code is copied.
• Choose a location for your project.
• Specify the solution name
• SelectNext
  

Additional information

1. In the Additional information dialog.
   • Select .NET 8.0 (Long Term Support).
   • Verify that Do not use top-level statements is unchecked.

Select Create

Add Connection String

The ASP.NET Core Configuration system reads the DefaultConnection key. For local development, it gets the connection string from the appsettings.json file.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=farhan1086\\SQLEXPRESS;Initial Catalog=Sample-DB;Integrated Security=True;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Add a data model class

Right-click the Models folder > Add > Class. Name the file ProductModel.cs.

Replace the Models/ProductModel.cs file using c# following code.

The Company class contains an ID field, which is required by the database for the primary key.

ProductModel.cs

using System.ComponentModel.DataAnnotations;

namespace CurdOperationWithDapperNetCoreMVC_Demo.Models
{
    public class ProductModel
    {
        public Guid ProductId { get; set; }

        [Display(Name = "Product Name")]
        public string ProductName { get; set; }
        public decimal Price { get; set; }

        [Display(Name = "Prodcut Description")]
        public string ProdcutDescription { get; set; }

        [Display(Name = "Create Date")]
        public DateTime? CreatedOn { get; set; }

        [Display(Name = "Update Date")]
        public DateTime? UpdateOn { get; set; }
    }
}

Add DBContext Folder and DapperContext Class

• Right-click on Project inSolution Explorer and choose to Add New to add "New Folder".
• Rename this folder toDBContext.
• Right-click on the DBContext folder and choose AddNew to add the New ItemC# class.
• Rename name this classDapperContext.

using Microsoft.Data.SqlClient;
using System.Data;

namespace CurdOperationWithDapperNetCoreMVC_Demo.Data
{
    public class DapperDbContext
    {
        private readonly IConfiguration _configuration;
        private readonly string _connection;

        public DapperDbContext(IConfiguration configuration)
        {
            _configuration = configuration;
            _connection = configuration.GetConnectionString("DefaultConnection");
        }
        public IDbConnection CreateConnection() => new SqlConnection(_connection);
    }
}

Adding interfaces

• In Solution Explorer, right-click Add New Folder.
• Rename the folder to Interfaces.
• Right-click on the renamed folder and Add the interface name IProduct.cs

using CurdOperationWithDapperNetCoreMVC_Demo.Models;

namespace CurdOperationWithDapperNetCoreMVC_Demo.Repositories
{
    public interface IProduct
    {
        Task<IEnumerable<ProductModel>> Get();
        Task<ProductModel> Find(Guid uid);
        Task<ProductModel> Add(ProductModel model);
        Task<ProductModel> Update(ProductModel model);
        Task<ProductModel> Remove(ProductModel model);
    }
}

Add Company Repository class to Implement Interface

Right-click on the Repositories folder and AddNew Item C# class name this class toProductRepository.

using CurdOperationWithDapperNetCoreMVC_Demo.Data;
using CurdOperationWithDapperNetCoreMVC_Demo.Models;
using Dapper;

namespace CurdOperationWithDapperNetCoreMVC_Demo.Repositories
{
    public class ProductRepository : IProduct
    {
        private readonly DapperDbContext context;

        public ProductRepository(DapperDbContext context)
        {
            this.context = context;
        }
        public async Task<IEnumerable<ProductModel>> Get()
        {
            var sql = $@"SELECT [ProductId],
                               [ProductName],
                               [Price],
                               [ProdcutDescription],
                               [CreatedOn],
                               [UpdateOn]
                            FROM 
                               [Products]";

            using var connection = context.CreateConnection();
            return await connection.QueryAsync<ProductModel>(sql);
        }
        public async Task<ProductModel> Find(Guid uid)
        {
            var sql = $@"SELECT [ProductId],
                               [ProductName],
                               [Price],
                               [ProdcutDescription],
                               [CreatedOn],
                               [UpdateOn]
                            FROM 
                               [Products]
                            WHERE
                              [ProductId]=@uid";

            using var connection = context.CreateConnection();
            return await connection.QueryFirstOrDefaultAsync<ProductModel>(sql, new { uid });
        }
        public async Task<ProductModel> Add(ProductModel model)
        {
            model.ProductId = Guid.NewGuid();
            model.CreatedOn = DateTime.Now;
            var sql = $@"INSERT INTO [dbo].[Products]
                                ([ProductId],
                                 [ProductName],
                                 [Price],
                                 [ProdcutDescription],
                                 [CreatedOn])
                                VALUES
                                (@ProductId,
                                 @ProductName,
                                 @Price,
                                 @ProdcutDescription,
                                 @CreatedOn)";

            using var connection = context.CreateConnection();
            await connection.ExecuteAsync(sql, model);
            return model;
        }
        public async Task<ProductModel> Update(ProductModel model)
        {
            model.UpdateOn = DateTime.Now;
            var sql = $@"UPDATE[dbo].[Products]
                           SET [ProductId] = @ProductId,
                               [ProductName] = @ProductName,
                               [Price] = @Price,
                               [ProdcutDescription] = @ProdcutDescription,
                               [UpdateOn] = @UpdateOn
                          WHERE
                              ProductId=@ProductId";

            using var connection = context.CreateConnection();
            await connection.ExecuteAsync(sql, model);
            return model;
        }
        public async Task<ProductModel> Remove(ProductModel model)
        {
            var sql = $@"
                        DELETE FROM
                            [dbo].[Products]
                        WHERE
                            [ProductId]=@ProductId";
            using var connection = context.CreateConnection();
            await connection.ExecuteAsync(sql, model);
            return model;
        }
    }
}

Add a controller

• In Solution Explorer, right-click Controllers > Add > Controller.
• In the Add New Scaffolded Item dialog box, select MVC Controller - Empty > Add.
• In the Add New Item - CurdOperationWithDapperNetCoreMVC_Demo dialog, enter ProductsController.cs and select Add.

Replace the contents of Controllers/ProductsController.cs with the following code.

using CurdOperationWithDapperNetCoreMVC_Demo.Models;
using CurdOperationWithDapperNetCoreMVC_Demo.Repositories;
using Microsoft.AspNetCore.Mvc;

namespace CurdOperationWithDapperNetCoreMVC_Demo.Controllers
{
    public class ProductsController : Controller
    {
        private readonly IProduct productRepository;
        public ProductsController(IProduct product)
        {
            this.productRepository = product;
        }
        public async Task<IActionResult> Index()
        {
            var products = await productRepository.Get();
            return View(products);
        }
        public async Task<IActionResult> Details(Guid id)
        {
            if (id == Guid.Empty)
            {
                return NotFound();
            }
            var product = await productRepository.Find(id);

            if (product == null)
            {
                return NotFound();
            }

            return View(product);
        }
        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create(ProductModel model)
        {
            if (ModelState.IsValid)
            {
                await productRepository.Add(model);
                return RedirectToAction(nameof(Index));
            }
            return View();
        }
        public async Task<IActionResult> Edit(Guid id)
        {
            if (id == Guid.Empty)
            {
                return NotFound();
            }

            var product = await productRepository.Find(id);

            if (product == null)
            {
                return NotFound();
            }
            return View(product);
        }

        [HttpPost]
        public async Task<IActionResult> Edit(Guid id, ProductModel model)
        {
            if (id == Guid.Empty)
            {
                return NotFound();
            }
            var product = await productRepository.Find(id);

            if (product == null)
            {
                return BadRequest();
            }
            await productRepository.Update(model);
            return RedirectToAction(nameof(Index));

        }
        public async Task<IActionResult> Delete(Guid id)
        {
            if (id == Guid.Empty)
            {
                return NotFound();
            }

            var product = await productRepository.Find(id);

            if (product == null)
            {
                return BadRequest();
            }
            return View(product);
        }

        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> ConfirmDelete(Guid id)
        {
            var product = await productRepository.Find(id);
            await productRepository.Remove(product);
            return RedirectToAction(nameof(Index));
        }
    }
}

Add a view

Right-click on the action in CompaniesController, and then Add View.

In the Add NewScaffolded Item dialog.

• SelectRazor ViewSelectAdd
• View Name: Index
• Template: List
• Model Class: Company
• selected

Replace the contents of theViews/Products/Index.CSS HTMLRazor view file with the following.

@model IEnumerable<CurdOperationWithDapperNetCoreMVC_Demo.Models.ProductModel>

@{
    ViewData["Title"] = "Index";
}

<h4 class="text-capitalize">List of products</h4>

<p>
    <a asp-action="Create" class="btn btn-primary rounded-0"><i class="fa-solid fa-plus"></i> Add New</a>
</p>
<table class="table table-bordered table-striped">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.ProductName)</th>
            <th>@Html.DisplayNameFor(model => model.Price)</th>
            <th>@Html.DisplayNameFor(model => model.ProdcutDescription)</th>
            <th>@Html.DisplayNameFor(model => model.CreatedOn)</th>
            <th>@Html.DisplayNameFor(model => model.UpdateOn)</th>
            <th>Action(s)</th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>@Html.DisplayFor(modelItem => item.ProductName)</td>
            <td>@Html.DisplayFor(modelItem => item.Price)</td>
            <td>@Html.DisplayFor(modelItem => item.ProdcutDescription)</td>
            <td>@Html.DisplayFor(modelItem => item.CreatedOn)</td>
            <td>@Html.DisplayFor(modelItem => item.UpdateOn)</td>
            <td>
                    <a href="@Url.Action("Details", "Products", new { id = item.ProductId })" class="btn btn-sm btn-info"><i class="fa-solid fa-eye"></i></a>
                    <a href="@Url.Action("Edit", "Products", new { id = item.ProductId })" class="btn btn-sm btn-primary"><i class="fa-solid fa-pen-to-square"></i></a>
                    <a href="@Url.Action("Delete", "Products", new { id = item.ProductId })" class="btn btn-sm btn-danger"><i class="fa-solid fa-trash"></i></a>
            </td>
        </tr>
}
    </tbody>
</table>

Add repository services to the Program.cs

The object get method handles for example the task of connecting to the database and mapping Speaker objects to database records.

The database context get method is registered with the Dependency Injection container using the C# method, for example, the Program.cs file.

builder.Services.AddTransient<DapperDbContext, DapperDbContext>();
builder.Services.AddTransient<IProduct,ProductRepository>();

Run the Application

Select Ctrl+F5 to run the app without the debugger. Visual Studio runs the ASP.NET app and opens the default browser.

Conclusion

The above article has taught us. How we can use Dapper with the repository pattern? Dapper is Micro ORM whereas Entity Framework is ORM. Dapper is faster than entity framework. Hope you enjoyed the article. Happy Coding.