# 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](https://docs.flexbase.in/data-and-providers/relational-db/entity-framework-core).

## 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](https://docs.flexbase.in/data-and-providers/relational-db/entity-framework-core) |

## 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](https://docs.flexbase.in/data-and-providers/relational-db)
* [Entity Framework Core](https://docs.flexbase.in/data-and-providers/relational-db/entity-framework-core)
* [Provider SQL Map](https://github.com/sumeru-flexbase/flexbase-docs/blob/main/ReferenceDocs/README_ProviderSqlMap.md)
* [Full-Text Search](https://docs.flexbase.in/data-and-providers/data-stores/search-store-full-text)