CRUD with Dapper in ASP.NET Core

What is Dapper?

Dapper is a fast, lightweight, open-source Object-Relational Mapper (ORM) that helps to minimize code for database operations. Dapper works well with .NET Framework, .NET Core, and .NET 8, simplifying database interactions, especially for scenarios where performance is critical.

Creating an ASP.NET Core MVC Project Using Dapper with SQL Server

1. Create the Project

1. Open Visual Studio 2022.
2. Create a new ASP.NET Core Web Application.
3. Select MVC as the project template and .NET 8 as the version.
4. Place the solution and project in the same directory for simplicity.

If you have multiple projects, place the solution file outside the project directory.

2. Set Up the Database

Create a database table for books. Here’s an SQL script to create the Books table:

CREATE TABLE [dbo].[Books] (
    [BookID] INT IDENTITY(1,1) PRIMARY KEY,
    [Title] NVARCHAR(100) NOT NULL,
    [Author] NVARCHAR(100) NOT NULL,
    [PublishedYear] INT NOT NULL
);

3. Insert Sample Records

Here are some sample records to insert into the Books table:

INSERT INTO Books (Title, Author, PublishedYear) 
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 1925);

INSERT INTO Books (Title, Author, PublishedYear) 
VALUES ('Moby Dick', 'Herman Melville', 1851);

INSERT INTO Books (Title, Author, PublishedYear) 
VALUES ('1984', 'George Orwell', 1949);

4. Create the Model Class

Add a new class called Book.cs to represent the data model.

namespace DapperNet8MvcCore
{
    public class Book
    {
        public int BookID { get; set; }
        public string Title { get; set; } = string.Empty;
        public string Author { get; set; } = string.Empty;
        public int PublishedYear { get; set; }
    }
}

5. Install Dapper via NuGet

Right-click the project and select Manage NuGet Packages. Install the following packages:

• Dapper
• System.Data.SqlClient

6. Add Connection String

In the appsettings.json, add the connection string for SQL Server:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=LibraryDB;User Id=sa;Password=your_password;"
  }
}

7. Create the Interface (IBookRepository.cs)

Define the interface for the repository:

namespace DapperNet8MvcCore
{
    public interface IBookRepository
    {
        IEnumerable<Book> GetAllBooks();
        Book GetBookById(int id);
        int AddBook(Book book);
        bool UpdateBook(Book book);
        bool DeleteBook(int id);
    }
}

8. Create the Repository (BookRepository.cs)

Now, implement the repository using Dapper for database interactions:

using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace DapperNet8MvcCore
{
    public class BookRepository : IBookRepository
    {
        private readonly IConfiguration _configuration;

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

        public IEnumerable<Book> GetAllBooks()
        {
            using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                return connection.Query<Book>("SELECT * FROM Books").ToList();
            }
        }

        public Book GetBookById(int id)
        {
            using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                return connection.QueryFirstOrDefault<Book>("SELECT * FROM Books WHERE BookID = @Id", new { Id = id });
            }
        }

        public int AddBook(Book book)
        {
            using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                string query = "INSERT INTO Books (Title, Author, PublishedYear) VALUES (@Title, @Author, @PublishedYear); SELECT CAST(SCOPE_IDENTITY() AS INT)";
                return connection.ExecuteScalar<int>(query, book);
            }
        }

        public bool UpdateBook(Book book)
        {
            using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                string query = "UPDATE Books SET Title = @Title, Author = @Author, PublishedYear = @PublishedYear WHERE BookID = @BookID";
                int rowsAffected = connection.Execute(query, book);
                return rowsAffected > 0;
            }
        }

        public bool DeleteBook(int id)
        {
            using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
            {
                connection.Open();
                string query = "DELETE FROM Books WHERE BookID = @Id";
                int rowsAffected = connection.Execute(query, new { Id = id });
                return rowsAffected > 0;
            }
        }
    }
}

9. Create the BookController

Now, create the controller to handle CRUD operations.

using Microsoft.AspNetCore.Mvc;

namespace DapperNet8MvcCore.Controllers
{
    public class BookController : Controller
    {
        private readonly IBookRepository _bookRepository;

        public BookController(IBookRepository bookRepository)
        {
            _bookRepository = bookRepository;
        }

        public ActionResult Index()
        {
            var books = _bookRepository.GetAllBooks();
            return View(books);
        }

        public ActionResult Details(int id)
        {
            var book = _bookRepository.GetBookById(id);
            return View(book);
        }

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

        [HttpPost]
        public ActionResult Create(Book book)
        {
            try
            {
                _bookRepository.AddBook(book);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        public ActionResult Edit(int id)
        {
            var book = _bookRepository.GetBookById(id);
            return View(book);
        }

        [HttpPost]
        public ActionResult Edit(Book book)
        {
            try
            {
                _bookRepository.UpdateBook(book);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        public ActionResult Delete(int id)
        {
            var book = _bookRepository.GetBookById(id);
            return View(book);
        }

        [HttpPost]
        [ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {
            try
            {
                _bookRepository.DeleteBook(id);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

10. Create Razor Views

Use Scaffolding in Visual Studio to generate the Razor views for Index, Create, Edit, Delete, and Details actions.

Below is an example of the Index.cshtml view:

@model IEnumerable<Book>

<h2>Books</h2>

<a href="@Url.Action("Create")">Create New Book</a>
<hr />
<table>
    <tr>
        <th>Title</th>
        <th>Author</th>
        <th>Published Year</th>
        <th>Actions</th>
    </tr>
    @foreach (var item in Model)
    {
        <tr>
            <td>@item.Title</td>
            <td>@item.Author</td>
            <td>@item.PublishedYear</td>
            <td>
                <a href="@Url.Action("Details", new { id = item.BookID })">Details</a> |
                <a href="@Url.Action("Edit", new { id = item.BookID })">Edit</a> |
                <a href="@Url.Action("Delete", new { id = item.BookID })">Delete</a>
            </td>
        </tr>
    }
</table>

With these steps, you've created a simple CRUD application for managing a Book collection using Dapper with ASP.NET Core MVC and SQL Server.