Interface IAdbInsertStatementService

An optional immutable provider service for creating a database insert statement using SQL-92 syntax, and a matching delegate for populating its database parameters from a dataflow row. It supports both single row per statement for per row error feedback, and multiple rows per batch (or statement) for improved performance. The latter uses the following syntax, with named or positional parameters:

INSERT INTO TableName (column-a, [column-b, ...])
VALUES (@r0_c0, [@r0_c1, ...]),
       (@r1_c0, [@r1_c1, ...]);

The implemented interface is used by AdbInsertTarget<TInputError>. Of the four ways to set the batch and transaction sizes, linking ErrorOutput to a downstream worker automatically disables creating transaction and has the highest precedence, then SetRowLimits(Int32, Int64), then AConfig configurations with the InsertStatementValuesPerBatch and InsertStatementValuesPerTransaction string keys, and finally the values in instances of this insert service has the lowest precedence.

You can create a modified service by inheriting from this class with a custom constructor or overriding Create<TRow>(ALog, AdbCommand, String, IList<AdbColumnSchema>, FromTypeColumnMappings, Int32).

As an alternative to using this service, note that some providers also support Bulk Insert.

Namespace: actionETL.Adb
Assembly: actionETL.dll
Syntax
public interface IAdbInsertStatementService

Properties

DefaultValuesPerBatch

Gets the default number of values (or parameters) per batch (or multi-row statement) (always 1 or greater, e.g. 256).

This value determines the default number of rows per batch, which is calculated by dividing by the number of values (or parameters) that is processed per row, and using no less than one row per batch.

Note that with multiple rows per batch, any insert error will reject all the rows in the batch.

Declaration
int DefaultValuesPerBatch { get; }
Property Value
Type Description
Int32

DefaultValuesPerTransaction

Gets the default number of values (or parameters) to use per explicit transaction. This is used to calculate the default number of rows per explicit transaction.

When set to 1 or greater (e.g. 50000), the number of rows is calculated by dividing by the number of values (or parameters) that is processed per row, and using no less than one row per explicit transaction. This means that when set to a very large value (e.g. long.MaxValue), all rows should be inserted wrapped in a single explicit transaction.

When set to less than 0, an explicit transaction will not be created.

Note that the value returned will never be 0.

Note that if the connection already has an active transaction, the insert worker should participate in it, irrespective of what this setting is set to.

Note that with multiple rows in the transaction, any insert error will reject all the rows in the transaction.

Declaration
long DefaultValuesPerTransaction { get; }
Property Value
Type Description
Int64

MaxRowsPerBatch

Gets the maximum number of rows allowed per batch or multi-row insert statement, always 1 or greater, e.g. 999. It limits the number of rows per insert batch used by AdbInsertTarget<TInputError>.

Declaration
int MaxRowsPerBatch { get; }
Property Value
Type Description
Int32
See Also

MaxValuesPerBatch

Gets the maximum number of values (or parameters) allowed per batch or multi-row insert statement, always 1 or greater, e.g. 999. This is used to calculate the maximum number of rows per batch by dividing by the number of values (or parameters) that is processed per row, and using no less than one row per batch.

Declaration
int MaxValuesPerBatch { get; }
Property Value
Type Description
Int32

Methods

Create<TRow>(ALog, AdbCommand, String, IList<AdbColumnSchema>, FromTypeColumnMappings, Int32)

Populates a database command with an insert statement and matching query parameters, and also creates matching high performance delegate for populating the parameters from a dataflow row. Using the delegate avoids having to set each parameter manually, and also handles null values correctly for both reference types and Nullable{T} types, assigning DBNull where needed. It supports both single row per statement and transaction for row-by-row error reporting, and multiple rows for improved performance.

Declaration
OutcomeStatusResult<Action<int, TRow>> Create<TRow>(ALog logger, AdbCommand adbCommand, string compositeTableName, IList<AdbColumnSchema> toColumnSchemas, FromTypeColumnMappings fromTypeColumnMappings, int rowsPerBatch)
    where TRow : class
Parameters
Type Name Description
ALog logger

The logger to use.
AdbCommand adbCommand

A database command. Its CommandText property will be set to an insert statement, and its Parameters will be populated with matching parameters.
String compositeTableName

The target composite table name to insert into.
IList<AdbColumnSchema> toColumnSchemas

To column schemas for the target table. Created with a GetTableColumnsAsync(IAdbTableIdentifier) overload.
FromTypeColumnMappings fromTypeColumnMappings

Column mapper result from the dataflow row to the target table. Typically created with FromTypeColumnMapper<TFrom>.
Int32 rowsPerBatch

Number of rows per batch. Use user (SetRowLimits(Int32, Int64)), configuration (InsertStatementValuesPerBatch), or default values (DefaultValuesPerBatch) to calculate the desired rows per batch, and call GetRowsPerBatch(Int32, Int32) to get the actual rows per batch to pass to this parameter.
Returns
Type Description
OutcomeStatusResult<Action<Int32, TRow>>

On success, Status will be Succeeded and Result will contain an Action{int, TRow} delegate that sets the value of command parameters from one dataflow row. The delegate must be invoked once for each dataflow row in the insert statement, and has a signature corresponding to: void SetParametersFromRow(int rowIndex, T row), where rowIndex is the zero-based index of the row in the insert statement, and row is the dataflow row. The most common scenario is to store the action in a class field or local variable, and run it for all rows from a dataflow input port.

On failure, Status will be Error and Result will be undefined.

Type Parameters
Name Description
TRow

The type of the row. Must be a class.
Exceptions
Type Condition
ArgumentException
  • rowsPerBatch - Values per batch exceeded MaxValuesPerBatch.
  • The row type must be a class, not a value type or interface.
  • compositeTableName is null or blank.
ArgumentNullException
  • adbCommand
  • compositeTableName
  • logger
  • toColumnSchemas
Exception

See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException.
InvalidOperationException
  • May throw if the connection is associated with a transaction.
  • No column name matches found.
NotImplementedException

Not implemented for provider.

GetRowsPerBatch(Int32, Int32)

Calculates the actual number of rows to use per insert batch. It takes MaxRowsPerBatch and MaxValuesPerBatch into account.

Declaration
int GetRowsPerBatch(int columnsPerRow, int desiredRowsPerBatch)
Parameters
Type Name Description
Int32 columnsPerRow

The number of columns in the insert batch. Must be at least 1.
Int32 desiredRowsPerBatch

The number of rows the caller prefer to use in the insert batch. If set to less than 1, DefaultValuesPerBatch will be used to calculate actual number of rows.
Returns
Type Description
Int32

The actual number of rows per batch that the caller should use when creating the insert batch.
Exceptions
Type Condition
ArgumentException

columnsPerRow - Must be at least one.

See Also

AdbInsertTarget<TInputError>