static string overwriteSql(JobSettings settings, string target, string source, string primaryKey, IEnumerable <string> restOfColumns)
{
string sql = $"UPDATE t SET ";
var firstColumn = true;
foreach (var column in restOfColumns)
{
if (firstColumn)
{
firstColumn = false;
}
else
{
sql += ",\n";
}
sql += $"[{column}] = ISNULL(s.[{column}],t.[{column}])";
}
if (settings.UseAuditColumnsOnImport.Value)
{
sql += @",
@modifiedUser = SUSER_NAME(),
@modifiedDate = GETDATE()";
}
sql += $"\nFROM {target} t\nINNER JOIN {source} s ON t.{primaryKey} = s.{primaryKey}";
return(sql);
}
public bool Initialize <T>(JobSettings settings, IErrorHandler errorHandler)
{
Fields.AddRange(typeof(T).GetProperties().Select(p => new Core.Table.Field {
Name = p.Name, IsNullable = TypeCanBeNull(p.PropertyType)
}));
return(Initialize(settings, errorHandler));
}
public bool Initialize(SqlConnection connection, JobSettings settings, IErrorHandler errorHandler)
{
Fields.AddRange(connection.Query <Field>(@"
SELECT c.Name, c.is_identity as IsPrimaryKey, c.is_nullable AS IsNullable, df.definition AS DefaultValue
FROM sys.all_objects o
INNER JOIN sys.all_columns c ON o.object_id = c.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
LEFT JOIN sys.default_constraints df ON c.default_object_id = df.object_id
WHERE o.Name = @table AND s.Name = @schema
ORDER BY column_id
", new { table = BasicName, schema = SchemaName }));
return(Initialize(settings, errorHandler));
}
public static string GetSqlForMergeStrategy(JobSettings settings, Table table)
{
var target = table.QualifiedName;
var source = "##" + table.BasicName;
var primaryKey = table.PrimaryKey;
var restOfColumns = table.DataFields;
var configObject = new
{
target = target,
id = primaryKey,
columns = string.Join(",", restOfColumns.Select(c => $"[{c}]")),
source = source,
columnUpdateList = string.Join(",", restOfColumns.Select(r => $"[{r}]=s.[{r}]")),
modifiedUser = settings.AuditColumns?.ModifiedUser,
modifiedDate = settings.AuditColumns?.ModifiedDate,
createdUser = settings.AuditColumns?.CreatedUser,
createdDate = settings.AuditColumns?.CreatedDate
};
string sqlToUse = null;
switch (table.MergeStrategy ?? settings.MergeStrategy)
{
case Strategy.MergeWithoutDelete:
sqlToUse = getInsert(settings) + "\n" + getUpdate(settings);
break;
case Strategy.MergeWithDelete:
sqlToUse = getInsert(settings) + "\n" + getUpdate(settings) + "\n" + delete;
break;
case Strategy.AddOnly:
sqlToUse = getInsert(settings);
break;
case Strategy.Override:
sqlToUse = getInsert(settings) + "\n" + overwriteSql(settings, target, source, primaryKey, restOfColumns);
break;
case Strategy.DropReadd:
sqlToUse = dropReadd;
break;
default:
throw new NotImplementedException("That merge strategy is not yet supported");
}
return(sqlToUse.FormatWith(configObject));
}
public SqlSimpleDataWriter(StreamWriter file, Table table, JobSettings settings)
{
outputFormatter = new FileOutputFormatter(file);
this.table = table;
this.settings = settings;
}
public SqlSimpleDataWriter(string connectionString, Table table, JobSettings settings)
{
outputFormatter = new SqlOutputFormatter(connectionString);
this.table = table;
this.settings = settings;
}
static string getInsert(JobSettings settings) => settings.UseAuditColumnsOnImport.Value ? insertWithAudit : insert;
static string getUpdate(JobSettings settings) => settings.UseAuditColumnsOnImport.Value ? updateWithAudit : update;
bool Initialize(JobSettings settings, IErrorHandler errorHandler)
{
this.settings = settings;
var auditColumns = settings.AuditColumns.AuditColumnNames().Select(c => c.ToLowerInvariant()).ToList();
if (settings.UseAuditColumnsOnImport ?? false)
{
foreach (var field in Fields)
{
if (auditColumns.Contains(field.CanonicalName))
{
field.IsAuditingColumn = true;
}
}
}
if (!Fields.Any())
{
errorHandler.Error($"Could not find any information for table {Name}. Make sure it exists in the target database");
return(false);
}
var data = Fields.Select(f => f.Name.ToLowerInvariant());
if (PrimaryKey == null)
{
var primaryKeys = Fields.Where(f => f.IsPrimaryKey).ToList();
if (primaryKeys.Count == 0)
{
//errorHandler.Warning($"No primary key set for table {Name}, trying to infer from name");
primaryKeys = Fields.Where(f => f.Name.ToLowerInvariant() == "id" || f.Name.ToLowerInvariant() == BasicName.ToLowerInvariant() + "id").ToList();
}
if (primaryKeys.Count > 1)
{
errorHandler.Error($"Multiple primary keys found for table {Name} ({string.Join(", ", primaryKeys.Select(pk => pk.Name))}). Please specify one manually.");
return(false);
}
if (!primaryKeys.Any())
{
errorHandler.Error($"No primary key could be found for table {Name}. Please specify one manually");
return(false);
}
PrimaryKey = primaryKeys.Single().Name;
}
PrimaryKey = PrimaryKey.ToLowerInvariant();
data = data.Where(f => f != PrimaryKey);
if (settings.UseAuditColumnsOnImport ?? false)
{
data = data.Where(f => !settings.AuditColumns.AuditColumnNames().Select(a => a.ToLowerInvariant()).Contains(f));
}
if (IsEnvironmentSpecific)
{
data = data.Where(f => f != "isenvironmentspecific");
}
DataFields = data.ToList();
return(true);
}