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