private string GenerateInserts(ComparerTableResult ctable, string sourceDB, string targetDB) { List <FieldInfo> allFields = new List <FieldInfo>(); allFields.AddRange(_extraFields); allFields.AddRange(ctable.UntouchedFields); allFields.AddRange(ctable.NewFields); allFields.AddRange(ctable.UpgradedFields); string cmdUPD = ""; foreach (var field in allFields) { cmdUPD += $"DECLARE @{field.Name} as {field.Type}; \r\n"; if (field.Type.Contains("char") || field.Type.Contains("text") || field.Type.Contains("date") || field.Type.Contains("time")) { cmdUPD += $"SET @{field.Name} = '{field.Value}'; \r\n"; } else { cmdUPD += $"SET @{field.Name} = {field.Value}; \r\n"; } } cmdUPD += "INSERT INTO [" + targetDB + "]." + ctable.FullName + " ( "; int fCount = 0; foreach (var field in allFields) { if (fCount > 0) { cmdUPD += ", "; } cmdUPD += $"[{field.Name}]"; fCount++; } cmdUPD += ") SELECT "; fCount = 0; foreach (var field in allFields) { if (fCount > 0) { cmdUPD += ", "; } if (field.IsExtra) { cmdUPD += $"@{field.Name} as [{field.Name}]"; } else { cmdUPD += "[" + field.Name + "]"; } fCount++; } cmdUPD += " FROM [" + sourceDB + "]." + ctable.FullName + " WITH (NOLOCK) \r\n"; return(cmdUPD); }
public ComparerTableResult CompareTable(TableInfo source, TableInfo target) { ComparerTableResult result = new ComparerTableResult(); result.Name = source.Name; result.Schema = source.Schema; foreach (var field in source.Fields) { if (target == null) { result.NewFields.Add(field); continue; } bool existField = target.Fields.Exists(t => t.Name == field.Name); if (!existField) { if (ExistField(result.NewFields, field)) { _logger.Log("Duplicate (NewFields) Field: " + field.Name, EventType.Error); } field.Nullable = true; result.NewFields.Add(field); } else { // Compare Field FieldInfo targetFiled = target.Fields.FirstOrDefault(t => t.Name == field.Name); try { if (field == targetFiled) { if (ExistField(result.UntouchedFields, field)) { _logger.Log("Duplicate (UntouchedFields) Field: " + field.Name, EventType.Error); } result.UntouchedFields.Add(field); } else { var uField = _fieldCompatibility.CreateCompatibleField(field, targetFiled); if (ExistField(result.UpgradedFields, uField)) { _logger.Log("Duplicate (UpgradedFields) Field: " + uField.Name, EventType.Error); } result.UpgradedFields.Add(uField); } } catch (IncompatibleTypesException ex) { _logger.Log(ex.Message, EventType.Error); result.ErrorFields.Add(field); } } } return(result); }
private string GenerateUpdates(ComparerTableResult ctable, string targetDB) { string cmdUPD = ""; foreach (var field in ctable.NewFields) { cmdUPD += "ALTER TABLE [" + targetDB + "]." + ctable.FullName + " ADD [" + field.Name + "] " + field.Type + (field.Nullable ? " NULL" : " NOT NULL") + "\r\n"; } foreach (var field in ctable.UpgradedFields) { cmdUPD += "ALTER TABLE [" + targetDB + "]." + ctable.FullName + " ALTER COLUMN [" + field.Name + "] " + field.Type + (field.Nullable ? " NULL" : " NOT NULL") + "\r\n"; } return(cmdUPD); }
public ComparerResult Compare(List <TableInfo> source, List <TableInfo> target, string[] targetSchemas) { _logger.Log("Checking structure differences.", EventType.Info); ComparerResult result = new ComparerResult(); if (source.Count == 0) { _logger.Log("No tables found in source database.", EventType.Error); return(result); } result.sourceDB = source[0].Database; if (target.Count == 0) { _logger.Log("No tables found in target database.", EventType.Error); return(result); } result.targetDB = target[0].Database; foreach (var table in source) { bool existTable = target.Exists(t => t.FullName == table.FullName); if (!existTable) { result.NewTables.Add(CompareTable(table, null)); if (targetSchemas.FirstOrDefault(s => s == table.Schema) == null) { result.NewSchemas.Add(table.Schema); } } else { var targetTable = target.FirstOrDefault(t => t.FullName == table.FullName); ComparerTableResult compTable = CompareTable(table, targetTable); if ((compTable.NewFields.Count > 0) || (compTable.RemovedFields.Count > 0) || (compTable.UpgradedFields.Count > 0)) { result.ModifiedTables.Add(compTable); } else { result.UntouchedTables.Add(compTable); } } } return(result); }
private string GenerateCreates(ComparerTableResult ctable, string targetDB) { string cmdCREATE = "CREATE TABLE [" + targetDB + "]." + ctable.FullName + " (\r\n"; string cmdPrimaryKey = ""; int fCount = 0; int pkCount = 0; ctable.NewFields.AddRange(_extraFields); foreach (var field in ctable.NewFields) { if (fCount > 0) { cmdCREATE += ",\r\n"; } cmdCREATE += " [" + field.Name + "] " + field.Type + (field.Nullable ? " NULL" : " NOT NULL"); if (field.IsPK) { if (string.IsNullOrEmpty(cmdPrimaryKey)) { cmdPrimaryKey += " PRIMARY KEY CLUSTERED (\r\n"; } if (pkCount > 0) { cmdPrimaryKey += ",\r\n"; } cmdPrimaryKey += "\t[" + field.Name + "] ASC"; pkCount++; } fCount++; } if (!string.IsNullOrEmpty(cmdPrimaryKey)) { cmdPrimaryKey += "\r\n )"; cmdCREATE += ",\r\n" + cmdPrimaryKey + "\r\n)\r\n"; } else { cmdCREATE += "\r\n)\r\n"; } return(cmdCREATE); }