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).
Inheritance
DbMerge<TInput>
Namespace: ETLBox.DataFlow
Assembly: ETLBox.dll
Syntax
{{< /rawhtml >}}
```C#
public class DbMerge : DataFlowTransformation, IDataFlowTransformation, IDataFlowSource, IDataFlowSource, IDataFlowBatchDestination, IDataFlowDestination, IDataFlowBatchDestination, IDataFlowDestination, IDataFlowComponent, ILoggableTask
```
{{< rawhtml >}}
Type Parameters
| Name |
Description |
| TInput |
Type of ingoing data.
|
Constructors
DbMerge()
Declaration
{{< /rawhtml >}}
```C#
public DbMerge()
```
{{< rawhtml >}}
DbMerge(IConnectionManager, string)
Declaration
{{< /rawhtml >}}
```C#
public DbMerge(IConnectionManager connectionManager, string tableName)
```
{{< rawhtml >}}
Parameters
DbMerge(IConnectionManager, string, int)
Declaration
{{< /rawhtml >}}
```C#
public DbMerge(IConnectionManager connectionManager, string tableName, int batchSize)
```
{{< rawhtml >}}
Parameters
DbMerge(string)
Declaration
{{< /rawhtml >}}
```C#
public DbMerge(string tableName)
```
{{< rawhtml >}}
Parameters
| Type |
Name |
Description |
| string |
tableName |
|
DbMerge(string, int)
Declaration
{{< /rawhtml >}}
```C#
public DbMerge(string tableName, int batchSize)
```
{{< rawhtml >}}
Parameters
| Type |
Name |
Description |
| string |
tableName |
|
| int |
batchSize |
|
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
{{< /rawhtml >}}
```C#
public bool AllowIdentityInsert { get; set; }
```
{{< rawhtml >}}
Property Value
BatchSize
The batch size used when inserted data into the database table.
Declaration
{{< /rawhtml >}}
```C#
public int BatchSize { get; set; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public CacheMode CacheMode { get; set; }
```
{{< rawhtml >}}
Property Value
ColumnConverters
Use a column converter to apply a conversion function to each value of a column before writing
into the target
Declaration
{{< /rawhtml >}}
```C#
public ICollection ColumnConverters { get; set; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public ICollection ColumnMapping { get; set; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public ICollection CompareColumns { get; set; }
```
{{< rawhtml >}}
Property Value
CompareFunc
By default, the CompareColumns are used to determine if a row needs to be updated.
You can override the matching behavior using your own compare function which takes the current row and the
row from the destination table as input and returns true if the rows are equal (no update), and false if the destination row should be updated.
You still need to define the CompareColumns to specify which columns should be retrieved from the destination table.
Declaration
{{< /rawhtml >}}
```C#
public Func CompareFunc { get; set; }
```
{{< rawhtml >}}
Property Value
ConnectionManager
The connection manager used to connect to the database - use the right connection manager for your database type.
Declaration
{{< /rawhtml >}}
```C#
public virtual IConnectionManager ConnectionManager { get; set; }
```
{{< rawhtml >}}
Property Value
DeleteColumns
List of property names and a to-be value that tells the DbMerge if this row can be deleted.
Declaration
{{< /rawhtml >}}
```C#
public ICollection DeleteColumns { get; set; }
```
{{< rawhtml >}}
Property Value
DeletedCount
Number of rows that were deleted.
Declaration
{{< /rawhtml >}}
```C#
public int DeletedCount { get; }
```
{{< rawhtml >}}
Property Value
DuplicateCount
Number of duplicate rows that were detected (if FindDuplicates is enabled).
Declaration
{{< /rawhtml >}}
```C#
public int DuplicateCount { get; }
```
{{< rawhtml >}}
Property Value
EvictionPolicy
The eviction policy used for the partial cache.
Only applies if the CacheMode is set to Partial.
Declaration
{{< /rawhtml >}}
```C#
public CacheEvictionPolicy EvictionPolicy { get; set; }
```
{{< rawhtml >}}
Property Value
ExistsCount
Number of rows that already existed and were not modified.
Declaration
{{< /rawhtml >}}
```C#
public int ExistsCount { get; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public bool FindDuplicates { get; set; }
```
{{< rawhtml >}}
Property Value
IdColumns
Property names that are used to check if the columns match (id values are equal).
Declaration
{{< /rawhtml >}}
```C#
public ICollection IdColumns { get; set; }
```
{{< rawhtml >}}
Property Value
IgnoreDefaultColumnsOnInsert
If set to true, columns that have a default value declartion in the database are
ignored when inserting data
Declaration
{{< /rawhtml >}}
```C#
public bool IgnoreDefaultColumnsOnInsert { get; set; }
```
{{< rawhtml >}}
Property Value
InsertColumns
Property names that describe which columns are actually insert (if an insert of the row is necessary).
Can be left empty, then all matching columns will be inserted.
Declaration
{{< /rawhtml >}}
```C#
public ICollection InsertColumns { get; set; }
```
{{< rawhtml >}}
Property Value
InsertedCount
Number of rows that were inserted.
Declaration
{{< /rawhtml >}}
```C#
public int InsertedCount { get; }
```
{{< rawhtml >}}
Property Value
MatchCaseInsensitive
If set to true, the DbMerge will automatically create column mappings for properties
that have a case-insensitive match with database columns (e.g., property "col1" matches database column "COL1").
Cannot be used together with ColumnMapping - if you have custom column mappings, you need to handle case differences manually.
Declaration
{{< /rawhtml >}}
```C#
public bool MatchCaseInsensitive { get; set; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public int MaxCacheSize { get; set; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public MergeMode MergeMode { get; set; }
```
{{< rawhtml >}}
Property Value
NotFoundCount
Number of rows that were marked for deletion but not found in the destination table (only applies to Delta mode).
Declaration
{{< /rawhtml >}}
```C#
public int NotFoundCount { get; }
```
{{< rawhtml >}}
Property Value
ReadConnectionManager
Only applies if CacheMode is set to Partial.
When using a transaction or a single connection that is left open, the DbMerge needs a second connection manager
for reading the data from the destination table.
Declaration
{{< /rawhtml >}}
```C#
public IConnectionManager ReadConnectionManager { get; set; }
```
{{< rawhtml >}}
Property Value
SourceBlock
SourceBlock from the underlying TPL.Dataflow which is used as output buffer for the component.
Declaration
{{< /rawhtml >}}
```C#
public override ISourceBlock SourceBlock { get; }
```
{{< rawhtml >}}
Property Value
Overrides
TableDefinition
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
{{< /rawhtml >}}
```C#
public TableDefinition TableDefinition { get; set; }
```
{{< rawhtml >}}
Property Value
TableName
The name of the target database table for the merge.
Declaration
{{< /rawhtml >}}
```C#
public string TableName { get; set; }
```
{{< rawhtml >}}
Property Value
TargetBlock
TargetBlock from the underlying TPL.Dataflow which is used as input buffer for the component.
Declaration
{{< /rawhtml >}}
```C#
public override ITargetBlock TargetBlock { get; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public ICollection UpdateColumns { get; set; }
```
{{< rawhtml >}}
Property Value
UpdatedCount
Number of rows that were updated.
Declaration
{{< /rawhtml >}}
```C#
public int UpdatedCount { get; }
```
{{< rawhtml >}}
Property Value
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
{{< /rawhtml >}}
```C#
public bool UseTruncateMethod { get; set; }
```
{{< rawhtml >}}
Property Value
UseUpdateColumnsForInserts
If set to true, the UpdateColumns will be used also for inserts (only if no insert
columns are explicitly specified).
Declaration
{{< /rawhtml >}}
```C#
public bool UseUpdateColumnsForInserts { get; set; }
```
{{< rawhtml >}}
Property Value
ValueGeneratedColumns
Declaration
{{< /rawhtml >}}
```C#
public ICollection ValueGeneratedColumns { get; set; }
```
{{< rawhtml >}}
Property Value
Methods
CheckParameter()
Declaration
{{< /rawhtml >}}
```C#
protected override void CheckParameter()
```
{{< rawhtml >}}
Overrides
CheckParameterAfterInternalFlowCreation()
Declaration
{{< /rawhtml >}}
```C#
protected void CheckParameterAfterInternalFlowCreation()
```
{{< rawhtml >}}
CleanUpOnFaulted(Exception)
Declaration
{{< /rawhtml >}}
```C#
protected override void CleanUpOnFaulted(Exception e)
```
{{< rawhtml >}}
Parameters
Overrides
CleanUpOnSuccess()
Declaration
{{< /rawhtml >}}
```C#
protected override void CleanUpOnSuccess()
```
{{< rawhtml >}}
Overrides
CompleteBuffer()
Declaration
{{< /rawhtml >}}
```C#
protected override void CompleteBuffer()
```
{{< rawhtml >}}
Overrides
FaultBuffer(Exception)
Declaration
{{< /rawhtml >}}
```C#
protected override void FaultBuffer(Exception e)
```
{{< rawhtml >}}
Parameters
Overrides
InitComponent()
Declaration
{{< /rawhtml >}}
```C#
protected override void InitComponent()
```
{{< rawhtml >}}
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
{{< /rawhtml >}}
```C#
public override IDataFlowSource LinkErrorTo(IDataFlowDestination target)
```
{{< rawhtml >}}
Parameters
Returns
Overrides
PrepareParameterForCheck()
Declaration
{{< /rawhtml >}}
```C#
protected override void PrepareParameterForCheck()
```
{{< rawhtml >}}
Overrides
Reset()
Declaration
{{< /rawhtml >}}
```C#
protected override void Reset()
```
{{< rawhtml >}}
Overrides
Implements
{{< /rawhtml >}}