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.");
}
}