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 AppendUsingSelect(DataMergeDefinition mergeDefinition, List <DataMergeColumn> mergeColumns, StringBuilder builder)
{
builder
.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");
}
/// <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());
}
/// <summary>
/// Creates new instance of <see cref="DataMergeDefinition"/> with properties from type <typeparamref name="TEntity"/> auto mapped.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <returns>A new instance of <see cref="DataMergeDefinition"/>.</returns>
public static DataMergeDefinition Create <TEntity>()
{
var mergeDefinition = new DataMergeDefinition();
AutoMap <TEntity>(mergeDefinition);
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="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));
}
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(")");
}
/// <summary>
/// Validates the specified merge definition.
/// </summary>
/// <param name="mergeDefinition">The merge definition.</param>
/// <param name="isBulk"><c>true</c> if date merge mode is bulk copy; otherwise <c>false</c>.</param>
/// <returns></returns>
/// <exception cref="System.ComponentModel.DataAnnotations.ValidationException">
/// TargetTable is require for the merge definition.
/// or
/// At least one column is required for the merge definition.
/// or
/// At least one column is required to be marked as a key for the merge definition.
/// or
/// SourceColumn is require for column merge definition.
/// or
/// NativeType is require for column merge definition.
/// </exception>
public static bool Validate(DataMergeDefinition mergeDefinition, bool isBulk)
{
if (mergeDefinition.TargetTable.IsNullOrEmpty())
{
throw new ValidationException("TargetTable is require for the merge definition.");
}
// generate temporary name if not set
if (mergeDefinition.TemporaryTable.IsNullOrEmpty())
{
mergeDefinition.TemporaryTable = "#Merge" + DateTime.Now.Ticks;
}
// make sure it starts with #
if (!mergeDefinition.TemporaryTable.StartsWith("#"))
{
mergeDefinition.TemporaryTable = "#" + mergeDefinition.TemporaryTable;
}
// filter ignored columns
var mergeColumns = mergeDefinition.Columns
.Where(c => !c.IsIgnored)
.ToList();
if (mergeColumns.Count == 0)
{
throw new ValidationException("At least one column is required for the merge definition.");
}
if (mergeColumns.Count(c => c.IsKey) == 0)
{
throw new ValidationException("At least one column is required to be marked as a key for the merge definition.");
}
for (int i = 0; i < mergeColumns.Count; i++)
{
var column = mergeColumns[i];
if (column.SourceColumn.IsNullOrEmpty())
{
throw new ValidationException("SourceColumn is require for column index {0} merge definition.".FormatWith(i));
}
// use source if no target
if (column.TargetColumn.IsNullOrEmpty())
{
column.TargetColumn = column.SourceColumn;
}
if (isBulk && column.NativeType.IsNullOrEmpty())
{
throw new ValidationException("NativeType is require for column '{0}' merge definition.".FormatWith(column.SourceColumn));
}
}
return(true);
}
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 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("]");
}
}
/// <summary>
/// Automatics the map the properties of type <typeparamref name="TEntity"/> to the specified <see cref="DataMergeDefinition"/> .
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="mergeDefinition">The merge definition up auto map to.</param>
public static void AutoMap <TEntity>(DataMergeDefinition mergeDefinition)
{
var entityType = typeof(TEntity);
var properties = TypeDescriptor.GetProperties(entityType);
var tableAttribute = Attribute.GetCustomAttribute(entityType, typeof(TableAttribute)) as TableAttribute;
if (tableAttribute != null)
{
string targetTable = tableAttribute.Name;
if (!string.IsNullOrEmpty(tableAttribute.Schema))
{
targetTable = tableAttribute.Schema + "." + targetTable;
}
mergeDefinition.TargetTable = targetTable;
}
if (string.IsNullOrEmpty(mergeDefinition.TargetTable))
{
mergeDefinition.TargetTable = entityType.Name;
}
foreach (PropertyDescriptor p in properties)
{
string sourceColumn = p.Name;
string targetColumn = sourceColumn;
string nativeType = null;
var columnAttribute = p.Attributes
.OfType <ColumnAttribute>()
.FirstOrDefault();
if (columnAttribute != null)
{
if (columnAttribute.Name.HasValue())
{
targetColumn = columnAttribute.Name;
}
if (columnAttribute.TypeName.HasValue())
{
nativeType = columnAttribute.TypeName;
}
}
var mergeColumn = mergeDefinition.Columns.FirstOrAdd(
m => m.SourceColumn == sourceColumn,
() => new DataMergeColumn {
SourceColumn = sourceColumn
});
mergeColumn.TargetColumn = targetColumn;
mergeColumn.NativeType = nativeType;
var keyAttribute = p.Attributes
.OfType <KeyAttribute>()
.FirstOrDefault();
if (keyAttribute != null)
{
mergeColumn.IsKey = true;
mergeColumn.CanUpdate = false;
}
var ignoreAttribute = p.Attributes
.OfType <NotMappedAttribute>()
.FirstOrDefault();
if (ignoreAttribute != null)
{
mergeColumn.IsIgnored = true;
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="DataMerge"/> class.
/// </summary>
/// <param name="dataSession">The data session.</param>
/// <param name="mergeDefinition">The data merge definition.</param>
public DataMerge(IDataSession dataSession, DataMergeDefinition mergeDefinition)
{
_dataSession = dataSession;
_mergeDefinition = mergeDefinition;
}
/// <summary>
/// Automatically maps all properties in <typeparamref name="TEntity"/> as columns.
/// </summary>
/// <returns></returns>
public IDataMergeMapping <TEntity> AutoMap()
{
DataMergeDefinition.AutoMap <TEntity>(MergeDefinition);
return(this);
}
/// <summary>
/// Initializes a new instance of the <see cref="DataMergeMapping{TEntity}"/> class.
/// </summary>
/// <param name="mergeDefinition">The data merge definition.</param>
public DataMergeMapping(DataMergeDefinition mergeDefinition)
: base(mergeDefinition)
{
}
/// <summary>
/// Builds the SQL merge statement for the merge operation.
/// </summary>
/// <param name="mergeDefinition">The merge definition.</param>
/// <returns>The merge sql statement</returns>
public static string BuildMerge(DataMergeDefinition mergeDefinition)
{
return(BuildMerge(mergeDefinition, null));
}
private static void AppendUsingData(DataMergeDefinition mergeDefinition, List <DataMergeColumn> mergeColumns, DataTable table, StringBuilder builder)
{
builder
.AppendLine("USING")
.AppendLine("(")
.Append(' ', TabSize)
.AppendLine("VALUES");
bool wroteRow = false;
foreach (DataRow row in table.Rows)
{
bool wrote = false;
builder
.AppendLineIf(", ", s => wroteRow)
.Append(' ', TabSize)
.Append("(");
for (int i = 0; i < row.ItemArray.Length; i++)
{
var column = table.Columns[i];
var isFound = mergeColumns.Any(c => c.SourceColumn == column.ColumnName);
if (!isFound)
{
continue;
}
builder.AppendIf(", ", v => wrote);
object value = row[i];
string stringValue = GetValue(value);
if ((value != null && value != DBNull.Value) && NeedQuote(row.Table.Columns[i].DataType))
{
builder.AppendFormat("'{0}'", stringValue.Replace("'", "''"));
}
else
{
builder.Append(stringValue);
}
wrote = true;
}
builder.Append(")");
wroteRow = true;
}
builder
.AppendLine()
.AppendLine(")")
.AppendLine("AS s")
.AppendLine("(")
.Append(' ', TabSize);
bool wroteColumn = false;
for (int i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
var isFound = mergeColumns.Any(c => c.SourceColumn == column.ColumnName);
if (!isFound)
{
continue;
}
if (wroteColumn)
{
builder.Append(", ");
}
builder.Append(QuoteIdentifier(column.ColumnName));
wroteColumn = true;
}
builder
.AppendLine()
.AppendLine(")");
}
/// <summary>
/// Builds the SQL merge statement for the merge operation.
/// </summary>
/// <param name="mergeDefinition">The merge definition.</param>
/// <param name="table">The data table to generate merge statement with.</param>
/// <returns>The merge sql statement</returns>
public static string BuildMerge(DataMergeDefinition mergeDefinition, DataTable table)
{
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();
if (table == null)
{
AppendUsingSelect(mergeDefinition, mergeColumns, builder);
}
else
{
AppendUsingData(mergeDefinition, mergeColumns, table, builder);
}
AppendJoin(mergeColumns, builder);
// 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());
}
/// <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);
}
/// <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());
}
