/// <summary>
/// Starts a data merge operation with the specified destination table name.
/// </summary>
/// <param name="session">The session to use for the merge.</param>
/// <param name="destinationTable">Name of the destination table on the server.</param>
/// <returns>
/// A fluent <see langword="interface" /> to a <see cref="DataMerge " /> operation.
/// </returns>
public static IDataMerge MergeData(this IDataSession session, string destinationTable)
{
var definition = new DataMergeDefinition();
definition.TargetTable = destinationTable;
return MergeData(session, definition);
}
/// <summary>
/// Creates a <see cref="DataMergeDefinition" /> from the specified <paramref name="importContext" />.
/// </summary>
/// <param name="importContext">The import context.</param>
/// <returns>
/// An instance of <see cref="DataMergeDefinition" />
/// </returns>
/// <exception cref="InvalidOperationException">Could not find matching field definition for data column</exception>
protected virtual DataMergeDefinition CreateMergeDefinition(ImportProcessContext importContext)
{
var importDefinition = importContext.Definition;
var mergeDefinition = new DataMergeDefinition();
mergeDefinition.TargetTable = importDefinition.TargetTable;
mergeDefinition.IncludeInsert = importDefinition.CanInsert;
mergeDefinition.IncludeUpdate = importDefinition.CanUpdate;
// fluent builder
var mergeMapping = new DataMergeMapping(mergeDefinition);
// map included columns
foreach (var fieldMapping in importContext.MappedFields)
{
var fieldDefinition = fieldMapping.Definition;
var nativeType = SqlTypeMapping.NativeType(fieldDefinition.DataType);
mergeMapping
.Column(fieldDefinition.Name)
.Insert(fieldDefinition.CanInsert)
.Update(fieldDefinition.CanUpdate)
.Key(fieldDefinition.IsKey)
.NativeType(nativeType);
}
return(mergeDefinition);
}
/// <summary>
/// Builds the SQL for the temporary table used in the merge operation.
/// </summary>
/// <param name="mergeDefinition">The merge definition.</param>
/// <returns></returns>
public static string BuildTable(DataMergeDefinition mergeDefinition)
{
var builder = new StringBuilder();
builder
.Append("CREATE TABLE ")
.Append(TableIdentifier(mergeDefinition.TemporaryTable))
.AppendLine()
.Append("(")
.AppendLine();
bool hasColumn = false;
foreach (var mergeColumn in mergeDefinition.Columns.Where(c => !c.IsIgnored))
{
bool writeComma = hasColumn;
builder
.AppendLineIf(",", v => writeComma)
.Append(' ', TabSize)
.Append(QuoteIdentifier(mergeColumn.SourceColumn))
.Append(" ")
.Append(mergeColumn.NativeType)
.Append(" NULL");
hasColumn = true;
}
builder
.AppendLine()
.Append(")")
.AppendLine();
return builder.ToString();
}
private IEnumerable <DataMergeOutputRow> Merge(DataMergeDefinition mergeDefinition, DataTable dataTable)
{
_logger.LogDebug("Executing batch merge to: '{table}'", mergeDefinition.TargetTable);
var result = _dataSession
.MergeData(mergeDefinition)
.ExecuteOutput(dataTable)
.ToList();
return(result);
}
public void BuildMergeDataOutputTests()
{
var definition = new DataMergeDefinition();
DataMergeDefinition.AutoMap <UserImport>(definition);
definition.Columns.Should().NotBeNullOrEmpty();
definition.IncludeOutput = true;
definition.TargetTable = "dbo.User";
var column = definition.Columns.Find(c => c.SourceColumn == "EmailAddress");
column.Should().NotBeNull();
column.IsKey = true;
column.CanUpdate = false;
var users = new List <UserImport>
{
new UserImport
{
EmailAddress = "*****@*****.**",
DisplayName = "Test User",
FirstName = "Test",
LastName = "User"
},
new UserImport
{
EmailAddress = "*****@*****.**",
DisplayName = "Blah User",
FirstName = "Blah",
LastName = "User"
},
new UserImport
{
EmailAddress = $"random.{DateTime.Now.Ticks}@email.com",
DisplayName = "Random User",
FirstName = "Random",
LastName = "User"
}
};
var dataTable = users.ToDataTable();
var sql = DataMergeGenerator.BuildMerge(definition, dataTable);
sql.Should().NotBeNullOrEmpty();
Output.WriteLine("MergeStatement:");
Output.WriteLine(sql);
}
private IEnumerable <DataMergeOutputRow> Merge(DataMergeDefinition mergeDefinition, DataTable dataTable, string connectionName)
{
Logger.Debug()
.Message("Executing batch merge to: '{0}'", mergeDefinition.TargetTable)
.Write();
List <DataMergeOutputRow> result;
using (var session = new DataSession(connectionName))
{
result = session
.MergeData(mergeDefinition)
.MergeOutput(dataTable)
.ToList();
}
return(result);
}
private IEnumerable <DataMergeOutputRow> Merge(DataMergeDefinition mergeDefinition, DataTable dataTable, IDataConfiguration dataConfiguration)
{
Logger.Debug()
.Message("Executing batch merge to: '{0}'", mergeDefinition.TargetTable)
.Write();
List <DataMergeOutputRow> result;
using (var session = dataConfiguration.CreateSession())
{
result = session
.MergeData(mergeDefinition)
.ExecuteOutput(dataTable)
.ToList();
}
return(result);
}
public void BuildMergeTests()
{
var definition = new DataMergeDefinition();
DataMergeDefinition.AutoMap <UserImport>(definition);
definition.Columns.Should().NotBeNullOrEmpty();
definition.TargetTable = "dbo.User";
var column = definition.Columns.Find(c => c.SourceColumn == "EmailAddress");
column.Should().NotBeNull();
column.IsKey = true;
column.CanUpdate = false;
var sql = DataMergeGenerator.BuildMerge(definition);
sql.Should().NotBeNullOrEmpty();
Output.WriteLine("MergeStatement:");
Output.WriteLine(sql);
}
private DataMergeDefinition CreateDefinition(BatchJob batchJob)
{
var mergeDefinition = new DataMergeDefinition();
mergeDefinition.TargetTable = batchJob.TargetTable;
mergeDefinition.IncludeInsert = batchJob.CanInsert;
mergeDefinition.IncludeUpdate = batchJob.CanUpdate;
// fluent builder
var mergeMapping = new DataMergeMapping(mergeDefinition);
// map included columns
foreach (var fieldMapping in batchJob.Fields.Where(m => m.Index.HasValue || m.Default.HasValue))
{
mergeMapping
.Column(fieldMapping.Name)
.Insert(fieldMapping.CanInsert)
.Update(fieldMapping.CanUpdate)
.NativeType(fieldMapping.NativeType)
.Key(fieldMapping.IsKey);
}
return(mergeDefinition);
}
/// <summary>
/// Starts a data merge operation with the specified destination table name.
/// </summary>
/// <param name="session">The session to use for the merge.</param>
/// <param name="mergeDefinition">The data merge definition.</param>
/// <returns>
/// A fluent <see langword="interface" /> to a <see cref="DataMerge " /> operation.
/// </returns>
public static IDataMerge MergeData(this IDataSession session, DataMergeDefinition mergeDefinition)
{
var dataMerge = new DataMerge(session, mergeDefinition);
return dataMerge;
}
private IEnumerable<DataMergeOutputRow> Merge(DataMergeDefinition mergeDefinition, DataTable dataTable, string connectionName)
{
Logger.Debug()
.Message("Executing batch merge to: '{0}'", mergeDefinition.TargetTable)
.Write();
List<DataMergeOutputRow> result;
using (var session = new DataSession(connectionName))
{
result = session
.MergeData(mergeDefinition)
.MergeOutput(dataTable)
.ToList();
}
return result;
}
/// <summary>
/// Builds the SQL merge statement for the merge operation.
/// </summary>
/// <param name="mergeDefinition">The merge definition.</param>
/// <returns></returns>
public static string BuildMerge(DataMergeDefinition mergeDefinition)
{
var mergeColumns = mergeDefinition.Columns
.Where(c => !c.IsIgnored)
.ToList();
var builder = new StringBuilder();
if (mergeDefinition.IdentityInsert && mergeDefinition.IncludeInsert)
{
builder
.Append("SET IDENTITY_INSERT ")
.Append(TableIdentifier(mergeDefinition.TargetTable))
.AppendLine(" ON;")
.AppendLine();
}
builder
.Append("MERGE INTO ")
.Append(TableIdentifier(mergeDefinition.TargetTable))
.Append(" AS t")
.AppendLine()
.AppendLine("USING")
.AppendLine("(")
.Append(' ', TabSize)
.AppendLine("SELECT");
bool hasColumn = false;
foreach (var mergeColumn in mergeColumns)
{
bool writeComma = hasColumn;
builder
.AppendLineIf(",", v => writeComma)
.Append(' ', TabSize * 2)
.Append(QuoteIdentifier(mergeColumn.SourceColumn));
hasColumn = true;
}
builder
.AppendLine()
.Append(' ', TabSize)
.Append("FROM ")
.Append(TableIdentifier(mergeDefinition.TemporaryTable))
.AppendLine()
.AppendLine(")")
.AppendLine("AS s")
.AppendLine("ON")
.AppendLine("(");
hasColumn = false;
foreach (var mergeColumn in mergeColumns.Where(c => c.IsKey))
{
bool writeComma = hasColumn;
builder
.AppendLineIf(" AND ", v => writeComma)
.Append(' ', TabSize)
.Append("t.")
.Append(QuoteIdentifier(mergeColumn.TargetColumn))
.Append(" = s.")
.Append(QuoteIdentifier(mergeColumn.SourceColumn));
hasColumn = true;
}
builder
.AppendLine()
.Append(")")
.AppendLine();
// Insert
AppendInsert(mergeDefinition, builder);
// Update
AppendUpdate(mergeDefinition, builder);
// Delete
AppendDelete(mergeDefinition, builder);
// Output
AppendOutput(mergeDefinition, builder);
// merge must end with ;
builder.Append(";");
if (mergeDefinition.IdentityInsert && mergeDefinition.IncludeInsert)
{
builder
.Append("SET IDENTITY_INSERT ")
.Append(TableIdentifier(mergeDefinition.TargetTable))
.AppendLine(" OFF;")
.AppendLine();
}
return builder.ToString();
}
private static void AppendInsert(DataMergeDefinition mergeDefinition, StringBuilder builder)
{
if (!mergeDefinition.IncludeInsert)
return;
var mergeColumns = mergeDefinition.Columns
.Where(c => !c.IsIgnored && c.CanInsert)
.ToList();
builder
.AppendLine("WHEN NOT MATCHED BY TARGET THEN ")
.Append(' ', TabSize)
.AppendLine("INSERT")
.Append(' ', TabSize)
.AppendLine("(");
bool hasColumn = false;
foreach (var mergeColumn in mergeColumns)
{
bool writeComma = hasColumn;
builder
.AppendLineIf(",", v => writeComma)
.Append(' ', TabSize * 2)
.Append(QuoteIdentifier(mergeColumn.TargetColumn));
hasColumn = true;
}
builder.AppendLine();
builder
.Append(' ', TabSize)
.AppendLine(")")
.Append(' ', TabSize)
.AppendLine("VALUES")
.Append(' ', TabSize)
.AppendLine("(");
hasColumn = false;
foreach (var mergeColumn in mergeColumns)
{
bool writeComma = hasColumn;
builder
.AppendLineIf(",", v => writeComma)
.Append(' ', TabSize * 2)
.Append("s.")
.Append(QuoteIdentifier(mergeColumn.SourceColumn));
hasColumn = true;
}
builder.AppendLine();
builder
.Append(' ', TabSize)
.AppendLine(")");
}
private static void AppendUpdate(DataMergeDefinition mergeDefinition, StringBuilder builder)
{
if (!mergeDefinition.IncludeUpdate)
return;
var mergeColumns = mergeDefinition.Columns
.Where(c => !c.IsIgnored && c.CanUpdate)
.ToList();
builder
.AppendLine("WHEN MATCHED THEN ")
.Append(' ', TabSize)
.AppendLine("UPDATE SET");
bool hasColumn = false;
foreach (var mergeColumn in mergeColumns)
{
bool writeComma = hasColumn;
builder
.AppendLineIf(",", v => writeComma)
.Append(' ', TabSize * 2)
.Append("t.")
.Append(QuoteIdentifier(mergeColumn.TargetColumn))
.Append(" = s.")
.Append(QuoteIdentifier(mergeColumn.SourceColumn));
hasColumn = true;
}
builder.AppendLine();
}
private static void AppendDelete(DataMergeDefinition mergeDefinition, StringBuilder builder)
{
if (!mergeDefinition.IncludeDelete)
return;
builder
.AppendLine("WHEN NOT MATCHED BY SOURCE THEN ")
.Append(' ', TabSize)
.AppendLine("DELETE");
}
private DataMergeDefinition CreateDefinition(BatchJob batchJob)
{
var mergeDefinition = new DataMergeDefinition();
mergeDefinition.TargetTable = batchJob.TargetTable;
mergeDefinition.IncludeInsert = batchJob.CanInsert;
mergeDefinition.IncludeUpdate = batchJob.CanUpdate;
// fluent builder
var mergeMapping = new DataMergeMapping(mergeDefinition);
// map included columns
foreach (var fieldMapping in batchJob.SourceMapping.Where(c => c.IsIncluded))
{
mergeMapping
.Column(fieldMapping.Name)
.Insert(fieldMapping.CanInsert)
.Update(fieldMapping.CanUpdate)
.NativeType(fieldMapping.NativeType)
.Key(fieldMapping.IsKey);
}
return mergeDefinition;
}
private static void AppendOutput(DataMergeDefinition mergeDefinition, StringBuilder builder)
{
if (!mergeDefinition.IncludeOutput)
return;
var mergeColumns = mergeDefinition.Columns
.Where(c => !c.IsIgnored)
.ToList();
builder
.AppendLine("OUTPUT")
.Append(' ', TabSize)
.Append("$action as [Action]");
foreach (var mergeColumn in mergeColumns)
{
builder
.AppendLine(",")
.Append(' ', TabSize)
.Append("DELETED.")
.Append(QuoteIdentifier(mergeColumn.SourceColumn))
.Append(" as [")
.Append(OriginalPrefix)
.Append(ParseIdentifier(mergeColumn.SourceColumn))
.Append("],")
.AppendLine();
builder
.Append(' ', TabSize)
.Append("INSERTED.")
.Append(QuoteIdentifier(mergeColumn.SourceColumn))
.Append(" as [")
.Append(CurrentPrefix)
.Append(ParseIdentifier(mergeColumn.SourceColumn))
.Append("]");
}
}
