1. User's API
2. Swagger for API's Endpoint
3. Dapper and Dapper Plus
4. AutoMapper
5. FluentValidation
6. EPPlus

These instructions will get you to setup the project, install sdk and add package (CLI or Package manager console).

• Visual Studio 2022 or higher
• .NET 8.x SDK

• Install .net SDK 8
  Download .NET SDK here.
• Create new Web API's project (Please create main project with empty and add below to the main project)

dotnet new webapi –-name DapperWebAPIProject

• Add package to DapperWebAPIProject's project
  • Dapper
    dotnet add package Dapper
  • Dapper Plus
    dotnet add package Z.Dapper.Plus
  • Npgsql
    dotnet add package Npgsql
  • EPPlus
    dotnet add package EPPlus
  • FluentValidation
    dotnet add package FluentValidation
  • Newtonsoft Json
    dotnet add package Newtonsoft.Json
  • Automaper

dotnet add package AutoMapper.Extensions.Microsoft.DependencyInjection

           

What is Dapper?

• Lightweight ORM: Dapper is a micro ORM developed by the Stack Overflow team. It provides a simple way to map .NET objects to database tables, without the overhead of a full-fledged ORM like Entity Framework.
• Extension Methods: It operates as a set of extension methods for IDbConnection, which is the interface that represents a connection to a database in ADO.NET.
• No Context: Unlike Entity Framework, Dapper doesn’t use a context object to track changes. Instead, it focuses on executing raw SQL queries and mapping the results to strongly-typed objects.

Key Features

• Performance: Dapper is designed to be fast. It performs almost as fast as using raw ADO.NET queries.
• SQL-Centric: Since it’s SQL-centric, you write SQL queries directly in your code. This gives you complete control over the queries executed against the database.
• Supports Multiple Databases: Dapper works with any database that supports ADO.NET, including SQL Server, MySQL, SQLite, PostgreSQL, and others.
• Strong Typing: Dapper maps results from SQL queries to strongly-typed .NET objects, providing compile-time checking and IntelliSense in your IDE.

Basic Usage

• Querying Data: In this example, Dapper maps the results of the SELECT query to a list of users objects.

using (var connection = new SqlConnection(connectionString))
{
    var users = connection.Query<User>("SELECT * FROM users").ToList();
}

• Executing Commands: The Execute method is used for queries that don’t return a result set (e.g., INSERT, UPDATE, DELETE).

using (var connection = new SqlConnection(connectionString))
{
    var affectedRows = connection.Execute("UPDATE users SET Name = @Name WHERE Id = @Id", new { Name = "John", Id = 1 });
}

• Parameterized Queries: Dapper supports parameterized queries, which help prevent SQL injection attacks.

Advanced Features

• Multi-Mapping: Dapper can map complex queries that return multiple result sets or join multiple tables to different types of objects.
• Stored Procedures: Dapper supports the execution of stored procedures.
• Transactions: Dapper can execute commands within a transaction, ensuring atomicity.
• Bulk Operations: While Dapper doesn’t natively support bulk operations like Entity Framework, you can execute bulk inserts or updates using raw SQL.

Use Cases

• Performance-Critical Applications: Dapper is ideal for scenarios where you need high-performance data access, such as in web APIs or services that require low latency.
• Microservices: Its lightweight nature makes it a good fit for microservices, where the overhead of a full ORM might be unnecessary.
• Legacy Systems: Dapper can be easily integrated into legacy systems that already use ADO.NET.

Limitations

• Manual SQL: While writing SQL manually provides control and flexibility, it also requires more effort and knowledge of SQL.
• No Change Tracking: Dapper doesn’t track changes to objects, so you must manually manage changes and updates.
• Limited Abstraction: Unlike Entity Framework, Dapper doesn’t abstract the database schema, so any schema changes might require corresponding changes in your queries.

Getting Started

Let's start to configuation on DataContext with dapper.

using System.Data;
using Npgsql;
using Dapper;

namespace DapperWebAPIProject.Data;
public class DataContext
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public DataContext(IConfiguration configuration)
    {
        _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
        _connectionString = _configuration.GetConnectionString("DefaultConnection")
                    ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
    }

    public IDbConnection CreateConnection()
    {
        return new NpgsqlConnection(_connectionString);
    }

    public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object? parameters = null)
    {
        using (var connection = CreateConnection())
        {
            return await connection.QueryAsync<T>(sql, parameters);
        }
    }
    public async Task<T> QuerySingleAsync<T>(string sql, object? parameters = null) where T : class
    {
        using (var connection = CreateConnection())
        {
            var result = await connection.QuerySingleOrDefaultAsync<T>(sql, parameters);
            if (result == null)
            {
                throw new InvalidOperationException("The query returned no results.");
            }
            return result;
        }
    }

    public async Task<int> InsertAsync(string sql, object parameters)
    {
        using (var connection = CreateConnection())
        {
            return await connection.ExecuteAsync(sql, parameters);
        }
    }

    public async Task<int> UpdateAsync(string sql, object parameters)
    {
        using (var connection = CreateConnection())
        {
            return await connection.ExecuteAsync(sql, parameters);
        }
    }

    public async Task<int> DeleteAsync(string sql, object parameters)
    {
        using (var connection = CreateConnection())
        {
            return await connection.ExecuteAsync(sql, parameters);
        }
    }

    public async Task<int> ExecuteAsync(string sql, object? parameters = null)
    {
        using (var connection = CreateConnection())
        {
            return await connection.ExecuteAsync(sql, parameters);
        }
    }

    public async Task<T> ExecuteScalarAsync<T>(string sql, object? parameters = null)
    {
        using (var connection = CreateConnection())
        {
            var result = await connection.ExecuteScalarAsync<T>(sql, parameters);
            if (result == null)
            {
                throw new InvalidOperationException("The query returned no results.");
            }
            return result;
        }
    }
}

Let add to 'Program.cs' :

builder.Services.AddSingleton<DataContext>();

Look good now so I'm starting to create Repository.

I am create IUserRepository interface for hold all my crud on User entity

using DapperWebAPIProject.Dto.Request;
using DapperWebAPIProject.Dto.Response;
using DapperWebAPIProject.Entity;

namespace DapperWebAPIProject.Repository;

public interface IUserRepository { 
    Task<PaginatedResponseDto<User>> GetUsers(PaginationParamsRequestDto paginationParamsRequestDto);
    Task<User> GetUser(int id);
    Task<int> InsertUser(User user);
    Task<int> UpdateUser(User user);
    Task<int> DeleteUser(int id);
    Task<IEnumerable<User>> BulkInsertUpload(IEnumerable<User> users);
    Task<IEnumerable<User>> BulkMergeUpload(IEnumerable<User> users);
}

After IUserRepository have been create. So I can create UserRepository for give definition to IUserRepository as DI (Dependency Injection).

using DapperWebAPIProject.Entity;
using DapperWebAPIProject.Data;
using Z.Dapper.Plus;
using System.Text;
using DapperWebAPIProject.Dto.Request;
using DapperWebAPIProject.Dto.Response;

namespace DapperWebAPIProject.Repository;

public class UserRepository : IUserRepository {

    private readonly ILogger<UserRepository> _logger;
    private readonly DataContext _dataContext;
    public UserRepository(ILogger<UserRepository> logger, DataContext dataContext) {
        _logger = logger;
        _dataContext = dataContext;
    }

    public async Task<PaginatedResponseDto<User>> GetUsers(PaginationParamsRequestDto paginationParamsRequestDto) {
        string sql = @"
                SELECT * 
                FROM users
                ORDER BY id
                LIMIT @PageSize
                OFFSET @Offset";
        var content = await _dataContext.QueryAsync<User>(sql, new 
            { 
                PageSize = paginationParamsRequestDto.PageSize, 
                Offset = paginationParamsRequestDto.Offset 
            });

        string countSql = "SELECT COUNT(*) FROM users";
        int totalRecords = await _dataContext.ExecuteScalarAsync<int>(countSql);
        return new PaginatedResponseDto<User>
            {
                PageNumber = paginationParamsRequestDto.PageNumber,
                PageSize = paginationParamsRequestDto.PageSize,
                TotalRecords = totalRecords,
                TotalPages = (int)Math.Ceiling((double)totalRecords / paginationParamsRequestDto.PageSize),
                Data = content
            };
    }

    public async Task<User> GetUser(int id) {
        return await _dataContext.QuerySingleAsync<User>("SELECT * FROM users WHERE id = @id", new { id = id });
    }

    public async Task<int> InsertUser(User user) {
        return await _dataContext.InsertAsync("INSERT INTO users (name, email) VALUES (@name, @email)", user);
    }

    public async Task<int> UpdateUser(User user) { 
        return await _dataContext.UpdateAsync("UPDATE users SET name = @name, email = @email WHERE id = @id", user);
    }

    public async Task<int> DeleteUser(int id) {
        return await _dataContext.DeleteAsync("DELETE FROM users WHERE id = @id", new { id = id });
    }

    public async Task<IEnumerable<User>> BulkInsertUpload(IEnumerable<User> users)
    {
        using (var connection = _dataContext.CreateConnection())
        {
            var batchId = Guid.NewGuid();
            var userList = users.Select(user => {
                user.BatchId = batchId;
                return user;
            });
            await connection.BulkInsertAsync("users", userList);
            return await _dataContext.QueryAsync<User>("SELECT * FROM users WHERE batch_id = @BatchId", new { BatchId = batchId });
        }
    }
    public async Task<IEnumerable<User>> BulkMergeUpload(IEnumerable<User> users)
    {
        using (var connection = _dataContext.CreateConnection())
        {
            var batchId = Guid.NewGuid();
            var userList = users.Select(user => {
                user.BatchId = batchId;
                return user;
            });
            DapperPlusManager.Entity<User>("users")
                .Identity(x => x.Id, true)
                .IgnoreOnMergeUpdate(x => x.BatchId);
            var sb = new StringBuilder();
            await connection
                .UseBulkOptions(options => 
                { 
                    options.Log = s => sb.AppendLine(s);
                })
                .BulkMergeAsync("users", userList);
            _logger.LogInformation(sb.ToString());
            return await _dataContext.QueryAsync<User>("SELECT * FROM users WHERE batch_id = @BatchId or id = ANY(@ids)", new { BatchId = batchId, ids = users.Select(user => user.Id).ToList() });
        }
    }
}

In this example I also add Dapper Plus for bulk operation for performance instead of insert with looping. Also please check this url for details about Dapper Plus: here 

After everything is look good on repository please don't forget to add it to 'Program.cs' while it's DI (Dependency Injection).

builder.Services.AddScoped<IUserRepository, UserRepository>();

Perfect! so you can call it to your service right now...

Summary 

Download the source code for the sample application with Dapper and Dapper Plus.