DEV Community

Sardar Mudassar Ali Khan
Sardar Mudassar Ali Khan

Posted on • Edited on

14

CRUD In Asp.NET Core Using Dapper With Practical Example

CRUD (Create, Read, Update, Delete) operations in ASP.NET Core using Dapper. Dapper is a lightweight ORM (Object-Relational Mapping) tool that can be used to simplify database operations in.NET applications.

To get started, you'll need to have ASP.NET Core and Dapper installed in your project. You can install Dapper via NuGet by running the following command in the NuGet Package Manager Console:

Install-Package Dapper
Enter fullscreen mode Exit fullscreen mode

Once you have everything set up, let's dive into the code. Here's an example of how to perform CRUD operations using Dapper in ASP.NET Core:

  1. Create a Model class representing your data entity. For example, let's create a Product class:
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}
Enter fullscreen mode Exit fullscreen mode
  1. Create a repository class that will handle the database operations using Dapper. Here's an example of a ProductRepository class:
using Dapper;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

public class ProductRepository
{
    private readonly string _connectionString;

    public ProductRepository(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public IDbConnection Connection
    {
        get
        {
            return new SqlConnection(_connectionString);
        }
    }

    public async Task<IEnumerable<Product>> GetAllProductsAsync()
    {
        using (IDbConnection conn = Connection)
        {
            conn.Open();
            string query = "SELECT * FROM Products";
            return await conn.QueryAsync<Product>(query);
        }
    }

    public async Task<Product> GetProductByIdAsync(int id)
    {
        using (IDbConnection conn = Connection)
        {
            conn.Open();
            string query = "SELECT * FROM Products WHERE Id = @Id";
            return await conn.QuerySingleOrDefaultAsync<Product>(query, new { Id = id });
        }
    }

    public async Task<int> AddProductAsync(Product product)
    {
        using (IDbConnection conn = Connection)
        {
            conn.Open();
            string query = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT CAST(SCOPE_IDENTITY() as int)";
            return await conn.ExecuteScalarAsync<int>(query, product);
        }
    }

    public async Task<bool> UpdateProductAsync(Product product)
    {
        using (IDbConnection conn = Connection)
        {
            conn.Open();
            string query = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id";
            int rowsAffected = await conn.ExecuteAsync(query, product);
            return rowsAffected > 0;
        }
    }

    public async Task<bool> DeleteProductAsync(int id)
    {
        using (IDbConnection conn = Connection)
        {
            conn.Open();
            string query = "DELETE FROM Products WHERE Id = @Id";
            int rowsAffected = await conn.ExecuteAsync(query, new { Id = id });
            return rowsAffected > 0;
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

In the ProductRepository class, we establish a database connection, define methods for performing CRUD operations, and use Dapper's extension methods for executing SQL queries.

  1. In your ASP.NET Core controller, inject the ProductRepository and use it to perform CRUD operations. Here's an example of a controller that uses the ProductRepository:

csharp
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;

Enter fullscreen mode Exit fullscreen mode

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more