Net4x.DapperLibrary.Base 1.9.9.8

DapperLibrary

DapperLibrary is a lightweight data-access library that builds on ADO.NET and Dapper to provide a consistent, engine-agnostic API for querying and manipulating relational databases. The library centers on the DapperContext class and the IDapperContext interface which encapsulate connection creation, command execution, parameter handling and schema helpers.

Overview

• Core types: DapperContext (concrete) and IDapperContext (interface). Create an instance of DapperContext (or use a provider-specific derived context) and use it via the IDapperContext surface to execute commands and queries.
• The library supports multiple database engines (SQL Server, Oracle, MySQL, PostgreSQL, SQLite, and more) through provider factories and engine-specific context helpers.
• DapperContext manages connection lifetime automatically and exposes a rich set of synchronous and asynchronous operations.

Multi-targeting and framework support

This repository is designed to support a broad range of .NET frameworks to make the library usable in legacy and modern applications. The source contains conditional compilation directives and multi-targeted build configurations so different assemblies are produced for different target frameworks. Supported frameworks include (but are not limited to):

• .NET Framework 3.5
• .NET Framework 4.0
• .NET Framework 4.5 / 4.6.1 / 4.7.2 / 4.8
• .NET Standard 2.0 / 2.1
• .NET 6, .NET 8, .NET 10 (modern .NET)

Notes about multi-targeting:

• Conditional compilation (#if NETSTANDARD, #if NET8_0_OR_GREATER, etc.) is used to vary implementations that depend on runtime APIs (for example JSON serialization, configuration APIs, or host builder integration).
• NuGet packages produced from this source include framework-specific assemblies. Consumers should reference the package from the target framework matching their application to get the appropriate implementation.
• Where APIs differ between frameworks (e.g., ConfigurationManager, IHostApplicationBuilder availability, or System.Text.Json vs Newtonsoft.Json), the library exposes equivalent surface behavior while using the most appropriate underlying runtime facility.

Key concepts

• IDapperContext / DapperContext

  • Use a DapperContext instance to obtain database access. The instance is responsible for creating and tracking DbConnection instances and for closing them when appropriate.
  • IDapperContext exposes an Operations/Schema surface for segregated responsibilities (query/set operations vs schema inspection).
  • Provider-specific convenience helpers exist (e.g. SqlDapperContext, MySqlDapperContext) to simplify engine-specific configuration and lifetime semantics.

• Multiple connection types and mixing engines

  • You can have different DapperContext objects for different engines and mix them in the same application.
  • Typical usage patterns include SqlDapperContext.Use(), SqlDapperContext.UseOnce(), MySqlDapperContext.Use(), MySqlDapperContext.UseOnce() to simplify scoped or one-off usage.

• In-memory & mocked database support

  • The library includes helpers to use an in-memory engine (SQLite) for tests.
  • A mocked SQL Server implementation (lite) and Dapper-based mocks are available for unit tests.
  • The library integrates with containerized or mocked DB runtimes via test containers (TestContainers.* NuGet packages) when needed.

Managing connection strings

DapperContext provides several APIs and properties to control which connection string is used for operations and how a connection string is resolved:

• ConnectionStringToRead (string)

  • The active connection name used by the context when creating new connections. Many convenience methods use this value as the default connection name.
  • Set this property to point the context at a different connection (by name) at runtime. Example: context.ConnectionStringToRead = "MyOtherConnection".

• ConnectionStringSettings and AlternativeConnectionString

  • ConnectionStringSettings can hold the currently active connection settings. When a configured ConnectionStringSettings is present the context will prefer its connection string.
  • AlternativeConnectionString serves as a fallback value if no configuration provider or settings supply a concrete connection string.

• ConnectionStrings (IConnectionStrings)

  • The context exposes a ConnectionStrings collection that is initialized from the owning DapperContextFactory / configuration manager. Use context.ConnectionStrings[...] or the ConnectionStrings.Current helpers to inspect available named connections.

• GetConnectionString(string connectionStringName)

  • Resolve the actual connection string text by name. This method merges configured values, factory defaults and AlternativeConnectionString, and raises a ConnectionStringEventArgs that can be patched by DapperContextFactory internals.
  • Use this method when you need the raw connection string value instead of the connection-name.

• Custom connections via indexer

  • context[connectionStringName] returns an ICustomConnection bound to that name (useful for per-connection shortcuts and schema helpers).

Notes and best practices

• Set ConnectionStringToRead once during initialization when a context is used for a single logical database. Change it dynamically only when you understand the effect on pooled/allocated connections.
• Prefer the explicit WithConnection(...) overload when you must perform an operation against a different database for a single call; that avoids race conditions around concurrent operations and reader-associated connections.
• When combining multiple engines or databases, keep a separate DapperContext per engine/connection string to keep behavior predictable and to avoid mixing provider factories.

Schema helpers and caching

DapperLibrary exposes a typed, convenient schema inspection surface through IDapperContext.Schema (and the concrete DapperContext.Schema property). The ISchema surface aggregates small helper types that make reading table metadata easy and concise.

• Schema helper properties available on ISchema (examples):
  • ColumnNames � indexer ColumnNames[tableName] returns an array of column names for the table.
  • IdentityColumnIndex � indexer IdentityColumnIndex[tableName] returns the ordinal of the identity/autoincrement column.
  • IdentityColumn � indexer IdentityColumn[tableName] returns the identity column name (if any).
  • PrimaryKeyColumnNames � indexer PrimaryKeyColumnNames[tableName] returns the primary key column names.
  • SchemaTableTypes � indexer SchemaTableTypes[tableName] returns an array of rich SchemaTable descriptors.
  • UniqueKeyColumnNames � indexer UniqueKeyColumnNames[tableName] returns unique columns.
  • AllowDbNull, ColumnOrdinal, ColumnSize, ColumnType, NumericPrecision, NumericScale, ProviderType � each type exposes indexers with either a (tableName, columnName) or (tableName, index) overload for convenience.

Usage examples:

• Read column names: var names = context.Schema.ColumnNames["Orders"];
• Inspect column type: var dt = context.Schema.ColumnType["Orders", "OrderDate"];
• Read identity ordinal: var idx = context.Schema.IdentityColumnIndex["Orders"];

With-connection overloads

• The schema helper types have constructors that accept a connectionStringName. When a helper is initialized with a connection string the indexers call *WithConnection variants internally (for example GetSchemaTableWithConnection) so you can query schema on a different named connection via context.SchemaWithConnection(name)-style factories (the library exposes ways to build schema helpers bound to a named connection).

Caching behavior

• Reading schema metadata is relatively expensive; the library caches schema reads internally to avoid repeating database queries.
• Under the hood the schema helpers call GetSchemaTableWithConnection(...), which uses CacheUtility.Instance.GetValueFromCache with a cache key typically built from the resolved connection string and the table name. Common cache keys follow the pattern: ${connectionString}_${tableName}_SchemaTable, ${connectionString}_${tableName}_ColumnNames, etc.
• Cache entries include the computed SchemaTable[], column names arrays and other derived values. The cache reduces round-trips and speeds up repeated metadata inspection.

Cache invalidation

• When the library performs DDL-like operations (create/alter/drop table), or an operation that changes the database layout, it will attempt to clean related cache entries (see methods such as CleanCacheForTable and the internal paths that call it).
• If you perform schema changes outside of DapperContext (e.g., manually via another process), you may need to evict or refresh the cache programmatically. The internal CacheUtility API is used for caching; extend or call into that helper to control cache lifetime if required.

Best practices

• Use schema helpers for read-only metadata lookup and rely on the cache for performance.
• When doing programmatic schema changes within your application, perform the change through DapperContext operations so the library can invalidate cached metadata.
• For cross-connection schema inspection, prefer the helper constructors that accept a connection string name so cached results are kept per resolved connection string.

Automatic connection lifetime management

• DapperContext opens DbConnection instances as needed and keeps track of them in an internal Behavior container.

• If a DbDataReader is opened on a connection, and you execute another statement on the same DapperContext, a new connection will be opened automatically to avoid interfering with the reader.

• Disposing the DapperContext will close all owned connections. If a DapperContext is not explicitly disposed, its finalizer will still attempt to close open connections when the GC collects the object.

• You can explicitly close the connection associated with a specific reader using DapperContext.CloseConnectionAssociatedWithDataReader(DbDataReader reader).

• The property DapperContext.CloseConnection controls detailed closing behavior; it maps to underlying CommandBehavior and Behavior.KeepOpen semantics. The library supports the three typical behaviours:

  • AsSoonAsPossible � connections closed immediately after operations complete
  • WhenDataReaderCloses � keep connection open until the reader is disposed
  • AtDispose � keep connections open until the DapperContext is disposed

  Inspect the Behavior and CloseConnection usage in DapperContext and related code to see how the container selects, reuses, and disposes connections.

Automatic parameter management

• The library abstracts parameters via indexed placeholders inside SQL text. Placeholders use the form {i} or {i:out} inside a statement.
  • Example: SELECT {0:out}, Field1, {1}+' Test' FROM SqlTestTable WHERE KeyField = {2}
  • During preparation this becomes SELECT @Par0, Field1, @Par1+' Test' FROM SqlTestTable WHERE KeyField = @Par2 (parameter names like @Par0, @Par1, etc.).
• Parameter translation and preparation is centralized in the ParameterModel class and related helpers such as ParameterModel.DefaultPrepareCommandParameters (see ParameterModel.PrepareDynamicParameters and PrepareCommandParameters for details).
  • The model supports output parameters (with :out marker), named parameters, and dynamic parameter objects (Dapper DynamicParameters).
  • The parameter system also handles copying back output values to the original parameter array after execution.

Synchronous and asynchronous methods

DapperLibrary exposes both synchronous and asynchronous versions of most operations so it can be used in synchronous legacy applications as well as modern async/await code paths. Common patterns and guidance:

• For each high-level operation there is typically a synchronous variant and an Async suffix variant. Examples:
  • Synchronous: GetDataTable, GetDataReader, SetData, GetScalar<T>, SaveDataTable, Save<T> etc.
  • Asynchronous: GetDataTableAsync, GetDataReaderAsync, SetDataAsync, GetScalarAsync<T>, SaveDataTableAsync, SaveAsync<T> etc.
• Use the asynchronous variants in I/O-bound, latency-sensitive applications (ASP.NET Core, background services) to avoid blocking thread-pool threads. Use synchronous APIs in legacy code paths or where async propagation is impractical.
• Internals: async implementations rely on DbCommand / DbDataReader async methods where supported by the provider and are guarded by conditional compilation for older frameworks that do not support Task-based ADO.NET async APIs.
• When mixing sync and async calls on the same DapperContext, be mindful of connection reentrancy: opening a long-lived DbDataReader via a sync call may cause subsequent operations to open separate connections; prefer async patterns end-to-end when using async readers.

Offered functionality (high level)

• Basic ADO.NET style operations

  • GetScalar<T> � executes a command and returns a scalar value (ExecuteScalar behavior)
  • GetDataReader � open a reader (ExecuteReader behavior)
  • SetData � execute non-query statements (ExecuteNonQuery behavior)
  • GetDataTable / GetDataSet � build a DataTable/DataSet from reader results
  • SaveDataTable � use a DbDataAdapter to persist a DataTable to the database (adapter commands created from table schema)

• Dapper-based query operations

  • Query<T> � maps rows to entities via Dapper Query<T>
  • QuerySingle<T>, QueryFirstOrDefault<T> � single-row mapping
  • QueryMultiple � use Dapper QueryMultiple to read multiple result sets

• Entity-oriented persistence helpers

  • Save<T> and SaveEnumerable<T> to persist entities (single and enumerable)
  • Delete<T> and DeleteEnumerable<T> to remove entities
  • Hooks for customizing entity mapping (see attributes below)

• Schema helpers

  • Schema helpers expose metadata like column names, column sizes, types, primary keys and identity column info.
  • Use IDapperContext.Schema to inspect table metadata in a provider-agnostic manner.

Entity mapping attributes

• To customize entity-to-table mapping the library supports attributes on entity classes and properties. Typical attributes include:

  • TableNameAttribute � map a CLR type to a specific database table
  • ColumnNameAttribute � map a CLR property to a specific column name
  • PrimaryKeyAttribute(int position) � define primary key properties and their order
  • TimestampAttribute � mark a property as a timestamp/rowversion

  These attributes are used by the library�s DatabaseModel and ParameterModel to generate correct SQL and mapping behavior.

Test support and mocking

• DapperLibrary ships helpers for in-memory testing (SQLite), mocked SQL Server runtimes and integration with containerized test DBs (TestContainers).* packages.
• There are also specialized helpers for creating an in-memory Northwind dataset used by the test suite (NorthwindHelper).

Where to look in the codebase

• Main entry points: DapperContext, IDapperContext, IDapperOperations and ParameterModel.
• Schema helpers: IDapperContext.Schema and implementations under DbContexts\Schema.
• Operations: check Operations implementations and DapperContext.Operations.cs for the facade delegations.
• Low-level plumbing: LowLevelDapperContext for direct ADO.Net interactions and DapperContextQueryUsingParameters for Dapper parameter handling.

NuGet-style description (summary)

DapperLibrary provides an engine-agnostic, Dapper-enhanced data access surface centered on DapperContext/IDapperContext. It simplifies parameter handling, connection lifetime management and schema inspection while exposing common ADO.NET and Dapper operations. Ideal for apps that need a thin abstraction over multiple relational databases with strong testability support (in-memory and mocks).

This README is a placeholder. Refer to the project sources and XML documentation inside the code for detailed API usage and advanced configuration examples.