public TwoWayDataMap(EntityToUpdateDefinition sourceDefinition, EntityToUpdateDefinition targetDefinition, JoinFieldCollection joinKeys, Func <DataRow, ConflictResolutionResult> conflictResolutionRule) : base(joinKeys, conflictResolutionRule) { if (sourceDefinition == null) { throw new Exception("Source entity definition can not be null."); } if (sourceDefinition.SyncSide != SyncSide.Source) { throw new Exception("Source entity definition can not be configured as target-side."); } if (targetDefinition == null) { throw new Exception("Target entity definition can not be null."); } if (targetDefinition.SyncSide != SyncSide.Target) { throw new Exception("Target entity definition can not be configured as source-side."); } SourceDefinition = sourceDefinition; TargetDefinition = targetDefinition; }
public OneToOneDataMap(JoinFieldCollection joinKeys, Func <DataRow, ConflictResolutionResult> conflictResolutionRule) : base(joinKeys) { if (conflictResolutionRule == null) { throw new Exception("Method for conflict resolution can not be null."); } ConflictResolutionRule = conflictResolutionRule; }
private static bool TargetKeyColumnsExist(DataTable targetTable, JoinFieldCollection joinKeyRelationships) { foreach (JoinFieldPair jkp in joinKeyRelationships.JoinFields) { if (!targetTable.Columns.Contains(jkp.TargetJoinField)) { throw new Exception(string.Format("Join column '{0}' not found in target DataTable.", jkp.TargetJoinField)); } } return(true); }
public DataMap(JoinFieldCollection joinKeys) { if (joinKeys == null) { throw new Exception("Join keys can not be null."); } if (joinKeys.Count == 0) { throw new Exception("At least one join key pair is required."); } JoinKeysCollection = joinKeys; }
private static void SetTargetPrimaryKeys(DataTable targetTable, JoinFieldCollection joinKeysCollection, string targetSidePrefix) { var targetJoinKeys = new DataColumn[joinKeysCollection.Count]; for (int i = 0; i < joinKeysCollection.Count; i++) { targetJoinKeys[i] = targetTable.Columns[targetSidePrefix + joinKeysCollection.JoinFields[i].TargetJoinField]; } try { targetTable.PrimaryKey = targetJoinKeys; } catch (Exception ex) { throw new Exception(string.Format("The target-side data has one or more duplicate primary key value(s) for column(s) '{0}'.", StringHelper.GetDelimitedString(joinKeysCollection.JoinFields.Select(d => d.TargetJoinField))), ex); } }
private static object[] GetKeyValues(DataRow row, JoinFieldCollection joinKeysCollection, SyncSide syncSide, string joinSidePrefix) { var keyValues = new object[joinKeysCollection.Count]; for (int i = 0; i < joinKeysCollection.Count; i++) { if (syncSide == SyncSide.Source) { keyValues[i] = row[joinSidePrefix + joinKeysCollection.JoinFields[i].SourceJoinField]; } else if (syncSide == SyncSide.Target) { keyValues[i] = row[joinSidePrefix + joinKeysCollection.JoinFields[i].TargetJoinField]; } else { throw new EnumValueNotImplementedException <SyncSide>(syncSide); } } return(keyValues); }
public OneWayDataMap(SyncDirection syncDirection, JoinFieldCollection joinKeys, EntityToUpdateDefinition entityToUpdateDefinition) : base(joinKeys, GetConflictResolutionRule(syncDirection)) { if (entityToUpdateDefinition == null) { throw new Exception("The definition for the entity to update can not be null."); } if (entityToUpdateDefinition.SyncSide == SyncSide.Target && syncDirection != SyncDirection.SourceToTarget) { throw new Exception("Target-side entity can not be updated when sync direction is target -> source."); } else if (entityToUpdateDefinition.SyncSide == SyncSide.Source && syncDirection != SyncDirection.TargetToSource) { throw new Exception("Source-side entity can not be updated when sync direction is source -> target."); } SyncDirection = syncDirection; EntityToUpdateDefinition = entityToUpdateDefinition; }
private static void SetCombinedPrimaryKeys(DataTable combinedTable, JoinFieldCollection joinKeysCollection, SyncSide syncSide, string joinSidePrefix) { var combinedJoinKeys = new DataColumn[joinKeysCollection.Count]; for (int i = 0; i < joinKeysCollection.Count; i++) { if (syncSide == SyncSide.Source) { combinedJoinKeys[i] = combinedTable.Columns[joinSidePrefix + joinKeysCollection.JoinFields[i].SourceJoinField]; } else if (syncSide == SyncSide.Target) { combinedJoinKeys[i] = combinedTable.Columns[joinSidePrefix + joinKeysCollection.JoinFields[i].TargetJoinField]; } else { throw new EnumValueNotImplementedException <SyncSide>(syncSide); } } try { combinedTable.PrimaryKey = combinedJoinKeys; } catch (Exception ex) { throw new Exception(string.Format("The {0}-side data has one or more duplicate primary key value(s) for column(s) '{1}'.", Enum.GetName(typeof(SyncSide), syncSide).ToLower(), StringHelper.GetDelimitedString(joinKeysCollection.JoinFields.Select(d => d.SourceJoinField))), ex); } // set the key to allow Nulls (otherwise, inserting target data w/o a matching source record will error) foreach (DataColumn col in combinedTable.PrimaryKey) { col.AllowDBNull = true; } }
public OneToMany_OneWayDataMap(SyncDirection syncDirection, JoinFieldCollection joinKeys, EntityToUpdateDefinition entityToUpdateDefinition, HashSet <string> columnNamesToTranspose, Func <IEnumerable <DataRow>, DataRow, string, TransposeResult> transposeMethod) : base(joinKeys) { if (entityToUpdateDefinition == null) { throw new Exception("The definition for the entity to update can not be null."); } if (entityToUpdateDefinition.SyncSide == SyncSide.Target && syncDirection != SyncDirection.SourceToTarget) { throw new Exception("Target-side entity can not be updated when sync direction is target -> source."); } else if (entityToUpdateDefinition.SyncSide == SyncSide.Source && syncDirection != SyncDirection.TargetToSource) { throw new Exception("Source-side entity can not be updated when sync direction is source -> target."); } if (columnNamesToTranspose == null || columnNamesToTranspose.Count == 0) { throw new Exception("At least one column name to transpose is required."); } if (transposeMethod == null) { throw new Exception("Transpose method can not be null."); } SyncDirection = syncDirection; EntityToUpdateDefinition = entityToUpdateDefinition; ColumnNamesToTranspose = columnNamesToTranspose; TransposeMethod = transposeMethod; }
public static DataTable ApplyLeftJoin(DataTable sourceTable, DataTable targetTable, JoinFieldCollection joinKeysCollection, string leftSidePrefix = SOURCE_PREFIX, string rightSidePrefix = TARGET_PREFIX) { if (leftSidePrefix == null) { leftSidePrefix = ""; } if (rightSidePrefix == null) { rightSidePrefix = ""; } if (leftSidePrefix.Trim() == rightSidePrefix.Trim()) { throw new Exception("Left and right side prefixes can not be the same."); } if (SourceKeyColumnsExist(sourceTable, joinKeysCollection) && TargetKeyColumnsExist(targetTable, joinKeysCollection)) { // modify source and target columns to include prefixes AddPrefixToColumnNames(leftSidePrefix, sourceTable); AddPrefixToColumnNames(rightSidePrefix, targetTable); // create a dataset and combined data table DataSet ds = new DataSet(); ds.Tables.Add("Combined"); DataTable combinedTable = ds.Tables[0]; // add source table columns foreach (DataColumn dc in sourceTable.Columns) { combinedTable.Columns.Add(dc.ToString(), dc.DataType); } // add target table columms foreach (DataColumn dc in targetTable.Columns) { combinedTable.Columns.Add(dc.ToString(), dc.DataType); } // clone the schema var unmatchingTargetRowsTable = combinedTable.Clone(); // add source data to combined table foreach (DataRow sourceRow in sourceTable.Rows) { combinedTable.ImportRow(sourceRow); } // set the table's primary key; a key is required for Rows.Find() SetTargetPrimaryKeys(targetTable, joinKeysCollection, rightSidePrefix); var searchTable = targetTable; // apply full outer join between source and target tables foreach (DataRow row in combinedTable.Rows) { var dr = searchTable.Rows.Find(GetKeyValues(row, joinKeysCollection, SyncSide.Source, leftSidePrefix)); // if a row matched, merge the row if (dr != null) { foreach (DataColumn dc in searchTable.Columns) { row[dc.ColumnName] = dr[dc.ColumnName]; } } } // remove the key from the combined table combinedTable.PrimaryKey = null; // remove source and target column prefixes RemovePrefixFromColumnNames(leftSidePrefix, sourceTable); RemovePrefixFromColumnNames(rightSidePrefix, targetTable); return(combinedTable); } else { throw new Exception("One or more join keys are missing from the source and target DataTable objects."); } }
/// <summary> /// Returns a combined DataTable using the specified join. /// </summary> /// <returns></returns> public static DataTable JoinTables(DataTable sourceTable, DataTable targetTable, JoinFieldCollection joinKeysCollection, JoinType joinType = JoinType.OuterJoin, string sourceSidePrefix = SOURCE_PREFIX, string targetSidePrefix = TARGET_PREFIX) { if (joinType == JoinType.OuterJoin) { return(ApplyFullOuterJoin(sourceTable, targetTable, joinKeysCollection, sourceSidePrefix, targetSidePrefix)); } else if (joinType == JoinType.LeftJoin) { return(ApplyLeftJoin(sourceTable, targetTable, joinKeysCollection, sourceSidePrefix, targetSidePrefix)); } else { throw new EnumValueNotImplementedException <JoinType>(joinType); } }
private static DataTable ApplyFullOuterJoin(DataTable sourceTable, DataTable targetTable, JoinFieldCollection joinKeys, string sourceSidePrefix = SOURCE_PREFIX, string targetSidePrefix = TARGET_PREFIX) { if (sourceSidePrefix == null) { sourceSidePrefix = ""; } if (targetSidePrefix == null) { targetSidePrefix = ""; } if (sourceSidePrefix.Trim() == targetSidePrefix.Trim()) { throw new Exception("Left and right side prefixes can not be the same."); } if (SourceKeyColumnsExist(sourceTable, joinKeys) && TargetKeyColumnsExist(targetTable, joinKeys)) { // modify source and target columns to include prefixes AddPrefixToColumnNames(sourceSidePrefix, sourceTable); AddPrefixToColumnNames(targetSidePrefix, targetTable); // create a dataset and combined data table DataSet ds = new DataSet(); ds.Tables.Add("Combined"); DataTable combinedTable = ds.Tables[0]; // add source table columns foreach (DataColumn dc in sourceTable.Columns) { combinedTable.Columns.Add(dc.ToString(), dc.DataType); } // add target table columms foreach (DataColumn dc in targetTable.Columns) { combinedTable.Columns.Add(dc.ToString(), dc.DataType); } // add source data to combined table foreach (DataRow sourceRow in sourceTable.Rows) { combinedTable.ImportRow(sourceRow); } // clone the schema var unmatchingTargetRowsTable = combinedTable.Clone(); // set the source table's primary key as the key for the combined table; a key is required for Rows.Find() SetCombinedPrimaryKeys(combinedTable, joinKeys, SyncSide.Source, sourceSidePrefix); // apply full outer join between source and target tables foreach (DataRow targetRow in targetTable.Rows) { var dr = combinedTable.Rows.Find(GetKeyValues(targetRow, joinKeys, SyncSide.Target, targetSidePrefix)); // if a row from the source table matched the target row's key, join the target data if (dr != null) { foreach (DataColumn dc in targetTable.Columns) { dr[dc.ColumnName] = targetRow[dc.ColumnName]; } } else { //if a matching record wasn't found, add the target row to as a new combined row var newRow = unmatchingTargetRowsTable.NewRow(); foreach (DataColumn dc in targetTable.Columns) { newRow[dc.ColumnName] = targetRow[dc.ColumnName]; } unmatchingTargetRowsTable.Rows.Add(newRow); } } // remove the key from the combined table combinedTable.PrimaryKey = null; foreach (DataRow targetOnlyRow in unmatchingTargetRowsTable.Rows) { combinedTable.Rows.Add(targetOnlyRow.ItemArray); } // remove source and target column prefixes RemovePrefixFromColumnNames(sourceSidePrefix, sourceTable); RemovePrefixFromColumnNames(targetSidePrefix, targetTable); return(combinedTable); } else { throw new Exception("One or more join keys are missing from the source and target DataTable objects."); } }