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