CRUD Operation with Dapper Using ASP.NET Core

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.