RepletoryLib.Data.Dapper 1.0.0

RepletoryLib.Data.Dapper

Dapper-based data access with a fluent SQL builder, JOINs, and bulk insert/update/delete support.

Part of the RepletoryLib ecosystem -- standalone, reusable .NET 10 libraries with zero business logic.

Overview

RepletoryLib.Data.Dapper provides lightweight, high-performance data access using Dapper. It includes a managed connection context (IDapperContext), a fluent SQL builder (ISqlBuilder) for constructing parameterized queries, and bulk insert extensions for efficient batch operations.

Use this package when you need raw SQL performance or when EF Core's overhead isn't justified for read-heavy or reporting scenarios.

Key Features

• IDapperContext -- Managed database connection creation (sync and async)
• ISqlBuilder -- Fluent API for building SELECT, INSERT, UPDATE, DELETE queries with JOINs and parameterized values
• Bulk inserts -- Batch insert entities with configurable batch size
• Bulk updates -- Batch update entities by primary key
• Bulk deletes -- Batch delete rows by key values using WHERE IN
• SQL Server support -- Built on Microsoft.Data.SqlClient

Installation

dotnet add package RepletoryLib.Data.Dapper

Or add to your .csproj:

<PackageReference Include="RepletoryLib.Data.Dapper" Version="1.0.0" />

Note: RepletoryLib packages are published to a local BaGet feed. See the main repository README for feed configuration.

Dependencies

Prerequisites

• SQL Server instance (local or remote)
• Connection string configured in appsettings.json

Quick Start

using RepletoryLib.Data.Dapper;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddRepletoryDapper(builder.Configuration);

{
  "RepletoryDapper": {
    "ConnectionString": "Server=localhost,1433;Database=MyApp;User Id=sa;Password=Repletory@123!;TrustServerCertificate=true",
    "CommandTimeoutSeconds": 30,
    "EnableQueryLogging": false
  }
}

Configuration

DapperOptions

Section name: "RepletoryDapper"

Usage Examples

Executing Queries with IDapperContext

using RepletoryLib.Data.Dapper.Interfaces;

public class ProductRepository
{
    private readonly IDapperContext _context;

    public ProductRepository(IDapperContext context) => _context = context;

    public async Task<Product?> GetByIdAsync(Guid id)
    {
        using var connection = await _context.CreateConnectionAsync();
        return await connection.QuerySingleOrDefaultAsync<Product>(
            "SELECT * FROM Products WHERE Id = @Id AND IsDeleted = 0",
            new { Id = id });
    }

    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        using var connection = await _context.CreateConnectionAsync();
        return await connection.QueryAsync<Product>(
            "SELECT * FROM Products WHERE IsDeleted = 0 ORDER BY CreatedAt DESC");
    }
}

Building Queries with ISqlBuilder

using RepletoryLib.Data.Dapper.Interfaces;

public class ReportRepository
{
    private readonly IDapperContext _context;
    private readonly ISqlBuilder _sqlBuilder;

    public ReportRepository(IDapperContext context, ISqlBuilder sqlBuilder)
    {
        _context = context;
        _sqlBuilder = sqlBuilder;
    }

    public async Task<IEnumerable<Product>> SearchAsync(string? name, decimal? minPrice, int page, int pageSize)
    {
        _sqlBuilder
            .Select("Products", "Id, Name, Sku, Price, StockQuantity, CreatedAt")
            .Where("IsDeleted = 0");

        if (!string.IsNullOrEmpty(name))
            _sqlBuilder.And("Name LIKE @Name").WithParameter("Name", $"%{name}%");

        if (minPrice.HasValue)
            _sqlBuilder.And("Price >= @MinPrice").WithParameter("MinPrice", minPrice.Value);

        _sqlBuilder
            .OrderBy("CreatedAt", descending: true)
            .Paginate((page - 1) * pageSize, pageSize);

        var sql = _sqlBuilder.Build();
        var parameters = _sqlBuilder.GetParameters();

        using var connection = await _context.CreateConnectionAsync();
        return await connection.QueryAsync<Product>(sql, parameters);
    }
}

SELECT with JOINs

// INNER JOIN
_sqlBuilder
    .Select("Orders o", "o.Id, o.TotalPrice, c.Name AS CustomerName")
    .Join("Customers c", "c.Id = o.CustomerId")
    .Where("o.TotalPrice > @MinTotal")
    .WithParameter("MinTotal", 100m)
    .OrderBy("o.TotalPrice", descending: true);

using var conn = await _context.CreateConnectionAsync();
var results = await conn.QueryAsync(sqlBuilder.Build(), sqlBuilder.GetParameters());

// LEFT JOIN (includes orders even without a customer)
_sqlBuilder.Reset();
_sqlBuilder
    .Select("Orders o", "o.Id, o.TotalPrice, c.Name AS CustomerName")
    .LeftJoin("Customers c", "c.Id = o.CustomerId")
    .Where("o.IsDeleted = 0")
    .OrderBy("o.CreatedAt", descending: true)
    .Paginate(0, 50);

// Multiple JOINs
_sqlBuilder.Reset();
_sqlBuilder
    .Select("OrderProducts op", "op.Quantity, p.Name AS ProductName, o.Id AS OrderId")
    .Join("Orders o", "o.Id = op.OrderId")
    .Join("Products p", "p.Id = op.ProductId")
    .Where("o.CustomerId = @CustomerId")
    .WithParameter("CustomerId", customerId);

INSERT, UPDATE, DELETE with SqlBuilder

// INSERT
_sqlBuilder
    .Insert("Products", "Id, Name, Sku, Price", "@Id, @Name, @Sku, @Price")
    .WithParameter("Id", Guid.NewGuid())
    .WithParameter("Name", "Widget")
    .WithParameter("Sku", "SKU-001")
    .WithParameter("Price", 29.99m);

using var conn = await _context.CreateConnectionAsync();
await conn.ExecuteAsync(_sqlBuilder.Build(), _sqlBuilder.GetParameters());

// UPDATE
_sqlBuilder.Reset();
_sqlBuilder
    .Update("Products", "Price = @Price, UpdatedAt = @Now")
    .Where("Sku = @Sku")
    .WithParameter("Price", 24.99m)
    .WithParameter("Now", DateTime.UtcNow)
    .WithParameter("Sku", "SKU-001");

await conn.ExecuteAsync(_sqlBuilder.Build(), _sqlBuilder.GetParameters());

// DELETE
_sqlBuilder.Reset();
_sqlBuilder
    .Delete("Products")
    .Where("Id = @Id")
    .WithParameter("Id", productId);

await conn.ExecuteAsync(_sqlBuilder.Build(), _sqlBuilder.GetParameters());

Bulk Insert

using RepletoryLib.Data.Dapper.Extensions;

var products = Enumerable.Range(1, 10000).Select(i => new Product
{
    Id = Guid.NewGuid(),
    Name = $"Product {i}",
    Sku = $"SKU-{i:D5}",
    Price = i * 1.99m,
    StockQuantity = 100
});

// Bulk insert via context (creates connection internally)
int inserted = await _context.BulkInsertAsync("Products", products, batchSize: 500);

// Or with an existing connection and transaction
using var connection = await _context.CreateConnectionAsync();
using var transaction = connection.BeginTransaction();
int rows = await connection.BulkInsertAsync("Products", products, transaction, batchSize: 1000);
transaction.Commit();

Bulk Update

using RepletoryLib.Data.Dapper.Extensions;

var updatedProducts = new[]
{
    new { Id = id1, Name = "Widget Pro", Price = 29.99m, StockQuantity = 50 },
    new { Id = id2, Name = "Gadget Plus", Price = 49.99m, StockQuantity = 25 },
    new { Id = id3, Name = "Gizmo Max", Price = 99.99m, StockQuantity = 10 },
};

// Bulk update via context (matches rows by "Id" column by default)
int updated = await _context.BulkUpdateAsync("Products", updatedProducts);

// Specify a different key column
int updated2 = await _context.BulkUpdateAsync("Products", updatedProducts, keyColumn: "Sku");

// With an existing connection and transaction
using var connection = await _context.CreateConnectionAsync();
using var transaction = connection.BeginTransaction();
int rows = await connection.BulkUpdateAsync("Products", updatedProducts, "Id", transaction);
transaction.Commit();

Bulk Delete

using RepletoryLib.Data.Dapper.Extensions;

// Delete by Guid IDs
var idsToDelete = new List<Guid> { id1, id2, id3 };
int deleted = await _context.BulkDeleteAsync("Products", idsToDelete);

// Delete by integer IDs
var intIds = new List<int> { 1, 2, 3, 4, 5 };
int deleted2 = await _context.BulkDeleteAsync("Logs", intIds);

// Specify a different key column
int deleted3 = await _context.BulkDeleteAsync("Products", skus, keyColumn: "Sku");

// With an existing connection and transaction
using var connection = await _context.CreateConnectionAsync();
using var transaction = connection.BeginTransaction();
int rows = await connection.BulkDeleteAsync("Products", idsToDelete, "Id", transaction);
transaction.Commit();

API Reference

IDapperContext

ISqlBuilder

Bulk Extensions

Integration with Other RepletoryLib Packages

Testing

[Fact]
public void SqlBuilder_builds_select_with_pagination()
{
    var builder = new SqlBuilder();

    var sql = builder
        .Select("Products", "Id, Name, Price")
        .Where("IsDeleted = 0")
        .And("Price > @MinPrice")
        .WithParameter("MinPrice", 10m)
        .OrderBy("Price", descending: true)
        .Paginate(0, 20)
        .Build();

    sql.Should().Contain("SELECT Id, Name, Price FROM Products");
    sql.Should().Contain("WHERE IsDeleted = 0 AND Price > @MinPrice");
    sql.Should().Contain("ORDER BY Price DESC");
    sql.Should().Contain("OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY");
}

Troubleshooting

License

This project is licensed under the MIT License.

Copyright (c) 2024-2026 Repletory.

For complete documentation, infrastructure setup, and configuration reference, see the RepletoryLib main repository.