Dapper CRUD in ASP.NET MVC with Code Examples

In this tutorial, we'll explore how to implement CRUD (Create, Read, Update, Delete) operations in an ASP.NET MVC application using Dapper, a lightweight Object-Relational Mapping (ORM) library for .NET.

Prerequisites

Before diving into the tutorial, ensure you have the following:

Visual Studio installed
Basic knowledge of ASP.NET MVC
SQL Server or any other database engine of your choice

Step 1: Create a New ASP.NET MVC Project

Open Visual Studio.
Click on "Create a new project."
Choose "ASP.NET Web Application."
Select the "ASP.NET MVC" template.
Click "Create."

Step 2: Install Dapper

Right-click on your project in Solution Explorer.
Select "Manage NuGet Packages."
Search for "Dapper" and install the Dapper package.

Step 3: Create a Database

Create a database with a table for the entity you want to manage. For example, let's create a "Products" table.

    CREATE TABLE Products (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(255),
    Price DECIMAL(18, 2)
    );

Step 4: Create a Model

Create a model class representing the entity you want to manage (e.g., Product).

 // Models/Product.cs
 public class Product
 {
     public int Id { get; set; }

     [Required]
     public string Name { get; set; }

     [Required]
     public decimal? Price { get; set; }
 }

Step 5: Configure Database Connection

Configure your database connection in the web.config or appsettings.json file. For simplicity, you can use a connection string in web.config:

<!-- Inside the <configuration> element in web.config -->
<connectionStrings>
    <add name="DefaultConnection" connectionString="YourConnectionStringHere" />
</connectionStrings>

Step 6: Implement Dapper CRUD Operations

Create a class to handle CRUD operations using Dapper.

// DataAccess/ProductRepository.cs
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;

public class ProductRepository
{
    private readonly string connectionString;

    public ProductRepository(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public IEnumerable GetProducts()
    {
        using (IDbConnection dbConnection = new SqlConnection(connectionString))
        {
            dbConnection.Open();
            return dbConnection.Query("SELECT * FROM Products");
        }
    }

    public Product GetProductById(int id)
    {
        using (IDbConnection dbConnection = new SqlConnection(connectionString))
        {
            dbConnection.Open();
            return dbConnection.QueryFirstOrDefault("SELECT * FROM Products WHERE Id = @Id", new { Id = id });
        }
    }

    public void InsertProduct(Product product)
    {
        using (IDbConnection dbConnection = new SqlConnection(connectionString))
        {
            dbConnection.Open();
            dbConnection.Execute("INSERT INTO Products (Name, Price) VALUES (@Name, @Price)", product);
        }
    }

    public void UpdateProduct(Product product)
    {
        using (IDbConnection dbConnection = new SqlConnection(connectionString))
        {
            dbConnection.Open();
            dbConnection.Execute("UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id", product);
        }
    }

    public void DeleteProduct(int id)
    {
        using (IDbConnection dbConnection = new SqlConnection(connectionString))
        {
            dbConnection.Open();
            dbConnection.Execute("DELETE FROM Products WHERE Id = @Id", new { Id = id });
        }
    }
}

Step 7: Use Dapper in Controller

Now, use the ProductRepository in your controller to perform CRUD operations.

// Controllers/ProductController.cs
using System.Web.Mvc;

public class ProductController : Controller
{
    private readonly ProductRepository productRepository;

    public ProductController()
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        this.productRepository = new ProductRepository(connectionString);
    }

    public ActionResult Index()
    {
        var products = productRepository.GetProducts();
        return View(products);
    }

    public ActionResult Details(int id)
    {
        var product = productRepository.GetProductById(id);
        return View(product);
    }

    public ActionResult Create()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Create(Product product)
    {
        if (ModelState.IsValid)
        {
            productRepository.InsertProduct(product);
            return RedirectToAction("Index");
        }
        // If model validation fails, return to the create view with validation errors.
        return View(product);
    }

    public ActionResult Edit(int id)
    {
        var product = productRepository.GetProductById(id);
        return View(product);
    }

    [HttpPost]
    public ActionResult Edit(Product product)
    {
        productRepository.UpdateProduct(product);
        return RedirectToAction("Index");
    }

    public ActionResult Delete(int id)
    {
        var product = productRepository.GetProductById(id);
        return View(product);
    }

    [HttpPost]
    public ActionResult DeleteConfirm(int id)
    {
        productRepository.DeleteProduct(id);
        return RedirectToAction("Index");
    }
}

Step 8: Create Views

Create views for your controller actions (Index, Details, Create, Edit, Delete).

Views/Product/Index.cshtml

@model IEnumerable<Product>

<h2>Product List</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Price)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Price)
            </td>
            <td>
                @Html.ActionLink("Details", "Details", new { id = item.Id })
                |
                @Html.ActionLink("Edit", "Edit", new { id = item.Id })
                |
                @Html.ActionLink("Delete", "Delete", new { id = item.Id })
            </td>
        </tr>
    }
</table>

Views/Product/Details.cshtml

@model Product

<h2>Product Details</h2>

<div>
    <h4>Product</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Name)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Name)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Price)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Price)
        </dd>
    </dl>
</div>
<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.Id }) |
    @Html.ActionLink("Back to List", "Index")
</p>

Views/Product/Create.cshtml

@model Product

<h2>Create Product</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Price, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Price, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Price, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

Views/Product/Edit.cshtml

@model Product

<h2>Edit Product</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.Id)
        <div class="form-group">
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Price, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Price, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Price, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

Views/Product/Delete.cshtml

@model Product

<h2>Delete Product</h2>

<div>
    <h4>Are you sure you want to delete this?</h4>
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Name)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Name)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Price)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Price)
        </dd>
    </dl>

    @using (Html.BeginForm("DeleteConfirm", "Product", new { id = Model.Id }, FormMethod.Post))
 {
     @Html.AntiForgeryToken()
     <input type="submit" value="Delete" class="btn btn-danger" />
     @Html.ActionLink("Cancel", "Index")
 }
</div>

Using Dapper in ASP.NET MVC makes dealing with data easier and faster. Dapper is like a helpful tool that simplifies how your web applications connect to databases. It not only makes your code easier to read but also helps your apps run smoother. So, as you dive into using Dapper, enjoy the simplicity it brings to your ASP.NET MVC projects.

Give your valuable comments.
Name
Email
Comment
5 + 5 =