Featured

Dapper In ASP.NET 6 With Repository Pattern

Last modified: April 14, 2022

< gRPC Authenticate and Authorize (Server & Client)

Step 1 Create a MVC Application

project

Step 2 Install NuGet Packages

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.123" />
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="6.0.1" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Abstractions" Version="6.0.0" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.3" />
  </ItemGroup>

nuget

3. Create a Database and Table

Open SQL Server Managment Studio and run the below SQL Script to create a database, table and insert sample records

CREATE DATABASE Dapper

GO
CREATE TABLE [Dapper].[dbo].[Product](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Sku] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
	[Manufacturer] [nvarchar](200) NOT NULL,
	[Price] [decimal](9, 2) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [Dapper].[dbo].[Product] (Sku,[Name], Manufacturer, Price)
VALUES
('iphone12', 'iPhone 12', 'Apple', 800),
('iphone12pro', 'iPhone 12 Pro', 'Apple', 900),
('iphone13', 'iPhone 13', 'Apple', 1000),
('iphone12pro', 'iPhone 12 Pro', 'Apple', 1100)

4. Architecture

Core => Interfaces and Domain Models
Infrastructure => Implementations of Repository and other interface
Web => User Interface

We usually create different projects for above layers but for demo and simplity, we will just create separate folders.

Create a folder named Core
- Create a folder named Entities on Core foder
- Create a folder name Interfaces on Core folder
Create a folder named Infrastructure
- Create a folder named Repository on Infrastructure folder

Entities

Product

public class Product
{
    public int Id { get; set; }
    public string Sku { get; set; }
    public string Name { get; set; }
    public string Manufacturer { get; set; }
    public decimal Price { get; set; }
}

Interfaces

IGenericRepository

public interface IGenericRepository<T> where T : class
{
    Task<T?> GetById(int id);
    Task<IEnumerable<T>> GetAll();
    Task<bool> Add(T entity);
    Task<bool> Remove(int id);
    Task<bool> Update(T entity);
}

IProductRepository

public interface IProductRepository : IGenericRepository<Product>
{
}

IUnitOfWork

public interface IUnitOfWork
{
    IProductRepository Products { get; }
}

Repository

ProductRepository

We use Dapper instead of Entity Framework core

public class ProductRepository : IProductRepository
{
    private readonly IConfiguration _configuration;

    public ProductRepository(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public async Task<bool> Add(Product entity)
    {
        var sql = "INSERT INTO [dbo].[Product] ([Sku], [Name], [Manufacturer],[Price]) VALUES (@Sku, @Name, @Manufacturer, @Price)";

        using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
        {
            connection.Open();
            var result = await connection.ExecuteAsync(sql, entity);
            return true;
        }

    }

    public async Task<IEnumerable<Product>> GetAll()
    {
        var sql = "SELECT * FROM Product";
        using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
        {
            connection.Open();
            var result = await connection.QueryAsync<Product>(sql);
            return result.ToList();
        }
    }

    public async Task<Product?> GetById(int id)
    {
        var sql = "SELECT * FROM Product WHERE Id = @Id";
        using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
        {
            connection.Open();
            var result = await connection.QuerySingleOrDefaultAsync<Product>(sql, new { Id = id });
            return result;
        }
    }

    public async Task<bool> Remove(int id)
    {
        var sql = "DELETE FROM Products WHERE Id = @Id";
        using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
        {
            connection.Open();
            var result = await connection.ExecuteAsync(sql, new { Id = id });
            return true;
        }
    }

    public async Task<bool> Update(Product entity)
    {
        var sql = "UPDATE Product SET Sku = @Sku, Name = @Name, Manufacturer = @Manufacturer, Price = @Price WHERE Id = @Id";

        using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
        {
            connection.Open();
            await connection.ExecuteAsync(sql, entity);
            return true;
        }
    }
}

UnitOfWork

public class UnitOfWork : IUnitOfWork
{
    public UnitOfWork(IProductRepository productRepository)
    {
        Products = productRepository;
    }
    public IProductRepository Products { get; }
}

5. appsettings.json

Configure the database connection string

"ConnectionStrings": {
    "DefaultConnection": "Data Source=.;Initial Catalog=Dapper;Integrated Security=True;MultipleActiveResultSets=True"
  },

6. Controller.

Create a controller ProductController with the values before. We are going to use AJAX to perform whole CRUD (Create, Read, Update & Delete) methods.

public class ProductController : Controller
{
    // GET: ProductController

    private readonly IUnitOfWork _unitOfWork;

    public ProductController(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public IActionResult Index()
    {
        return View();
    }

    public async Task<JsonResult> GetProduct()
    {
        var products = await _unitOfWork.Products.GetAll();
        return Json(products);
    }

    // GET: ProductController/Details/5
    public async Task<JsonResult> DetailsAsync(int id)
    {
        var product = await _unitOfWork.Products.GetById(id);
        return Json(product);
    }

    // POST: ProductController/Create
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<JsonResult> Create(Product product)
    {
        bool add = await _unitOfWork.Products.Add(product);
        return Json(add);
    }

    // POST: ProductController/Edit/5
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<JsonResult> Edit(int id, Product product)
    {
        var p = await _unitOfWork.Products.GetById(id);

        if(p == null)
            return Json(NotFound());

        bool update = await _unitOfWork.Products.Update(product);

        return Json(update);
    }

    // POST: ProductController/Delete/5
    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<ActionResult> DeleteAsync(int id)
    {
        var p = await _unitOfWork.Products.GetById(id);

        if (p == null)
            return Json(NotFound());

        bool delete = await _unitOfWork.Products.Remove(id);

        return Json(delete);
    }
}

7. Program.cs

Add two DIs(dependency injection)

builder.Services.AddTransient<IProductRepository, ProductRepository>();
builder.Services.AddTransient<IUnitOfWork, UnitOfWork>();

8. View

Replace or add following JavaScripts on _Layout Page

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" integrity="sha512-894YE6QWD5I59HgZOGReFYm4dnWc1Qt5NtvYSaNcOP+u1T9qYdvdihz0PPSiiqn/+/3e7Jo4EaG7TubfWGUrMQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js" integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js" integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous"></script>

view

We need AntiForgeryToken for validation

@Html.AntiForgeryToken()

Add a Product Modal (Pop up)

<button type="button" class="btn btn-primary" data-toggle="modal" data-target="#ProductModel">
  Add Product
</button>

<!-- Modal -->
<div class="modal fade" id="ProductModel" tabindex="-1" role="dialog" aria-labelledby="ProductModelTitle" aria-hidden="true">
  <div class="modal-dialog modal-dialog-centered" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLongTitle">Modal title</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">×</span>
        </button>
      </div>
      <div class="modal-body">
        <form>
          <div class="form-group">
              <label for="name">Sku</label>
            <input type="text" class="form-control" id="Sku" name="Sku" aria-describedby="SkuHelp" placeholder="Enter your sku">

            <label for="email">Name</label>
            <input type="text" class="form-control" id="Name" name="Name" aria-describedby="NameHelp" placeholder="Enter product name">

            <label for="email">Manufacturer</label>
            <input type="text" class="form-control" id="Manufacturer" name="Manufacturer" aria-describedby="ManufacturerHelp" placeholder="Enter manufacturer">

            <label for="mobile">Price</label>
            <input type="number" class="form-control" id="Price" name="Price" aria-describedby="PriceHelp" placeholder="Enter your product price">
            <input type="hidden" id="id" name="id" /> <!--Used it for Edit Product only to hold selected product id -->
            </div>
        </form>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
        <button type="button" id="btnAdd" class="btn btn-primary">Save changes</button>
      </div>
    </div>
  </div>
</div>

List Products


<table id="ProductTable" class="table table-striped table-bordered table-hover">
    <thead>
        <tr>
            <th>Sku</th>
            <th>Name</th>
            <th>Manufacturer</th>
            <th>Price</th>
            <th></th>
            <th></th>
        </tr>
    </thead>
    <tbody id="tbodyid">      
    </tbody>
</table>

Load and Draw Table (Create)

loadProductRecords();

function loadProductRecords()
{
    $.ajax({
        type: "GET",
        url: "/Product/GetProducts",
        dataType: "json",
        success: function (msg) { 
            DrawTable(msg);
        }
    });

}

function DrawTable(response)
{
    $("#tbodyid").empty();

    $.each(response, function(i, item) {
        var $tr = $('<tr id="'+item.id+'">').append(
            $('<td>').text(item.sku),
            $('<td>').text(item.name),
            $('<td>').text(item.manufacturer),
            $('<td>').text(item.price),
            $('<td>').html('<button type="button" args="'+ item.id +'" class="btn-edit btn btn-success">Edit</button>'),
            $('<td>').html('<button type="button" args="'+ item.id +'" class="btn-delete btn btn-danger">Delete</button>')
        ).appendTo('#ProductTable');
    });
}

Add and Edit Product (Add and Update)

$("#btnAdd").click(function(){
if ($('#btnAdd').hasClass('btn-update'))
{
    var id = $('#id').val();

    $.ajax({
            type: "POST",
            url: "/Product/Edit",
            data: $('form').serialize(),
            contentType: 'application/x-www-form-urlencoded',
            dataType: "json",
            headers:
            {
                "RequestVerificationToken": $('input:hidden[name="__RequestVerificationToken"]').val()
            },
            success: function (msg) { 
                loadProductRecords();
                $("#btnAdd").removeClass('btn-update');
                $('#ProductModel').modal('hide'); //hide the modal
            }
        });

}
else{
        $.ajax({
            type: "POST",
            url: "/Product/Create",
            data: $('form').serialize(),
            contentType: 'application/x-www-form-urlencoded',
            dataType: "json",
            headers:
            {
                "RequestVerificationToken": $('input:hidden[name="__RequestVerificationToken"]').val()
            },
            success: function (msg) { 

                loadProductRecords();
                $('#ProductModel').modal('hide'); //hide the modal
            }
        });
    }
});

Read and Delete

$(document).on('click', '.btn-edit', function(){ 
    var id = $(this).attr('args');
    $.ajax({
        type: "GET",
        url: "/Product/Details",
        data: {"id": id},
        contentType: 'application/x-www-form-urlencoded',
        dataType: "json",
        headers:
        {
            "RequestVerificationToken": $('input:hidden[name="__RequestVerificationToken"]').val()
        },
        success: function (msg) {  
            $("#Sku").val(msg.sku);
            $("#Name").val(msg.name);
            $("#Manufacturer").val(msg.manufacturer);
            $("#Price").val(msg.price);
            $("#id").val(msg.id);
            $("#btnAdd").addClass("btn-update"); //add the btn-update class to modal save button so that we can make diferrent between add or update action


            $('#ProductModel').modal('show');
        }
    });
});

$(document).on('click', '.btn-delete', function(){ 
    var id = $(this).attr('args');
    $.ajax({
        type: "POST",
        url: "/Product/Delete",
        data: {"id": id},
        contentType: 'application/x-www-form-urlencoded',
        dataType: "json",
        headers:
        {
            "RequestVerificationToken": $('input:hidden[name="__RequestVerificationToken"]').val()
        },
        success: function (msg) {  
            $('table#ProductTable tr#'+ id).remove();
        }
    });
    });

9. Test the application

Add a product

test

Edit a product

test

List and delete product

test

< gRPC Authenticate and Authorize (Server & Client)

Google Chart in ASP.NET 5 >

Featured

Dapper In ASP.NET 6 With Repository Pattern

Step 1 Create a MVC Application

Step 2 Install NuGet Packages

3. Create a Database and Table

4. Architecture

Entities

Product

Interfaces

IGenericRepository

IProductRepository

IUnitOfWork

Repository

ProductRepository

UnitOfWork

5. appsettings.json

6. Controller.

7. Program.cs

8. View

Load and Draw Table (Create)

Add and Edit Product (Add and Update)

Read and Delete

9. Test the application

Add a product

Edit a product

List and delete product

Search

Tags

Get $100 Free, sign up today

Popular Articles