Class AdbInsertTarget<TInputError>

A dataflow worker with one Input port, that inserts all incoming rows into a SQL table or view. It also has an ErrorOutput port that receives any row batches that throw an exception when creating and executing their queries.

Note that this worker requires an AdbProvider that supports both IAdbTableInformationService and IAdbInsertStatementService, see Database Support for details. You can also use IsSupported(AdbProvider) at runtime to determine whether a given provider is supported with this worker.

Note: Use the factory methods in AdbInsertTargetFactory to create instances of this class.

Rows will be inserted in batches and transactions according to how the provider IAdbInsertStatementService has been configured. The out-of-box providers typically use multi-row batches and also wrap the batches into periodic transactions. To change the defaults, either use SetRowLimits(Int32, Int64) on this worker, or use AConfig configurations with the InsertStatementValuesPerBatch and InsertStatementValuesPerTransaction string keys, or create a new provider instance with e.g. With(String, IAdbInsertStatementService).

Using any transactions will automatically set ErrorOutput.MaxRowsBeforeError to 0 (i.e. fail on the first batch error).

Note however that creating transactions is automatically disabled when the ErrorOutput port has been linked to a downstream worker, since otherwise rows could end up processed several different ways (rows successfully inserted, incorrect and correct rows sent to downstream worker, and rows rolled back) which would be difficult to program against.

This worker does participate in any pre-existing transaction currently active on the connection. Using both a transaction and linking the ErrorOutput port to a downstream worker is however incompatible, and would fail the worker.

Note that with multiple rows per batch or transaction, any insert error will reject all the rows in the batch or transaction. Only the failed batch insert will be sent to ErrorOutput, even if transactions are used and some or all previous batches are rolled back.

The following exceptions can occur when the worker runs:

ArgumentNullExceptionConnectionBuilder
ArgumentExceptionCompositeTableName
InvalidOperationException

- No column name matches found.

- Cannot both use transactions and link the ErrorOutput port.

Inheritance
Object
WorkerParent
WorkerBase
WorkerBase<AdbInsertTarget<TInputError>>
AdbInsertTarget<TInputError>
Implements
Inherited Members
Namespace: actionETL.Adb
Assembly: actionETL.dll
Syntax
public class AdbInsertTarget<TInputError> : WorkerBase<AdbInsertTarget<TInputError>>, IDisposeOnFinished where TInputError : class
Type Parameters
Name Description
TInputError

The type of each Input and ErrorOutput row.

Properties

CompositeTableName

Gets or sets the composite name of the table to insert into. The string must have a format appropriate for the data source. E.g. for the SqlClient provider: Product, dbo.Product, [Sales].[dbo].[Internet Sales], or "Sales"."dbo"."Internet Sales". Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public string CompositeTableName { get; set; }
Property Value
Type Description
String

The composite name of the table.
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

ConnectionBuilder

Gets or sets the database connection builder. Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public IAdbConnectionBuilder ConnectionBuilder { get; set; }
Property Value
Type Description
IAdbConnectionBuilder
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

ErrorOutput

Gets the error output port for sending error rows to logging and an optional downstream worker.

Using any transactions will automatically set ErrorOutput.MaxRowsBeforeError to 0 (i.e. fail on the first batch error).

Note however that creating transactions is automatically disabled when the ErrorOutput port has been linked to a downstream worker.

Declaration
public ErrorOutputPort<TInputError> ErrorOutput { get; }
Property Value
Type Description
ErrorOutputPort<TInputError>

Input

Gets the input port for receiving rows from an upstream worker.

Declaration
public InputPort<TInputError> Input { get; }
Property Value
Type Description
InputPort<TInputError>

Methods

RunAsync()

This method can be overridden to add custom functionality to the derived worker that runs before and after the row processing. In this case, the base class base.RunAsync() must be called for the worker to function correctly.

Typically, this worker is used without overriding this method.

Declaration
protected override async Task<OutcomeStatus> RunAsync()
Returns
Type Description
Task<OutcomeStatus>

A Task describing the success or failure of the worker. An asynchronous async implementation would e.g. return OutcomeStatus.Succeeded on success, while a synchronous implementation would return OutcomeStatus.SucceededTask.
Overrides

SetRowLimits(Int32, Int64)

Sets the number of rows to use per batch (or multi-row statement) and transaction.

Note that performance and resource usage scales with (and batch size hard limits apply to) the number of values (or parameters) per batch and transaction, and also locks the target table for longer. Using too large batches and transactions can decrease performance or fail the transaction if the database runs out of resources. Use more rows per batch and transaction when rows are narrow, and fewer rows when rows are wide.

Calling this method will override anything set with the AConfig configurations InsertStatementValuesPerBatch and InsertStatementValuesPerTransaction.

Note that with multiple rows per batch or transaction, any insert error will reject all the rows in the batch or transaction. Only the failed batch insert will be sent to ErrorOutput, even if transactions are used and some or all previous batches are rolled back.

Using any transactions will automatically set ErrorOutput.MaxRowsBeforeError to 0 (i.e. fail on the first batch error).

Note however that creating transactions is automatically disabled when the ErrorOutput port has been linked to a downstream worker.

To get a single row per batch and no explicit transaction, use SetRowLimits(1,-1). This allows rejecting individual rows to the error output on any row insert error, but is also particularly slow compared to using large batches and transactions.

Cannot be set after the worker has started running.

Note: This method is thread-safe.

Declaration
public void SetRowLimits(int rowsPerBatch, long rowsPerTransaction)
Parameters
Type Name Description
Int32 rowsPerBatch

The number rows to use per batch (or multi-row statement).

Set to greater than 1 (e.g. 25) to use multi-row batches. Use more rows when rows are narrow and vice versa.

Set to 1 to get a single row per batch.

Set to less than 1 (the default) to get the number of rows derived from the InsertStatementValuesPerBatch configuration value if available, or otherwise the provider dependent default number of values (or parameters) per batch, e.g. the number of rows that corresponds to 256 values (or parameters) per batch.

The actual value used is also automatically limited by RowsPerBuffer rows, and potentially by individual database drivers.

Int64 rowsPerTransaction

The number of rows to use per explicit transaction.

Set to long.MaxValue to insert all rows in a single explicit transaction. Ensure the database can handle the resource usage of all rows in a single transaction.

Set to 1 or greater, e.g. 2000 to use one explicit transaction per 2000 rows. Use more rows when rows are narrow and vice versa. Note that using a transaction adds overhead, and typically only increases performance when encompassing three or more batches.

Set to 0 (the default when ErrorOutput is not linked) to get the number of rows derived from the InsertStatementValuesPerTransaction configuration value if available, or otherwise from the provider dependent default number of values (or parameters) per explicit transaction, e.g. 40000.

Set to less than 0 (the default when ErrorOutput is linked) to not use any explicit transaction. Note that many databases then uses an implicit transaction for each batch (or statement).

Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

Implements

See Also