# Dapper

## Description

Dapper is the relational “micro-ORM” option in FlexBase: explicit SQL, minimal overhead, and high performance.

Flex wraps Dapper with **Flex Dapper** (a Dapper-first implementation of the **Flex MicroORM** abstraction). Your application code can stay provider-agnostic by using:

* `IFlexDapperRepository` for query/command helpers
* `FlexDapperSql` + `ProviderSqlMap` for provider-specific SQL variants
* Built-in soft-delete filtering (optional)
* Paging helpers
* Transaction helpers

If you prefer LINQ, change tracking, and migrations, see [Entity Framework Core](/data-and-providers/relational-db/entity-framework-core.md).

## When to Use Dapper

| Scenario                                                     | Recommendation                                                                             |
| ------------------------------------------------------------ | ------------------------------------------------------------------------------------------ |
| High-performance reads (lists, dashboards, reporting)        | ✅ Use Dapper/Flex Dapper                                                                   |
| Complex/hand-tuned SQL (CTEs, vendor features, query hints)  | ✅ Use Dapper/Flex Dapper                                                                   |
| Bulk operations (insert/update many rows)                    | ✅ Use Dapper/Flex Dapper                                                                   |
| Simple table-centric CRUD on `TFlex` entities                | ✅ Use `FlexDapperTableRepository<TEntity>`                                                 |
| Rich domain graphs + change tracking + navigation properties | Prefer [Entity Framework Core](/data-and-providers/relational-db/entity-framework-core.md) |

## Configuration in DI

```csharp
using Sumeru.Flex;

services.AddSingleton<IFlexDapperConnectionFactory, FlexDapperConnectionFactory>();

services.AddScoped(_ => new FlexDapperRepositoryOptions
{
    ProviderName = "SqlServer",
    ConnectionString = configuration.GetConnectionString("FlexWriteDb"),
    EnableSoftDeleteFilter = true,
    SoftDeleteColumnName = "IsDeleted"
});
```

## appsettings.json

Where you store the connection string is app-specific, but most apps keep it under standard connection strings:

```json
{
  "ConnectionStrings": {
    "FlexWriteDb": "Server=...;Database=...;User Id=...;Password=<store-in-secrets>;TrustServerCertificate=True"
  }
}
```

## Sample usage (Queries / Handlers)

The typical pattern is: **queries** call `QueryAsync`/`QuerySingleAsync`, and **handlers** call `ExecuteAsync`/`ExecuteScalarAsync`, all through interfaces.

### Query example (read model)

```csharp
using Sumeru.Flex;

public sealed class ListActiveUsers
{
    private readonly IFlexDapperRepository _db;

    public ListActiveUsers(IFlexDapperRepository db) => _db = db;

    public Task<IReadOnlyList<UserListItemDto>> FetchAsync()
    {
        FlexDapperSql sql = "SELECT Id, UserName FROM Users WHERE IsActive = 1 ORDER BY UserName";
        return _db.QueryAsync<UserListItemDto>(sql);
    }
}

public sealed class UserListItemDto
{
    public string Id { get; set; }
    public string UserName { get; set; }
}
```

### Handler example (command)

```csharp
using Sumeru.Flex;

public sealed class CreateUserHandler
{
    private readonly IFlexDapperRepository _db;

    public CreateUserHandler(IFlexDapperRepository db) => _db = db;

    public Task<int?> ExecuteAsync(CreateUserCommand cmd)
    {
        FlexDapperSql sql = """
            INSERT INTO Users (UserName, Email, IsActive)
            VALUES (@UserName, @Email, 1);
            SELECT CAST(SCOPE_IDENTITY() as int);
            """;

        return _db.ExecuteScalarAsync<int>(sql, new { cmd.UserName, cmd.Email });
    }
}

public sealed record CreateUserCommand(string UserName, string Email);
```

## Common Patterns

### Custom SQL (Provider-Agnostic)

Use `ProviderSqlMap` + `FlexDapperSql.FromProviderMap(...)` to keep provider differences in one place.

```csharp
using Sumeru.Flex;

public sealed class ListRecentAccounts
{
    private readonly IFlexDapperRepository _db;

    public ListRecentAccounts(IFlexDapperRepository db) => _db = db;

    public Task<IReadOnlyList<Accountability>> FetchAsync(int take)
    {
        var sql = FlexDapperSql.FromProviderMap(
            ProviderSqlMap.Create(map =>
                map.ForSqlServer($"SELECT TOP {take} * FROM Accountabilities ORDER BY CreatedDate DESC")
                   .ForPostgres($"SELECT * FROM \"Accountabilities\" ORDER BY \"CreatedDate\" DESC LIMIT {take}")));

        return _db.QueryAsync<Accountability>(sql);
    }
}
```

### Paging

For custom queries, use `QueryPageAsync` with `FlexDapperPagedQuery`:

```csharp
public sealed class GetUsersPaged
{
    private readonly IFlexDapperRepository _db;

    public GetUsersPaged(IFlexDapperRepository db) => _db = db;

    public Task<FlexDapperPagedResult<User>> FetchAsync(int pageNumber, int pageSize)
    {
        var query = new FlexDapperPagedQuery(
            dataSql: "SELECT * FROM Users ORDER BY Id OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY",
            countSql: "SELECT COUNT(1) FROM Users",
            pageNumber: pageNumber,
            pageSize: pageSize);

        return _db.QueryPageAsync<User>(query);
    }
}
```

### Transactions

Use `WithTransactionAsync` to ensure atomic multi-statement work.

```csharp
await _db.WithTransactionAsync(async (connection, tx) =>
{
    await connection.ExecuteAsync("update ...", transaction: tx);
    await connection.ExecuteAsync("insert ...", transaction: tx);
    return true;
});
```

### Soft Delete Filter

* Enable filtering with `FlexDapperRepositoryOptions.EnableSoftDeleteFilter` and set `SoftDeleteColumnName` (commonly `IsDeleted`).
* `FlexDapperTableRepository<TEntity>` automatically appends the soft-delete predicate when the filter is enabled.
* To temporarily include soft-deleted rows:

```csharp
using (_db.SuppressSoftDeleteFilter())
{
    var all = await _db.QueryAsync<MyEntity>("SELECT * FROM MyEntities");
}
```

## Testing

* For unit tests, inject a fake `IFlexDapperConnectionFactory` and/or assert SQL text + parameters.
* For integration tests, use a real database (local/container) and reuse the same repository registrations.

## See Also

* [Relational Database Overview](/data-and-providers/relational-db.md)
* [Entity Framework Core](/data-and-providers/relational-db/entity-framework-core.md)
* [Provider SQL Map](https://github.com/sumeru-flexbase/flexbase-docs/blob/main/ReferenceDocs/README_ProviderSqlMap.md)
* [Full-Text Search](/data-and-providers/data-stores/search-store-full-text.md)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.flexbase.in/data-and-providers/relational-db/dapper.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.