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()); }