DbMerge<TInput>

Class DbMerge<TInput>

Inserts, updates and (optionally) deletes data in database target. Before the Merge is executed, all data from the destination is read into memory. A delta table is generated that stores information if a records was inserted, updated, deleted or hasn't been touched (existed).

Inherited Members
Namespace: ETLBox.DataFlow
Assembly: ETLBox.dll
Syntax
    public class DbMerge<TInput> : DataFlowTransformation<TInput, TInput>, IDataFlowTransformation<TInput, TInput>, IDataFlowSource<TInput>, IDataFlowSource, IDataFlowBatchDestination<TInput>, IDataFlowDestination<TInput>, IDataFlowBatchDestination, IDataFlowDestination, IDataFlowComponent, ILoggableTask
Type Parameters
NameDescription
TInput

Type of ingoing data.

Constructors

DbMerge()

Declaration
    public DbMerge()

DbMerge(IConnectionManager, string)

Declaration
    public DbMerge(IConnectionManager connectionManager, string tableName)
Parameters
TypeNameDescription
IConnectionManagerconnectionManager
stringtableName

DbMerge(IConnectionManager, string, int)

Declaration
    public DbMerge(IConnectionManager connectionManager, string tableName, int batchSize)
Parameters
TypeNameDescription
IConnectionManagerconnectionManager
stringtableName
intbatchSize

DbMerge(string)

Declaration
    public DbMerge(string tableName)
Parameters
TypeNameDescription
stringtableName

DbMerge(string, int)

Declaration
    public DbMerge(string tableName, int batchSize)
Parameters
TypeNameDescription
stringtableName
intbatchSize

Properties

AllowIdentityInsert

By default, identity columns (a.k.a auto increment or serial columns) are ignored when writing into the destination. If set to true, the DbMerge will try to keep existing identity values if possible.

Declaration
    public bool AllowIdentityInsert { get; set; }
Property Value
TypeDescription
bool

BatchSize

The batch size used when inserted data into the database table.

Declaration
    public int BatchSize { get; set; }
Property Value
TypeDescription
int

CacheMode

The cache mode used for reading data from the destination table. By default, all data is read into memory before the actual merge is executed (Full mode). If set to partial, only data is read into memory that is needed for the current merge comparison. This will keep the memory consumption of the DbMerge much smaller.

Declaration
    public CacheMode CacheMode { get; set; }
Property Value
TypeDescription
CacheMode

ColumnConverters

Use a column converter to apply a conversion function to each value of a column before writing into the target

Declaration
    public ICollection<ColumnConverter> ColumnConverters { get; set; }
Property Value
TypeDescription
ICollection<ColumnConverter>

ColumnMapping

Column mapping to map property to column names. E.g. if the value of property "Id" should be written into the database column "Key", then you can simply add a column mapping: DbColumnName: "Id" -> PropertyName: "Key".

Declaration
    public ICollection<DbColumnMap> ColumnMapping { get; set; }
Property Value
TypeDescription
ICollection<DbColumnMap>

CompareColumns

Property names that should be use to compare if the values of a column are equal, so that the DbMerge can decide if the column needs to be updated.

Declaration
    public ICollection<CompareColumn> CompareColumns { get; set; }
Property Value
TypeDescription
ICollection<CompareColumn>

ConnectionManager

The connection manager used to connect to the database - use the right connection manager for your database type.

Declaration
    public virtual IConnectionManager ConnectionManager { get; set; }
Property Value
TypeDescription
IConnectionManager

DeleteColumns

List of property names and a to-be value that tells the DbMerge if this row can be deleted.

Declaration
    public ICollection<DeleteColumn> DeleteColumns { get; set; }
Property Value
TypeDescription
ICollection<DeleteColumn>

DestinationTableDefinition

The table definition of the destination table. By default, the table definition is read from the database. Provide a table definition if the definition of the target can't be read automatically or you want the DbMerge only to use the columns in the provided definition.

Declaration
    public TableDefinition DestinationTableDefinition { get; set; }
Property Value
TypeDescription
TableDefinition

EvictionPolicy

The eviction policy used for the partial cache. Only applies if the CacheMode is set to Partial.

Declaration
    public CacheEvictionPolicy EvictionPolicy { get; set; }
Property Value
TypeDescription
CacheEvictionPolicy

FindDuplicates

By default, the DbMerge expects the IdColumns to be unique. If another record with the same id values arrives, this record is then identified as a duplicate. It won't be written into the destination table - the delta output will contain this record, and the ChangeAction is set to Duplicate Please note that this may have an impact on the overall memory consumption, as already processed id values need to be stored internally during processing.

Declaration
    public bool FindDuplicates { get; set; }
Property Value
TypeDescription
bool

IdColumns

Property names that are used to check if the columns match (id values are equal).

Declaration
    public ICollection<IdColumn> IdColumns { get; set; }
Property Value
TypeDescription
ICollection<IdColumn>

IgnoreDefaultColumnsOnInsert

If set to true, columns that have a default value declartions in the database are ignored when inserting data

Declaration
    public bool IgnoreDefaultColumnsOnInsert { get; set; }
Property Value
TypeDescription
bool

MaxCacheSize

Defines the max amount of rows stored in the partial cache if the eviction policy is set to a different value than FullRefresh. Only applies if the CacheMode is set to Partial.

Declaration
    public int MaxCacheSize { get; set; }
Property Value
TypeDescription
int

MergeMode

Defines the type of target data which affects how deletions or insertions are handled. Full means that source contains all data, NoDeletions that source contains all data but no deletions are executed, Delta means that source has only delta information and deletions are deferred from a particular property and OnlyUpdates means that only updates are applied to the destination.

Declaration
    public MergeMode MergeMode { get; set; }
Property Value
TypeDescription
MergeMode

SourceBlock

SourceBlock from the underlying TPL.Dataflow which is used as output buffer for the component.

Declaration
    public override ISourceBlock<TInput> SourceBlock { get; }
Property Value
TypeDescription
ISourceBlock<TInput>
Overrides

TableName

The name of the target database table for the merge.

Declaration
    public string TableName { get; set; }
Property Value
TypeDescription
string

TargetBlock

TargetBlock from the underlying TPL.Dataflow which is used as input buffer for the component.

Declaration
    public override ITargetBlock<TInput> TargetBlock { get; }
Property Value
TypeDescription
ITargetBlock<TInput>
Overrides

UpdateColumns

Property names that describe which columns are actually updated (if an update of the row is necessary). Can be left empty, then all non id columns will be updated.

Declaration
    public ICollection<UpdateColumn> UpdateColumns { get; set; }
Property Value
TypeDescription
ICollection<UpdateColumn>

UseTruncateMethod

By default, the deletion of records are performed with a bulk delete. This performs well if the number of records to delete is small compared to the total amount of records. If this property is set to true, all records are deleted before the merge operation, using a TRUNCATE. Subsequently, all records are reinserted again. In order to make this work, the MergeMode needs to be set to Full and the CacheMode set to Full as well.

Declaration
    public bool UseTruncateMethod { get; set; }
Property Value
TypeDescription
bool

ValueGeneratedColumns

Exposes the ValueGeneratedColumns property from the underlying DbDestination used for inserts/updates.

Declaration
    public ICollection<ValueGenerationColumn> ValueGeneratedColumns { get; set; }
Property Value
TypeDescription
ICollection<ValueGenerationColumn>

Methods

CheckParameter()

Declaration
    protected override void CheckParameter()
Overrides

CheckParameterAfterInternalFlowCreation()

Declaration
    protected void CheckParameterAfterInternalFlowCreation()

CleanUpOnFaulted(Exception)

Declaration
    protected override void CleanUpOnFaulted(Exception e)
Parameters
TypeNameDescription
Exceptione
Overrides

CleanUpOnSuccess()

Declaration
    protected override void CleanUpOnSuccess()
Overrides

InitComponent()

Declaration
    protected override void InitComponent()
Overrides

LinkErrorTo(IDataFlowDestination<ETLBoxError>)

If an error occurs in the component, by default the component will throw an exception and stop execution. If you use the error linking, any erroneous records will be caught and redirected.

Declaration
    public override IDataFlowSource<ETLBoxError> LinkErrorTo(IDataFlowDestination<ETLBoxError> target)
Parameters
TypeNameDescription
IDataFlowDestination<ETLBoxError>target

The target for erroneous rows.

Returns
TypeDescription
IDataFlowSource<ETLBoxError>

The linked component.

Overrides

PrepareParameterForCheck()

Declaration
    protected override void PrepareParameterForCheck()
Overrides

Reset()

Declaration
    protected override void Reset()
Overrides

Implements