private string BuildSqlCmdTextForUpdateData(
Dictionary <string, object> destColumnNameValueMappings,
TableMapping tableMapping,
KeySet sourceKeySet)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("UPDATE {0} SET ", tableMapping.Destination);
foreach (ColumnMapping columnMapping in tableMapping.ColumnMappings)
{
sb.AppendFormat(
"{0} = {1}, ",
columnMapping.Destination,
GetParaSpliter() + columnMapping.Destination);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(" WHERE ");
foreach (var key in sourceKeySet.Keys)
{
sb.AppendFormat("{0} = {1} AND ", key.Item1, GetParaSpliter() + key.Item1);
}
sb.Remove(sb.Length - 4, 4);
return(sb.ToString());
}
public long Write(DataTable data, TableMapping tableMapping, ref StringBuilder msgBuilder, ref int insertRowCount, ref int updateRowCount)
{
if (data.Rows.Count == 0)
{
return(0);
}
msgBuilder.Append(string.Format("Table sync detail for : {0} \n", tableMapping.Source));
var destColumnMetaDataList = LoadTableMetaData(tableMapping.Destination);
ValidateTableMapping(tableMapping, destColumnMetaDataList);
var destColumnNameTypeMappings = destColumnMetaDataList.ToDictionary(
columnMetaData => columnMetaData.ColumnName,
columnMetaData => columnMetaData.ColumnType,
StringComparer.OrdinalIgnoreCase);
if (tableMapping.IsContainFile)
{
return(SaveVarBinaryData(data, tableMapping, destColumnNameTypeMappings));
}
if (tableMapping.IsContainFilePath)
{
return(SaveVarBinaryDataFromPath(data, tableMapping, destColumnNameTypeMappings, tableMapping.FilePath, tableMapping.PathColumn));
}
var keyColumnNames = tableMapping.GetKeyColumnNames();
var sourceDataValues = new Dictionary <KeySet, Dictionary <string, object> >();
foreach (DataRow dataRow in data.Rows)
{
var destColumnNameValueMappings = LoadDestColumnNameValueMappings(dataRow, tableMapping);
var keySet = new KeySet();
foreach (var keyColumnName in keyColumnNames)
{
keySet.Add(keyColumnName, destColumnNameTypeMappings[keyColumnName], destColumnNameValueMappings[keyColumnName]);
}
if (sourceDataValues.ContainsKey(keySet))
{
throw new Exception(string.Format("The source data (table: {0}) contains rows with duplicated keys", tableMapping.Source));
}
sourceDataValues.Add(keySet, destColumnNameValueMappings);
}
var immutableKeySets = GetImmutableKeySets(sourceDataValues, tableMapping, destColumnNameTypeMappings);
var destinationKeySets = GetDestinationKeySets(immutableKeySets, tableMapping, destColumnNameTypeMappings, keyColumnNames);
if (!tableMapping.IsKeepObsoleteDestinationData)
{
RemoveObsoleteDestinationData(sourceDataValues, destinationKeySets, tableMapping, destColumnNameTypeMappings);
}
return(SaveSourceData(sourceDataValues, destinationKeySets, tableMapping, destColumnNameTypeMappings, ref msgBuilder, ref insertRowCount, ref updateRowCount));
}
private HashSet <KeySet> GetDestinationKeySets(
HashSet <KeySet> immutableKeySets,
TableMapping tableMapping,
Dictionary <string, DbColumnType> destColumnNameTypeMappings,
IEnumerable <string> keyColumnNames)
{
var keySets = new HashSet <KeySet>();
var chunkIndex = 0;
var chunkSize = 1000;
while (chunkIndex * chunkSize < immutableKeySets.Count)
{
var chunkedImmutableKeySets = immutableKeySets.Skip(chunkIndex++ *chunkSize).Take(chunkSize);
using (IDbCommand cmd = GetDbCommand(
BuildSqlCmdTextForLoadDestinationKeySets(
chunkedImmutableKeySets,
tableMapping,
keyColumnNames), _conn, _trans))
{
cmd.CommandTimeout = _sqlCommandTimeout;
using (IDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
var keySet = new KeySet();
foreach (var keyColumnName in keyColumnNames)
{
keySet.Add(keyColumnName, destColumnNameTypeMappings[keyColumnName], dataReader[keyColumnName]);
}
if (keySets.Contains(keySet))
{
throw new Exception(
string.Format(
"The destination table ({0}) contains data with duplicated keys ({1})",
tableMapping.Destination,
keySet.ToString()));
}
keySets.Add(keySet);
}
}
}
}
return(keySets);
}
private HashSet <KeySet> GetImmutableKeySets(
Dictionary <KeySet, Dictionary <string, object> > sourceDataValues,
TableMapping tableMapping,
Dictionary <string, DbColumnType> destColumnNameTypeMappings)
{
var immutableKeySets = new HashSet <KeySet>();
var immutableKeyColumnName = tableMapping.GetImmutableKeyColumnName();
foreach (var row in sourceDataValues.Values)
{
KeySet keySet = new KeySet();
keySet.Add(immutableKeyColumnName, destColumnNameTypeMappings[immutableKeyColumnName], row[immutableKeyColumnName]);
if (!immutableKeySets.Contains(keySet))
{
immutableKeySets.Add(keySet);
}
}
return(immutableKeySets);
}
public override bool Equals(object obj)
{
if (object.ReferenceEquals(this, obj))
{
return(true);
}
KeySet other = obj as KeySet;
if (other == null)
{
return(false);
}
if (this.keyNameTypeValueSet.Count != other.keyNameTypeValueSet.Count)
{
return(false);
}
foreach (var kvp in this.keyNameTypeValueSet)
{
if (!other.keyNameTypeValueSet.ContainsKey(kvp.Key))
{
return(false);
}
var thisKeyTypeValueTuple = kvp.Value;
var otherKeyTypeValueTuple = other.keyNameTypeValueSet[kvp.Key];
if (!(thisKeyTypeValueTuple.Item1 == otherKeyTypeValueTuple.Item1 &&
GetStringValue(thisKeyTypeValueTuple.Item1, thisKeyTypeValueTuple.Item2) == GetStringValue(otherKeyTypeValueTuple.Item1, otherKeyTypeValueTuple.Item2)))
{
return(false);
}
}
return(true);
}
private string BuildSqlCmdTextForRemoveObsoleteDestinationData(TableMapping tableMapping, KeySet destKeySet)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("DELETE FROM {0} ", tableMapping.Destination);
sb.Append(" WHERE ");
foreach (var key in destKeySet.Keys)
{
sb.AppendFormat(" {0} = {1} AND ", key.Item1, GetParaSpliter() + key.Item1);
}
sb.Remove(sb.Length - 4, 4);
return(sb.ToString());
}
