/// <summary>
/// Given a parent and child, this method creates the SQL statement needed to update tables and make both current
/// and in sync with each other.
/// </summary>
/// <param name="tableMap">Map defining the From-To column combination</param>
/// <param name="parentColumns">List of parent Columns and pertinent values</param>
/// <param name="childColumns">List of child Columns and pertinent values</param>
/// <param name="guid">The unique link that's equal between parent and child</param>
/// <param name="tableToUpdate">Table that we're going to update</param>
/// <param name="version">The highest RowVersion we're going to update table with</param>
/// <param name="conn">Connetion to target table</param>
/// <returns>Returns string with the update SQL statement</returns>
private static string UpdateSqlCmd(TableMap tableMap, List <DataColumn> parentColumns, List <DataColumn> childColumns, Guid guid, string tableToUpdate, long version, SqlConnection conn)
{
var setCmd = "";
//For each column, get both values and compare to each other. If ANY are different, add to setCmd
parentColumns.ForEach(parentColumn =>
{
var parentName = parentColumn.Name;
var parentValue = parentColumn.Value;
childColumns.ForEach(childColumn =>
{
var childName = childColumn.Name;
var childValue = childColumn.Value;
//Names of columns may be different so get name based on map
var parentColumnMap = tableMap.ColumnMaps.FirstOrDefault(p => p.FromColumn.Name == parentName);
var childColumnMap = tableMap.ColumnMaps.FirstOrDefault(p => p.ToColumn.Name == parentName);
var mappedName = "";
var isAutoIncremented = false;
var isKey = false;
//If parentColumnMap != null it's a parent-to-Child scenario, else it's child-to-parent
if (parentColumnMap != null)
{
mappedName = parentColumnMap.ToColumn.Name;
isAutoIncremented = parentColumnMap.ToColumn.IsAutoNumber;
isKey = parentColumnMap.ToColumn.IsKey;
}
else if (childColumnMap != null)
{
mappedName = childColumnMap.FromColumn.Name;
isAutoIncremented = childColumnMap.FromColumn.IsAutoNumber;
isKey = childColumnMap.FromColumn.IsKey;
}
//If it's not auto-incremented and not column RowVersion (added later), include as part of set.
if (!isAutoIncremented && !childName.Equals("RowVersion"))
{
//If the column names are the same and the values are different we should update that field.
if (childName.Equals(mappedName) && !childValue.Equals(parentValue))
{
var type = childColumn.ColumnType;
var value = (type == typeof(int) || type == typeof(long) ? parentValue : "'" + parentValue + "'");
//If it's a primary columnName, find a unique value if neccessary
if (isKey)
{
value = GenerateUniqueKey(conn, tableToUpdate, mappedName, parentValue.ToString(), type);
}
//Add all else to set
setCmd += childName + "=" + value + ", ";
}
}
});
});
return($@"UPDATE {tableToUpdate}
SET {setCmd} RowVersion = {version}
WHERE RowGuid='{guid}'");
}
/// <summary>
/// Syncs both tables together after verifying which table has greater RowVersion or insert if missing
/// </summary>
/// <param name="tableMap">Map defining the From-To column combination</param>
/// <param name="childConn">Open child connection</param>
/// <param name="parentConn">Open parent connection</param>
/// <param name="childTable">Name of Child table</param>
/// <param name="parentTable">Name of Parent table</param>
/// <param name="diff">Dictionary with Unique Guid's as columnName and List parent/child rows as value</param>
private static void SyncTables(TableMap tableMap, SqlConnection childConn, SqlConnection parentConn, string childTable, string parentTable, Dictionary <Guid, ParentChild> diff)
{
//For each difference found process it
foreach (var entry in diff)
{
//Get the row versions
var parentChild = entry.Value;
var parentRow = parentChild.Parent.Columns.FirstOrDefault(c => c.Name.Equals("RowVersion"));
var childRow = parentChild.Child.Columns.FirstOrDefault(c => c.Name.Equals("RowVersion"));
//If it's equal to null, return 0 else return the value.
//This is a sanity check as it should never be null.
var parentRowVersion = (long?)parentRow?.Value ?? 0;
var childRowVersion = (long?)childRow?.Value ?? 0;
string cmd;
var sqlCmd = new SqlCommand();
//If row version is 0, we didn't have a match so an insert needs to happen
if (parentRowVersion == 0 || childRowVersion == 0)
{
if (parentRowVersion != 0)
{
//We found a parent that didn't have a matching child.
SyncReport += $"GUID: {entry.Key} has an entry in table {parentTable} but not in {childTable}" + " \n";
if (!isReportOnly)
{
var fromColumns = parentChild.Parent.Columns;
cmd = InsertSqlCmd(tableMap, fromColumns, childTable, childConn);
//Insert into child
sqlCmd = new SqlCommand(cmd, childConn);
}
}
else
{
//We found a child that didn't have a matching parent.
SyncReport += $"GUID: {entry.Key} has an entry in table {childTable} but not in {parentTable}" + " \n";
if (!isReportOnly)
{
var fromColumns = parentChild.Child.Columns;
cmd = InsertSqlCmd(tableMap, fromColumns, parentTable, parentConn);
//Insert into parent
sqlCmd = new SqlCommand(cmd, parentConn);
}
}
}
else
{
//Check for different rowVersions between parent and child and update
if (parentRowVersion > childRowVersion)
{
//Parent is more up-to-date, update child
SyncReport += $@"GUID: {entry.Key} has a higher RowVersion of {parentRowVersion} in {parentTable} compared to {childRowVersion} in {childTable}" + " \n";
if (!isReportOnly)
{
var fromColumns = parentChild.Parent.Columns;
var toColumns = parentChild.Child.Columns;
cmd = UpdateSqlCmd(tableMap, fromColumns, toColumns, entry.Key, childTable, parentRowVersion, childConn);
//Update the child
sqlCmd = new SqlCommand(cmd, childConn);
}
}
else
{
//Child is more up-to-date, update parent
SyncReport += $@"GUID: {entry.Key} has a higher RowVersion of {childRowVersion} in {childTable} compared to {parentRowVersion} in {parentTable}" + " \n";
if (!isReportOnly)
{
var fromColumns = parentChild.Child.Columns;
var toColumns = parentChild.Parent.Columns;
cmd = UpdateSqlCmd(tableMap, fromColumns, toColumns, entry.Key, parentTable, childRowVersion, parentConn);
//Update the parent
sqlCmd = new SqlCommand(cmd, parentConn);
}
}
}
if (!isReportOnly)
{
sqlCmd.ExecuteNonQuery();
SyncReport += "Differences were remedied \n";
}
}
}