Creating CRUD API with Dapper in ASP.NET Core

Introduction

In this example, I'll demonstrate how to perform CRUD (Create, Read, Update, Delete) operations using Dapper in an ASP.NET Core Web API with a real-world use case model named CSharpCornerArticle. This model represents articles, and we'll create endpoints to manage these articles.

First, make sure you have created an ASP.NET Core Web API project and added the Dapper NuGet package. You'll also need a database connection.

Create the CSharpCornerArticle model

public class CSharpCornerArticle
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateTime PublishedDate { get; set; }
}

Create a CSharpCornerArticleRepository class to interact with the database using Dapper

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;

Author: Sardar Mudassar Ali Khan
public class CSharpCornerArticleRepository
{
    private readonly IDbConnection _dbConnection;

    public CSharpCornerArticleRepository(string connectionString)
    {
        _dbConnection = new SqlConnection(connectionString);
    }

    public async Task<IEnumerable<CSharpCornerArticle>> GetAllArticlesAsync()
    {
        string query = "SELECT * FROM CSharpCornerArticles";
        return await _dbConnection.QueryAsync<CSharpCornerArticle>(query);
    }

    public async Task<CSharpCornerArticle> GetArticleByIdAsync(int id)
    {
        string query = "SELECT * FROM CSharpCornerArticles WHERE Id = @Id";
        return await _dbConnection.QueryFirstOrDefaultAsync<CSharpCornerArticle>(query, new { Id = id });
    }

    public async Task<int> CreateArticleAsync(CSharpCornerArticle article)
    {
        string query = "INSERT INTO CSharpCornerArticles (Title, Content, PublishedDate) VALUES (@Title, @Content, @PublishedDate); SELECT CAST(SCOPE_IDENTITY() as int)";
        return await _dbConnection.ExecuteScalarAsync<int>(query, article);
    }

    public async Task<bool> UpdateArticleAsync(CSharpCornerArticle article)
    {
        string query = "UPDATE CSharpCornerArticles SET Title = @Title, Content = @Content, PublishedDate = @PublishedDate WHERE Id = @Id";
        int affectedRows = await _dbConnection.ExecuteAsync(query, article);
        return affectedRows > 0;
    }

    public async Task<bool> DeleteArticleAsync(int id)
    {
        string query = "DELETE FROM CSharpCornerArticles WHERE Id = @Id";
        int affectedRows = await _dbConnection.ExecuteAsync(query, new { Id = id });
        return affectedRows > 0;
    }
}

Create a controller to handle CRUD operations for CSharpCornerArticle

using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

Author: Sardar Mudassar Ali Khan

[Route("api/[controller]")]
[ApiController]
public class CSharpCornerArticlesController : ControllerBase
{
    private readonly CSharpCornerArticleRepository _repository;

    public CSharpCornerArticlesController(CSharpCornerArticleRepository repository)
    {
        _repository = repository;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<CSharpCornerArticle>>> GetAllArticles()
    {
        var articles = await _repository.GetAllArticlesAsync();
        return Ok(articles);
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<CSharpCornerArticle>> GetArticle(int id)
    {
        var article = await _repository.GetArticleByIdAsync(id);
        if (article == null)
        {
            return NotFound();
        }
        return Ok(article);
    }

    [HttpPost]
    public async Task<ActionResult<CSharpCornerArticle>> CreateArticle(CSharpCornerArticle article)
    {
        article.PublishedDate = DateTime.UtcNow;
        var newArticleId = await _repository.CreateArticleAsync(article);
        article.Id = newArticleId;
        return CreatedAtAction(nameof(GetArticle), new { id = newArticleId }, article);
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> UpdateArticle(int id, CSharpCornerArticle article)
    {
        var existingArticle = await _repository.GetArticleByIdAsync(id);
        if (existingArticle == null)
        {
            return NotFound();
        }

        article.Id = id;
        if (await _repository.UpdateArticleAsync(article))
        {
            return NoContent();
        }
        return StatusCode(500, "Internal server error");
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteArticle(int id)
    {
        var existingArticle = await _repository.GetArticleByIdAsync(id);
        if (existingArticle == null)
        {
            return NotFound();
        }

        if (await _repository.DeleteArticleAsync(id))
        {
            return NoContent();
        }
        return StatusCode(500, "Internal server error");
    }
}

Configure the repository and connection string in Startup.cs

public void ConfigureServices(IServiceCollection services)
{

    string connectionString = Configuration.GetConnectionString("DefaultConnection");
    services.AddSingleton<CSharpCornerArticleRepository>(provider => new CSharpCornerArticleRepository(connectionString));
   
}

Ensure that you have added a connection string to your appsettings.json file.

Migrate and update your database with the necessary table structure using Entity Framework Core or another ORM.

Now, you can use the /api/CSharpCornerArticles endpoints to perform CRUD operations on CSharpCornerArticle objects in your ASP.NET Core Web API.

Conclusion

In this article, we have demonstrated how to implement CRUD (Create, Read, Update, Delete) operations using Dapper in an ASP.NET Core Web API with a real-world use case.

1. Model Creation: We defined a CSharpCornerArticle model class to represent articles with properties like Id, Title, Content, and PublishedDate.
2. Repository for Data Access: We created a CSharpCornerArticleRepository class responsible for interacting with the database using Dapper. This class contains methods for querying, creating, updating, and deleting articles.
3. Controller for API Endpoints: We implemented a controller class (CSharpCornerArticlesController) that handles HTTP requests for CRUD operations. Each CRUD operation corresponds to a specific HTTP method (GET, POST, PUT, DELETE) and interacts with the repository.
4. Dependency Injection: We configured the repository in the Startup.cs file, injecting it as a singleton into the controller.
5. Database Configuration: We assumed that you have set up a database and configured a connection string in your appsettings.json file. You should also have migrated and updated your database schema accordingly.
6. Error Handling: In a real-world application, you should add proper error handling, validation, and authentication/authorization mechanisms to secure and improve the reliability of your API.

By following these steps, you can create a fully functional ASP.NET Core Web API that performs CRUD operations on CSharpCornerArticle objects using Dapper. This provides a foundation for building more complex and feature-rich web applications.