FclEx.Dapper 2.4.2

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.

Table Existence Checks

Use TableExistsAsync to query a table by name or entity mapping:

bool exists = await connection.TableExistsAsync("Orders", schema: "Sales", cancellationToken: cancellationToken);
bool mappedTableExists = await connection.TableExistsAsync<Order>(commandOptions: options, cancellationToken: cancellationToken);

Names are unquoted components passed as parameters, rather than interpolated SQL. The entity overload honors its mapping source and mapped schema; an explicit schema overrides the mapping. Command options support a local transaction, timeout, and adapter override. The operation restores a connection it opened to Closed and preserves an already-open connection.

SQL Server and PostgreSQL use native unqualified-name resolution when no schema is supplied. Oracle uses CURRENT_SCHEMA and exact catalog casing. MySqlConnector treats schema as a database name; MySql.Data ignores it and checks the selected database. SQLite searches main and temp with case-insensitive name comparison and ignores schema arguments. SQLite attached databases and SQL Server temporary tables are outside this lookup. Views and synonyms are excluded. A false result reflects catalog visibility for the current login; database access failures propagate. The check does not reserve or lock the table against concurrent DDL.

ISqlAdapter.BuildTableExistsCommandText supplies the metadata query. Direct interface implementations must implement this new member. SqlAdapterBase preserves existing derived implementations with a default that throws NotSupportedException until metadata querying is supplied.

Identity Sequence Synchronization

Use this maintenance operation after importing explicit keys or deleting high keys:

bool synchronized = await connection.ReseedIdentityAsync<Order>(
    schema: "Sales", commandOptions: options, cancellationToken: cancellationToken);
await connection.ReseedIdentityAsync("Orders", "OrderId", schema: "Sales");

The entity overload requires exactly one mapped database-generated integer key and uses its physical column name. The result is false when the table or native identity generator is absent. Invalid mappings and database access errors throw. A connection opened by this operation is closed before return.

Pause concurrent writes and other users of the sequence during synchronization. This API does not coordinate writers, and resetting a counter may reuse deleted values. PostgreSQL sequence changes survive rollback; MySQL and Oracle DDL implicitly commit. Namespace rules match CRUD, including MySql.Data and SQLite ignoring schema arguments. Custom ISqlAdapter implementations must implement ReseedIdentityAsync; SqlAdapterBase throws NotSupportedException by default. This is a source-breaking interface addition.

Table Truncation

TruncateAsync accepts an unquoted table name or an entity mapping and removes every row in the physical table:

await connection.TruncateAsync<Order>(schema: "Sales", commandOptions: options, cancellationToken: cancellationToken);
await connection.TruncateAsync("Orders", restartIdentity: true, cascade: false,
    schema: "Sales", commandOptions: options, cancellationToken: cancellationToken);

The entity overload honors its mapping source and mapped schema; an explicit schema overrides the mapping. No key is required. Identifier components are escaped by the resolved or overridden SQL adapter. The helper preserves the connection's initial state and accepts local transactions, timeouts, and cancellation through the existing command options. It does not validate EF-style inheritance or table sharing and does not consult application filters, soft-delete rules, or entity state.

SQLite uses DELETE followed by sequence maintenance in one transaction. It preserves caller-owned transactions and otherwise creates and commits a local transaction. Main and temp tables are supported; attached databases are excluded. DELETE triggers and foreign-key actions execute. Ordinary ROWID allocation follows SQLite rules regardless of restartIdentity. Native TRUNCATE behavior is database-dependent: foreign keys can prevent execution, CASCADE can affect additional tables, and MySQL/Oracle implicitly commit.

Adapters provide BuildTruncateCommandText and ExecuteTruncateAsync. Direct ISqlAdapter implementations must implement these new members. SqlAdapterBase rejects unsupported truncation by default and provides ordinary command execution; SQLite overrides execution for atomic sequence maintenance.

Dapper Global State and Type Handlers

On first use, DapperHelper calls Initialize() to register Dapper.GuidTypeHandler and Dapper.DateTimeOffsetTypeHandler independently when no application handler is registered for that type. Installing a default handler removes the corresponding parameter type maps, including nullable and application mappings, so Dapper uses that handler for parameters. Applications registering their own handlers remain responsible for removing conflicting parameter maps. These changes affect all Dapper calls in the process. Call Initialize() explicitly before ordinary Dapper operations or after resetting Dapper's 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.

DateTimeOffsetTypeHandler reads native DateTimeOffset values, invariant text (including SQLite TEXT), and DateTime values. Explicit offsets and available tick precision are preserved. Unspecified date-time values and text without an offset are interpreted as UTC; local date-time values retain their instant. Nullable scalar results preserve database nulls. Parameters retain their supplied offset and use DbType.DateTimeOffset; the provider determines whether that representation is supported. This handler does not change the destination column type or recover offsets discarded by a database.

Dapper.AssumeUtcDateTimeTypeHandler remains opt-in. Registering it through SqlMapper.AddTypeHandler changes Dapper process-wide state and remains the application's responsibility.

See DESIGN.md for the principles governing future changes.