FclEx.Dapper 2.4.0

FclEx.Dapper

FclEx.Dapper adds focused, cache-aware CRUD and transaction helpers to Dapper while keeping connections, transactions, SQL providers, and execution boundaries visible to the caller. It is not an ORM: it does not provide change tracking, relationship management, LINQ translation, schema migrations, repositories, or a unit of work.

Installation

Install FclEx.Dapper and the ADO.NET provider used by the application:

dotnet add package FclEx.Dapper
dotnet add package Microsoft.Data.Sqlite

The package targets net472, netstandard2.0, net8.0, net9.0, and net10.0. It references Dapper and FclEx.Core, but it does not add a database provider dependency.

Quick Start

Map an entity with DataAnnotations:

[Table("widgets")]
public sealed class Widget
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    public string Name { get; set; } = "";
}

Use a normal provider connection. The schema must already exist; the CREATE TABLE below is included only to make the example runnable:

using var connection = new SqliteConnection("Data Source=:memory:");
await connection.OpenAsync();
await connection.ExecuteAsync(
    "CREATE TABLE widgets (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL)");

var id = await connection.InsertAsync(new Widget { Name = "first" });
var widget = await connection.GetAsync<Widget>(id);

await connection.BulkInsertAsync(
[
    new Widget { Name = "second" },
    new Widget { Name = "third" },
]);

await connection.DeleteAsync<Widget>(id);

InsertAsync<TEntity> returns the generated key as long. Use InsertAsync<TEntity, TKey> when the generated key has another type.

Providers and SQL Adapters

FclEx.Dapper recognizes these provider connection types when the corresponding provider package is installed:

For generated Oracle insert commands, string values without a store type name use OracleDbType.NVarchar2. Explicit store types such as VARCHAR2, NVARCHAR2, CLOB, and NCLOB retain their configured parameter types. This parameter default does not create or change database columns and does not affect ordinary Dapper calls.

For generated Npgsql commands, DateTimeOffset parameters are converted to UTC when no store type is supplied or the explicit type is TimestampTz, timestamptz, or timestamp with time zone. This preserves the instant; PostgreSQL does not retain the original offset. Timestamp / timestamp without time zone and other explicit types leave values unchanged and may reject incompatible values. The adapter uses parameter mapping, does not inspect database columns, and does not convert DateTime values. This behavior does not affect ordinary Dapper calls.

Oracle uses double-quoted, case-sensitive identifiers and : parameter placeholders. Map identifiers with their actual database casing (typically uppercase for objects created without quotes). BulkInsertAsync uses multi-row VALUES, requiring Oracle Database 23ai or later. The adapter limits each command to floor(65535 / parameterCountPerRow) rows; the shared insert pipeline also caps each batch at 500 rows. Oracle restricts identity/sequence defaults in multi-row VALUES; supply explicit keys where the column permits them, or insert such rows individually. Single-row inserts return numeric, string, and date/time generated keys using RETURNING ... INTO output parameters. Default-only inserts are supported when a single generated key is requested, using that column with DEFAULT; otherwise an explicit insertable column is required. Explicit identity values require a column defined as GENERATED BY DEFAULT; GENERATED ALWAYS columns reject explicit values.

Adapter resolution examines the runtime connection type and its base types. Register an adapter for a custom or wrapped connection type when it cannot be recognized automatically:

DapperHelper.RegisterSqlAdapter<CustomConnection>(new CustomSqlAdapter()); // add or replace

var added = DapperHelper.TryRegisterSqlAdapter<CustomConnection>(new CustomSqlAdapter());

TryRegisterSqlAdapter returns false when the exact connection type is already registered. A registered adapter must keep its SQL-affecting behavior stable because generated SQL is cached by adapter instance. Schema support follows ISqlAdapter.SupportsSchemas; unsupported adapters ignore mapped and per-call schemas.

Table, schema, and column names supplied through mappings or method arguments must come from trusted application configuration, not end-user input. Adapters quote and escape identifiers, but identifier names cannot be parameterized like data values.

Entity Mapping and Key Limits

DataAnnotationsEntityMappingSource is used by default. It supports:

• [Table], including Schema
• [Column], including column aliases and provider store type names
• [Key]
• [NotMapped]
• [DatabaseGenerated] with None, Identity, or Computed

By convention, public readable and writable scalar instance properties are persistent. Static properties, indexers, read-only properties, navigation properties, and explicitly unmapped properties are excluded. A non-scalar property must declare an explicit mapping attribute to be included.

GetAsync<T> and DeleteAsync<T> require exactly one mapped key. Generated-key return from InsertAsync requires exactly one generated key. Composite-key lookup and deletion are not supported.

Implement IEntityMappingSource when mappings should be independent of DataAnnotations. A source must return the same immutable EntityMapping instance whenever the same entity type is requested because mapping identity participates in SQL cache keys:

var mapping = new EntityMapping(
    typeof(Widget),
    "widgets",
    [
        new(typeof(Widget).GetProperty(nameof(Widget.Id))!, "Id", true,
            DatabaseValueGeneration.OnInsert),
        new(typeof(Widget).GetProperty(nameof(Widget.Name))!, "Name"),
    ]);

var options = new CommandOptions { EntityMappingSource = new ApplicationMappingSource(mapping) };
var id = await connection.InsertAsync(new Widget { Name = "mapped" }, commandOptions: options);

ApplicationMappingSource in this example is an application-owned IEntityMappingSource implementation.

Commands, Connections, and Transactions

CommandOptions carries the command timeout, local transaction, adapter override, and mapping source. The same options shape is accepted by connection and transaction CRUD methods. Cancellation is supplied separately through the final optional cancellationToken parameter, which covers connection opening and command execution.

await connection.BulkInsertAsync(
    entities,
    commandOptions: options,
    cancellationToken: cancellationToken);

Migration note: CommandOptions.CancellationToken has been removed. Pass the token directly to each asynchronous CRUD method instead. This is a breaking API change; consumers must rebuild, and calls that initialized the removed property must be updated.

CRUD helpers record the connection's initial state. A connection opened by the helper is closed before the operation returns; a connection supplied already open remains open. Transaction extension methods bind the receiver transaction to the generated command.

ExecuteInTransactionAsync starts a local transaction with ReadCommitted by default, commits after a successful callback, and attempts rollback after callback or commit failure. If both the operation and rollback fail, an AggregateException contains both exceptions.

Bulk Inserts and SQL Caching

BulkInsertAsync emits bounded multi-row INSERT commands; it does not silently execute one command per entity. A batch contains at most 500 rows and may be smaller because of provider row or parameter limits. Multiple rows with no insertable properties are rejected when the adapter cannot express an efficient bulk form.

NpgsqlAdapter converts scalar ushort, uint, and ulong parameter values to int, long, and decimal, respectively. These conversions preserve their full ranges and allow PostgreSQL integer, bigint, and numeric parameters to be inferred. byte values remain unchanged. The conversion applies to generated insert commands, including bulk inserts; it does not change ordinary Dapper calls or configure table column types. Explicit PostgreSQL unsigned types (oid, xid, xid8, cid, regtype, and regconfig) retain their original values. Other explicit store types still determine the parameter type and must accept the supplied value.

Canonical CRUD command text is cached by adapter instance, immutable mapping identity, operation shape, and batch row count. Per-call schema and adapter overrides do not enter the process-wide cache. This avoids repeated SQL string construction without permanently retaining open-ended override values.

Explicit Generated Keys

Use InsertWithExplicitGeneratedKeysAsync or BulkInsertAsync(..., includeAutoKey: true) only when importing values for keys normally generated by the database.

These operations do not advance or reset provider identity, sequence, or auto-increment state. The caller must maintain that state so later database-generated keys do not conflict with explicitly inserted values.

Dapper Global State and Type Handlers

On first use, DapperHelper calls InitializeTypeHandlers() to register Dapper.GuidTypeHandler if no GUID handler is already registered. This removes the built-in Guid and Guid? type mappings so Dapper uses the handler for parameters. These changes affect all Dapper calls in the process. The method can also be called explicitly before using ordinary Dapper APIs, or after resetting Dapper type handlers. Core CRUD operations do not scan assemblies or change other Dapper settings. Generated queries alias database columns back to CLR property names, so they do not require a global Dapper type map.

Dapper.GuidTypeHandler and Dapper.AssumeUtcDateTimeTypeHandler are optional helpers. Registering either through SqlMapper.AddTypeHandler changes Dapper process-wide state and remains the application's responsibility.

See DESIGN.md for the principles governing future changes.