Class AdbSql92InsertStatementService

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.

Inheritance
Object
AdbSql92InsertStatementService
AdbSqlClientInsertStatementService
Implements
Namespace: actionETL.Adb
Assembly: actionETL.dll
Syntax
public class AdbSql92InsertStatementService : IAdbInsertStatementService

Constructors

AdbSql92InsertStatementService(Int32, Int32, Int32, Int64)

Initializes a new instance of the AdbSql92InsertStatementService class, which creates a database insert statement, and a matching delegate for populating the database parameters from a dataflow row.

Inserting multiple rows per batch or per transaction typically increases performance, although using too many parameters can decrease performance, and increases database resource usage.

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

To use single row inserts that are wrapped in a large transaction for performance, use the parameters (1, 1, 1, 0).

To use single row inserts without combining statements in any transaction (e.g. to allow rejecting individual rows), use the parameters (1, 1, 1, -1).

Declaration
public AdbSql92InsertStatementService(int defaultValuesPerBatch, int maxValuesPerBatch, int maxRowsPerBatch, long defaultValuesPerTransaction)
Parameters
Type Name Description
Int32 defaultValuesPerBatch

The default number of values (or parameters) per insert batch (or multi-row statement), which determines the default number of rows per batch.

When set to 0 or less, the default number of parameters per batch is used (currently 256). When set to 1 or greater (e.g. 500), 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 1 row per batch.

Int32 maxValuesPerBatch

The maximum number of values (or parameters) allowed per batch or multi-row insert statement, e.g. 999. Any attempt to set a larger batch size will be automatically limited to this number of values.

The maximum number of rows per batch 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.

Int32 maxRowsPerBatch

The maximum number of rows allowed per batch or multi-row insert statement, e.g. 999. Any attempt to set a larger batch size will be automatically limited to this number of rows.
Int64 defaultValuesPerTransaction

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 1 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 0, the default number of values (currently 16384) is used.

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

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.

Wrapping just one or a few insert statements in a transaction is possible, but typically decreases performance.

AdbSql92InsertStatementService(Int32, Int32, Int64)

Initializes a new instance of the AdbSql92InsertStatementService class, which creates a database insert statement, and a matching delegate for populating the database parameters from a dataflow row.

MaxRowsPerBatch will be set to maxValuesPerBatch (and therefore won't have any effect).

Inserting multiple rows per batch or per transaction typically increases performance, although using too many parameters can decrease performance, and increases database resource usage.

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

To use single row inserts that are wrapped in a large transaction for performance, use the parameters (1, 1, 0).

To use single row inserts without combining statements in any transaction (e.g. to allow rejecting individual rows), use the parameters (1, 1, -1).

Declaration
public AdbSql92InsertStatementService(int defaultValuesPerBatch, int maxValuesPerBatch, long defaultValuesPerTransaction)
Parameters
Type Name Description
Int32 defaultValuesPerBatch

The default number of values (or parameters) per insert batch (or multi-row statement), which determines the default number of rows per batch.

When set to 0 or less, the default number of parameters per batch is used (currently 256). When set to 1 or greater (e.g. 500), 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 1 row per batch.

Int32 maxValuesPerBatch

The maximum number of values (or parameters) allowed per batch or multi-row insert statement, e.g. 999. Any attempt to set a larger batch size will be automatically limited to this number of values.

The maximum number of rows per batch 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.

Int64 defaultValuesPerTransaction

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 1 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 0, the default number of values (currently 16384) is used.

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

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.

Wrapping just one or a few insert statements in a transaction is possible, but typically decreases performance.

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
public 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
public 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
public 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
public 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
public virtual 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
public virtual 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.

ToString()

Returns a string that represents the current object.

Declaration
public override string ToString()
Returns
Type Description
String
Overrides

Implements

See Also

AdbInsertTarget<TInputError>