CoreEx.Database

CoreEx.Database

The CoreEx.Database namespace provides extended ADO.NET capabilities.

Motivation

The motivation is to simplify and unify the approach to ADO.NET (database) access.

Railway-oriented programming

To support railway-oriented programming whenever a method name includes WithResult this indicates that it will return a Result or Result<T> including the resulting success or failure information. In these instances an Exception will only be thrown when considered truly exceptional.

Database

The Database is the base (common) implementation for the IDatabase interface that provides the standardized access to the underlying database.

The following additional IDatabase key capabilities exist.

Capability	Description
DatabaseColumns	Enables the specification of special database columns used for extended built-in capabilities.
Wildcard	Provides configuration to manage wildcard transformation.
DateTimeTransform	Specifies the DateTime transformation when reading from the database.

Provider specific

The following specific database provider implementations further extend the capabilities.

Database	Implementation
Microsoft SQL Server	SqlServerDatabase
Oracle MySQL	MySqlDatabase

Usage

To use the Database a connection creation function parameter is required that is leveraged at runtime (lazy instantiation) to get (create or provide) the underlying DbConnection. The IDatabase implements IDisposable; the Dispose is the primary mechanism to close the connection where automatically opened.

The following demonstrates usage.

    public class HrDb : SqlServerDatabase
    {
        public HrDb(SettingsBase settings) : base(() => new SqlConnection(settings.GetRequiredValue<string>("ConnectionStrings:Database"))) { }
    }

Additionally, review the Beef repo sample.

Commands

The CoreEx IDatabase encapsulates an ADO.NET DbCommand within a DatabaseCommand; via the following methods:

Method	Description
StoredProcedure	Creates a command for a stored procedure; (see CommandType.StoredProcedure)
SqlStatement	Creates a command for a SQL statement; (see CommandType.Text)
SqlFromResource	Creates a command for a SQL statement within the specified embedded resource.

or IDatabase.SqlStatement method passing the appropriate content.

The following DatabaseCommand methods provide additional capabilities. The query-based methods optionally leverage the rich Mapping capabilities.

Method	Description
NonQueryAsync, NonQueryWithResultAsync	Executes a non-query command.
ScalarAsync<T>, ScalarWithResultAsync<T>	Executes the query and returns the first column of the first row in the result set returned by the query.
SelectSingleAsync, SelectSingleWithResultAsync	Selects a single item.
SelectSingleOrDefaultAsync	Selects a single item or default.
SelectFirstAsync, SelectFirstWithResultAsync	Selects first item.
SelectFirstOrDefaultAsync, SelectFirstOrDefaultWithResultAsync	Selects first item or default.
SelectQueryAsync, SelectQueryWithResultAsync	Select items into or creating a resultant collection.
SelectMultiSetAsync, SelectMultiSetWithResulAsync	Executes a multi-dataset query command with one or more multi-set arguments.

The DbEx DatabaseExtensions class demonstrates usage of the SelectQueryAsync (without Mapping) within the SelectSchemaAsync method.

Query

The Extended namespace provides a DatabaseCommand.Query<T> that provides a DatabaseQuery<T> to encapsulate the following.

Method	Description
WithPaging	Adds Skip and Take paging to the query.
SelectSingleAsync, SelectSingleWithResultAsync	Selects a single item.
SelectSingleOrDefaultAsync, SelectSingleOrDefaultWithResultAsync	Selects a single item or default.
SelectFirstAsync, SelectFirstWithResultAsync	Selects first item.
SelectFirstOrDefaultAsync, SelectFirstOrDefaultWithResultAsync	Selects first item or default.
SelectQueryAsync, SelectQueryWithResultAsync	Select items into or creating a resultant collection.
SelectResultAsync, SelectResultWithResultAsync	Select items creating a ICollectionResult which also contains corresponding PagingResult.

Reference data

The Extended namespace provides a DatabaseCommand.ReferenceData<TColl, TItem, TId> that provides a RefDataLoader<TColl, TItem, TId> (via the LoadAsync and LoadWithResultAsync methods) to simplify the loading of a reference data collection.

The ReferenceDataService within the My.Hr smaple demonstrates usage.

await _db.ReferenceData<GenderCollection, Gender, Guid>("Hr", "Gender").LoadAsync("GenderId", cancellationToken: cancellationToken).ConfigureAwait(false)

Parameters

The DatabaseCommand provides a Parameters property that primarily enables the following core parameter capabilities.

Method	Description
AddParameter	Adds a DbParameter; there are a number of overloads enabled.
AddReturnValueParameter	Adds an int return value DbParameter (see DatabaseColumns.ReturnValueName).
AddReselectRecordParam	Adds a bool reselect record DbParameter (see DatabaseColumns.ReselectRecordName).

Additionally, the DatabaseCommand supports a set of extension methods to further enable, and simplify, the specification of parameters that leverage the aforementioned Parameters.

Method	Description
Param	Adds a DbParameter; there are a number of overloads enabled.
ParamWhen	Adds a DbParameter when the specified condition is true; there are a number of overloads enabled.
ParamWith	Adds a DbParameter when invoked with a non-default value; there are a number of overloads enabled.
ParamWithWildcard	Adds a wildcard DbParameter when invoked with a non-default value; there are a number of overloads enabled.
RowVersionParam	Adds a row version DbParameter (see DatabaseColumns.RowVersionName). Note that the underlying implementation is database specific.
ReselectRecordParam	Adds a bool reselect record DbParameter (see DatabaseColumns.ReselectRecordName).
ReselectRecordParamWhen	Adds a bool reselect record DbParameter (see DatabaseColumns.ReselectRecordName) when true.
PagingParams	Adds the PagingArgs DbParameter(s) being DatabaseColumns.PagingSkipName, DatabaseColumns.PagingTakeName and DatabaseColumns.PagingCountName.

Database record

CoreEx encapsulates an ADO.NET DbDataReader within a DatabaseRecord; this primarily provides the GetValue<T> method that provides extended capabilites to retrieve a column value from the underlying DbDataReader.

Mapping

To support the mapping from and/or to a .NET Type and the underlying database, the IDatabaseMapper and corresponding IDatabaseMapper<TSource> interface enable (also see DatabaseQueryMapper for query only (MapFromDb) support).

• MapToDb - maps the .NET Type to the database by adding the properties as database parameters.
• MapFromDb - maps the database columns to the properties of a .NET Type.

The Mapping namespace provides the primary mapping capabilities.

Class	Description
DatabaseMapper	Enables the Create and CreateAuto of a DatabaseMapper<TSource>.
DatabaseMapper<TSource>	Provides the to/from mapping configuration.
PropertyColumnMapper	Provides the property to/from mapping configuration.

The ChangeLogDatabaseMapper is a CoreEx implementation example. Additionally, see the Beef My.Hr sample which further demonstrates usage within the EmployeeBaseData.DbMapper class.

Multi-set arguments

To simplify the support for the retrieval of multiple result sets the IMultiSetArgs is provided. This is useful where a single command will result in multiple result sets reducing the chattiness between application and database, improving performance, reducing execution latency.

The following IMultiSetArgs implementations are provided. The StopOnNull property indicates whether to stop further query result set processing where the current set has resulted in a null (i.e. no records).

Class	Description
MultiSetCollArgs<TColl, TItem>	Provides the multi-set arguments when expecting a collection of items/records. The MinRows and MaxRows properties can also be specified to ensure/validate correctness of returned rows.
MultiSetSingleArgs<T>	Provides the multi-set arguments when expecting a single item/record only. The IsMandatory property indicates whether the value is mandatory.

The DatabaseCommannd.SelectMultiSetAsync method supports one or more IMultiSetArgs when invoked; leveraging the configuration within to create the resulting output. Note also, the IMultiSetArgs count must not be less that the number of result sets returned from the database.

The Beef My.Hr sample demonstrates usage within the EmployeeData class.

await db.SelectMultiSetAsync(
    new MultiSetSingleArgs<Employee>(DbMapper.Default, r => employee = r, isMandatory: false, stopOnNull: true),
    new MultiSetCollArgs<EmergencyContactCollection, EmergencyContact>(EmergencyContactData.DbMapper.Default, r => employee!.EmergencyContacts = r)).ConfigureAwait(false);