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:
ArgumentNullException | ConnectionBuilder |
ArgumentException | CompositeTableName |
InvalidOperationException |
- No column name matches found. - Cannot both use transactions and link the ErrorOutput port. |
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 |
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 |
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
Set to
Set to less than 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
Set to
Set to
Set to less than |
Exceptions
Type | Condition |
---|---|
InvalidOperationException | Cannot set the value after the worker has started running. |