Class AdbSqlClientInsertStatementService
An immutable provider service for creating a database insert statement for SqlClient SQL Server, 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, ...]);
This service optimizes inserts by enabling SqlCommand.EnableOptimizedParameterBinding.
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.
Note that MaxRowsPerBatch will be equal to
1000.
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.
Implements
Inherited Members
Namespace: actionETL.Adb
Assembly: actionETL.dll
Syntax
public class AdbSqlClientInsertStatementService : AdbSql92InsertStatementService, IAdbInsertStatementServiceConstructors
AdbSqlClientInsertStatementService(Int32, Int32, Int64)
Initializes a new instance of the AdbSqlClientInsertStatementService 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.
Declaration
public AdbSqlClientInsertStatementService(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 |
| Int32 | maxValuesPerBatch | The maximum number of values (or parameters) allowed per batch or multi-row
insert statement, e.g. 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
When set to
When set to less than 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. |
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.
This service optimizes inserts by enabling SqlCommand.EnableOptimizedParameterBinding.
Declaration
public override OutcomeStatusResult<Action<int, TRow>> Create<TRow>(ALog logger, AdbCommand adbCommand, string compositeTableName, IList<AdbColumnSchema> toColumnSchemas, FromTypeColumnMappings fromTypeColumnMappings, int rowsPerBatch)
where TRow : classParameters
| Type | Name | Description |
|---|---|---|
| ALog | logger | The logger to use. |
| AdbCommand | adbCommand | A database command. Its |
| 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
On failure, Status will be
|
Type Parameters
| Name | Description |
|---|---|
| TRow | The type of the row. Must be a class. |
Overrides
Exceptions
| Type | Condition |
|---|---|
| ArgumentException |
|
| ArgumentNullException |
|
| Exception | See the provider documentation for which specific exceptions Open() can throw, it typically includes DbException and InvalidOperationException. |
| InvalidOperationException |
|
| NotImplementedException | Not implemented for provider. |