29168809467

Committed 11 Jul 2026 09:26PM UTC coverage: 65.067% (+0.02%) from 65.051%

Build # 29168809467

Build Type

push

github

Committed by

pwelter34

Commit Message

update packages

Coverage Stats

1745 of 3480 branches covered (50.14%)

Branch coverage included in aggregate %.

5549 of 7730 relevant lines covered (71.79%)

302.92 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

89.31

/src/FluentCommand.SqlServer/Merge/DataMergeGenerator.cs

using System.Data;
using System.Text;
using System.Text.Json;

using FluentCommand.Extensions;
using FluentCommand.Internal;

namespace FluentCommand.Merge;

/// <summary>
/// Provides static methods to generate SQL statements for data merge operations, including table creation and SQL Server MERGE statements.
/// </summary>
public static class DataMergeGenerator
{
    /// <summary>
    /// The prefix used for original (pre-merge) column values in output.
    /// </summary>
    public const string OriginalPrefix = "Original";
    /// <summary>
    /// The prefix used for current (post-merge) column values in output.
    /// </summary>
    public const string CurrentPrefix = "Current";
    /// <summary>
    /// The number of spaces to use for indentation in generated SQL.
    /// </summary>
    public const int TabSize = 4;

    /// <summary>
    /// Formats a table or schema-qualified table name as a valid SQL identifier, quoting each part as needed.
    /// </summary>
    /// <param name="name">The table name, optionally schema-qualified (e.g., <c>dbo.TableName</c>).</param>
    /// <returns>The quoted table identifier (e.g., <c>[dbo].[TableName]</c>).</returns>
    public static string TableIdentifier(string name)
    {
        var parts = name.Split('.');
        for (int i = 0; i < parts.Length; i++)
            parts[i] = QuoteIdentifier(parts[i]);

        return string.Join(".", parts);
    }

    /// <summary>
    /// Quotes a SQL identifier with square brackets, escaping any closing brackets.
    /// </summary>
    /// <param name="name">The identifier to quote.</param>
    /// <returns>The quoted identifier.</returns>
    public static string QuoteIdentifier(string name)
    {
        if (name.StartsWith("[") && name.EndsWith("]"))
            return name;

        return "[" + name.Replace("]", "]]") + "]";
    }

    /// <summary>
    /// Removes square bracket quoting from a SQL identifier, if present.
    /// </summary>
    /// <param name="name">The quoted identifier.</param>
    /// <returns>The unquoted identifier.</returns>
    public static string ParseIdentifier(string name)
    {
        if (name.StartsWith("[") && name.EndsWith("]"))
            return name.Substring(1, name.Length - 2);

        return name;
    }

    /// <summary>
    /// Builds the SQL statement for creating the temporary table used in the merge operation.
    /// </summary>
    /// <param name="mergeDefinition">The <see cref="DataMergeDefinition"/> describing the temporary table schema.</param>
    /// <returns>The SQL statement to create the temporary table.</returns>
    public static string BuildTable(DataMergeDefinition mergeDefinition)
    {
        var builder = StringBuilderCache.Acquire();
        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 StringBuilderCache.ToString(builder);
    }

    /// <summary>
    /// Builds the SQL Server MERGE statement for the specified merge definition.
    /// </summary>
    /// <param name="mergeDefinition">The <see cref="DataMergeDefinition"/> describing the merge operation.</param>
    /// <returns>The SQL MERGE statement.</returns>
    public static string BuildMerge(DataMergeDefinition mergeDefinition)
    {
        return BuildMerge(mergeDefinition, null);
    }

    /// <summary>
    /// Builds the SQL Server MERGE statement for the specified merge definition, optionally using data from an <see cref="IDataReader"/>.
    /// </summary>
    /// <param name="mergeDefinition">The <see cref="DataMergeDefinition"/> describing the merge operation.</param>
    /// <param name="reader">An optional <see cref="IDataReader"/> to provide source data for the merge statement.</param>
    /// <returns>The SQL MERGE statement.</returns>
    public static string BuildMerge(DataMergeDefinition mergeDefinition, IDataReader? reader)
    {
        var mergeColumns = mergeDefinition.Columns
            .Where(c => !c.IsIgnored && (c.IsKey || c.CanInsert || c.CanUpdate))
            .ToList();

        var builder = StringBuilderCache.Acquire();

        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 (reader == null)
            AppendUsingSelect(mergeDefinition, mergeColumns, builder);
        else
            AppendUsingData(mergeDefinition, mergeColumns, reader, 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 StringBuilderCache.ToString(builder);
    }

    /// <summary>
    /// Appends a VALUES-based USING clause to the merge statement using data from an <see cref="IDataReader"/>.
    /// </summary>
    /// <param name="mergeDefinition">The merge definition.</param>
    /// <param name="mergeColumns">The columns to include in the merge.</param>
    /// <param name="reader">The data reader providing source data.</param>
    /// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
    private static void AppendUsingData(DataMergeDefinition mergeDefinition, List<DataMergeColumn> mergeColumns, IDataReader reader, StringBuilder builder)
    {
        builder
            .AppendLine("USING")
            .AppendLine("(")
            .Append(' ', TabSize)
            .AppendLine("VALUES");

        var fields = new HashSet<string>();

        bool wroteRow = false;
        while (reader.Read())
        {
            bool wrote = false;

            builder
                .AppendLineIf(", ", s => wroteRow)
                .Append(' ', TabSize)
                .Append('(');

            for (int i = 0; i < reader.FieldCount; i++)
            {
                var fieldName = reader.GetName(i);

                var isFound = mergeColumns.Any(c => c.SourceColumn == fieldName);
                if (!isFound)
                    continue;

                fields.Add(fieldName);

                builder.AppendIf(", ", v => wrote);

                var value = reader.GetValue(i);
                var stringValue = GetValue(value);
                var fieldType = reader.GetFieldType(i);

                // ReSharper disable once ConditionIsAlwaysTrueOrFalse
                if (value != null && value != DBNull.Value && NeedQuote(fieldType))
                    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;
        foreach (var field in fields)
        {
            if (wroteColumn)
                builder.Append(", ");

            builder.Append(QuoteIdentifier(field));
            wroteColumn = true;
        }

        builder
            .AppendLine()
            .AppendLine(")");
    }

    /// <summary>
    /// Appends a SELECT-based USING clause to the merge statement using the temporary table.
    /// </summary>
    /// <param name="mergeDefinition">The merge definition.</param>
    /// <param name="mergeColumns">The columns to include in the merge.</param>
    /// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
    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>
    /// Appends the JOIN condition for the MERGE statement based on key columns.
    /// </summary>
    /// <param name="mergeColumns">The columns to use for the join condition.</param>
    /// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
    private static void AppendJoin(List<DataMergeColumn> mergeColumns, StringBuilder builder)
    {
        bool hasColumn;
        builder
            .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();
    }

    /// <summary>
    /// Appends the OUTPUT clause to the MERGE statement if output is enabled in the merge definition.
    /// </summary>
    /// <param name="mergeDefinition">The merge definition.</param>
    /// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
    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>
    /// Appends the DELETE clause to the MERGE statement if delete is enabled in the merge definition.
    /// </summary>
    /// <param name="mergeDefinition">The merge definition.</param>
    /// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
    private static void AppendDelete(DataMergeDefinition mergeDefinition, StringBuilder builder)
    {
        if (!mergeDefinition.IncludeDelete)
            return;

        builder
            .AppendLine("WHEN NOT MATCHED BY SOURCE THEN ")
            .Append(' ', TabSize)
            .AppendLine("DELETE");
    }

    /// <summary>
    /// Appends the UPDATE clause to the MERGE statement if update is enabled in the merge definition.
    /// </summary>
    /// <param name="mergeDefinition">The merge definition.</param>
    /// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
    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();
    }

    /// <summary>
    /// Appends the INSERT clause to the MERGE statement if insert is enabled in the merge definition.
    /// </summary>
    /// <param name="mergeDefinition">The merge definition.</param>
    /// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
    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>
    /// Determines whether the specified type requires quoting in SQL statements.
    /// </summary>
    /// <param name="type">The type to check.</param>
    /// <returns><c>true</c> if the type requires quoting; otherwise, <c>false</c>.</returns>
    private static bool NeedQuote(Type type)
    {
        var underType = type.GetUnderlyingType();

        if (underType == typeof(string))
            return true;
        if (underType == typeof(TimeSpan))
            return true;
        if (underType == typeof(DateTime))
            return true;
        if (underType == typeof(DateTimeOffset))
            return true;
        if (underType == typeof(Guid))
            return true;
        if (underType == typeof(JsonElement))
            return true;
#if NET6_0_OR_GREATER
        if (underType == typeof(DateOnly))
            return true;
        if (underType == typeof(TimeOnly))
            return true;
#endif

        return false;
    }

    /// <summary>
    /// Converts a value to its SQL literal representation for use in generated statements.
    /// </summary>
    /// <param name="value">The value to convert.</param>
    /// <returns>The SQL literal as a string.</returns>
    private static string GetValue(object? value)
    {
        if (value == null || value == DBNull.Value)
            return "NULL";

        return value switch
        {
            string stringValue => stringValue,
            DateTime dateTimeValue => dateTimeValue.ToString("yyyy-MM-dd HH:mm:ss.fff"),
            DateTimeOffset dateTimeOffset => dateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.ffffffzzz"),
            byte[] byteArray => ToHex(byteArray),
            bool boolValue => boolValue ? "1" : "0",
            JsonElement jsonElement => GetJsonValue(jsonElement) ?? "NULL",
#if NET6_0_OR_GREATER
            DateOnly dateValue => dateValue.ToString("yyyy-MM-dd"),
            TimeOnly timeValue => timeValue.ToString("hh:mm:ss.ffffff"),
#endif
            _ => Convert.ToString(value) ?? string.Empty
        };
    }

    /// <summary>
    /// Converts a JSON element to its string representation for use in SQL statements.
    /// </summary>
    /// <param name="jsonElement">The JSON element to convert.</param>
    /// <returns>The string representation of the JSON element, or <c>null</c> if undefined.</returns>
    private static string? GetJsonValue(JsonElement jsonElement)
    {
        return jsonElement.ValueKind != JsonValueKind.Undefined
            ? jsonElement.GetRawText()
            : null;
    }

    /// <summary>
    /// Converts a byte array to its hexadecimal string representation for use in SQL statements.
    /// </summary>
    /// <param name="bytes">The byte array to convert.</param>
    /// <returns>The hexadecimal string representation.</returns>
    private static string ToHex(byte[] bytes)
    {
#if NET5_0_OR_GREATER
        return Convert.ToHexString(bytes);
#else
        var s = StringBuilderCache.Acquire();
        s.Append("0x");

        foreach (var b in bytes)
            s.Append(b.ToString("x2").ToUpperInvariant());

        return StringBuilderCache.ToString(s);
#endif
    }
}

1	using System.Data;
2	using System.Text;
3	using System.Text.Json;
4
5	using FluentCommand.Extensions;
6	using FluentCommand.Internal;
7
8	namespace FluentCommand.Merge;
9
10	/// <summary>
11	/// Provides static methods to generate SQL statements for data merge operations, including table creation and SQL Server MERGE statements.
12	/// </summary>
13	public static class DataMergeGenerator
14	{
15	/// <summary>
16	/// The prefix used for original (pre-merge) column values in output.
17	/// </summary>
18	public const string OriginalPrefix = "Original";
19	/// <summary>
20	/// The prefix used for current (post-merge) column values in output.
21	/// </summary>
22	public const string CurrentPrefix = "Current";
23	/// <summary>
24	/// The number of spaces to use for indentation in generated SQL.
25	/// </summary>
26	public const int TabSize = 4;
27
28	/// <summary>
29	/// Formats a table or schema-qualified table name as a valid SQL identifier, quoting each part as needed.
30	/// </summary>
31	/// <param name="name">The table name, optionally schema-qualified (e.g., <c>dbo.TableName</c>).</param>
32	/// <returns>The quoted table identifier (e.g., <c>[dbo].[TableName]</c>).</returns>
33	public static string TableIdentifier(string name)
34	{
35	var parts = name.Split('.');	23✔
36	for (int i = 0; i < parts.Length; i++)	120✔
37	parts[i] = QuoteIdentifier(parts[i]);	37✔
38
39	return string.Join(".", parts);	23✔
40	}
41
42	/// <summary>
43	/// Quotes a SQL identifier with square brackets, escaping any closing brackets.
44	/// </summary>
45	/// <param name="name">The identifier to quote.</param>
46	/// <returns>The quoted identifier.</returns>
47	public static string QuoteIdentifier(string name)
48	{
49	if (name.StartsWith("[") && name.EndsWith("]"))	695✔
50	return name;	1✔
51
52	return "[" + name.Replace("]", "]]") + "]";	694✔
53	}
54
55	/// <summary>
56	/// Removes square bracket quoting from a SQL identifier, if present.
57	/// </summary>
58	/// <param name="name">The quoted identifier.</param>
59	/// <returns>The unquoted identifier.</returns>
60	public static string ParseIdentifier(string name)
61	{
62	if (name.StartsWith("[") && name.EndsWith("]"))	42✔
63	return name.Substring(1, name.Length - 2);	3✔
64
65	return name;	39✔
66	}
67
68	/// <summary>
69	/// Builds the SQL statement for creating the temporary table used in the merge operation.
70	/// </summary>
71	/// <param name="mergeDefinition">The <see cref="DataMergeDefinition"/> describing the temporary table schema.</param>
72	/// <returns>The SQL statement to create the temporary table.</returns>
73	public static string BuildTable(DataMergeDefinition mergeDefinition)
74	{
75	var builder = StringBuilderCache.Acquire();	4✔
76	builder	4✔
77	.Append("CREATE TABLE ")	4✔
78	.Append(TableIdentifier(mergeDefinition.TemporaryTable))	4✔
79	.AppendLine()	4✔
80	.Append('(')	4✔
81	.AppendLine();	4✔
82
83	bool hasColumn = false;	4✔
84	foreach (var mergeColumn in mergeDefinition.Columns.Where(c => !c.IsIgnored))	88✔
85	{
86	bool writeComma = hasColumn;	40✔
87
88	builder	40✔
89	.AppendLineIf(",", v => writeComma)	40✔
90	.Append(' ', TabSize)	40✔
91	.Append(QuoteIdentifier(mergeColumn.SourceColumn))	40✔
92	.Append(' ')	40✔
93	.Append(mergeColumn.NativeType)	40✔
94	.Append(" NULL");	40✔
95
96	hasColumn = true;	40✔
97	}
98
99	builder	4✔
100	.AppendLine()	4✔
101	.Append(')')	4✔
102	.AppendLine();	4✔
103
104	return StringBuilderCache.ToString(builder);	4✔
105	}
106
107	/// <summary>
108	/// Builds the SQL Server MERGE statement for the specified merge definition.
109	/// </summary>
110	/// <param name="mergeDefinition">The <see cref="DataMergeDefinition"/> describing the merge operation.</param>
111	/// <returns>The SQL MERGE statement.</returns>
112	public static string BuildMerge(DataMergeDefinition mergeDefinition)
113	{
114	return BuildMerge(mergeDefinition, null);	5✔
115	}
116
117	/// <summary>
118	/// Builds the SQL Server MERGE statement for the specified merge definition, optionally using data from an <see cref="IDataReader"/>.
119	/// </summary>
120	/// <param name="mergeDefinition">The <see cref="DataMergeDefinition"/> describing the merge operation.</param>
121	/// <param name="reader">An optional <see cref="IDataReader"/> to provide source data for the merge statement.</param>
122	/// <returns>The SQL MERGE statement.</returns>
123	public static string BuildMerge(DataMergeDefinition mergeDefinition, IDataReader? reader)
124	{
125	var mergeColumns = mergeDefinition.Columns	14✔
126	.Where(c => !c.IsIgnored && (c.IsKey \|\| c.CanInsert \|\| c.CanUpdate))	14✔
127	.ToList();	14✔
128
129	var builder = StringBuilderCache.Acquire();	14✔
130
131	if (mergeDefinition.IdentityInsert && mergeDefinition.IncludeInsert)	14!
132	{
133	builder	×
134	.Append("SET IDENTITY_INSERT ")	×
135	.Append(TableIdentifier(mergeDefinition.TargetTable))	×
136	.AppendLine(" ON;")	×
137	.AppendLine();	×
138	}
139
140	builder	14✔
141	.Append("MERGE INTO ")	14✔
142	.Append(TableIdentifier(mergeDefinition.TargetTable))	14✔
143	.Append(" AS t")	14✔
144	.AppendLine();	14✔
145
146	if (reader == null)	14✔
147	AppendUsingSelect(mergeDefinition, mergeColumns, builder);	5✔
148	else
149	AppendUsingData(mergeDefinition, mergeColumns, reader, builder);	9✔
150
151	AppendJoin(mergeColumns, builder);	14✔
152
153	// Insert
154	AppendInsert(mergeDefinition, builder);	14✔
155
156	// Update
157	AppendUpdate(mergeDefinition, builder);	14✔
158
159	// Delete
160	AppendDelete(mergeDefinition, builder);	14✔
161
162	// Output
163	AppendOutput(mergeDefinition, builder);	14✔
164
165	// merge must end with ;
166	builder.Append(';');	14✔
167
168	if (mergeDefinition.IdentityInsert && mergeDefinition.IncludeInsert)	14!
169	{
170	builder	×
171	.Append("SET IDENTITY_INSERT ")	×
172	.Append(TableIdentifier(mergeDefinition.TargetTable))	×
173	.AppendLine(" OFF;")	×
174	.AppendLine();	×
175	}
176
177	return StringBuilderCache.ToString(builder);	14✔
178	}
179
180	/// <summary>
181	/// Appends a VALUES-based USING clause to the merge statement using data from an <see cref="IDataReader"/>.
182	/// </summary>
183	/// <param name="mergeDefinition">The merge definition.</param>
184	/// <param name="mergeColumns">The columns to include in the merge.</param>
185	/// <param name="reader">The data reader providing source data.</param>
186	/// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
187	private static void AppendUsingData(DataMergeDefinition mergeDefinition, List<DataMergeColumn> mergeColumns, IDataReader reader, StringBuilder builder)
188	{
189	builder	9✔
190	.AppendLine("USING")	9✔
191	.AppendLine("(")	9✔
192	.Append(' ', TabSize)	9✔
193	.AppendLine("VALUES");	9✔
194
195	var fields = new HashSet<string>();	9✔
196
197	bool wroteRow = false;	9✔
198	while (reader.Read())	420✔
199	{
200	bool wrote = false;	411✔
201
202	builder	411✔
203	.AppendLineIf(", ", s => wroteRow)	411✔
204	.Append(' ', TabSize)	411✔
205	.Append('(');	411✔
206
207	for (int i = 0; i < reader.FieldCount; i++)	5,820✔
208	{
209	var fieldName = reader.GetName(i);	2,499✔
210
211	var isFound = mergeColumns.Any(c => c.SourceColumn == fieldName);	2,499✔
212	if (!isFound)	2,499!
213	continue;
214
215	fields.Add(fieldName);	2,499✔
216
217	builder.AppendIf(", ", v => wrote);	2,499✔
218
219	var value = reader.GetValue(i);	2,499✔
220	var stringValue = GetValue(value);	2,499✔
221	var fieldType = reader.GetFieldType(i);	2,499✔
222
223	// ReSharper disable once ConditionIsAlwaysTrueOrFalse
224	if (value != null && value != DBNull.Value && NeedQuote(fieldType))	2,499✔
225	builder.AppendFormat("'{0}'", stringValue.Replace("'", "''"));	1,656✔
226	else
227	builder.Append(stringValue);	843✔
228
229	wrote = true;	2,499✔
230	}
231	builder.Append(')');	411✔
232
233	wroteRow = true;	411✔
234	}
235
236	builder	9✔
237	.AppendLine()	9✔
238	.AppendLine(")")	9✔
239	.AppendLine("AS s")	9✔
240	.AppendLine("(")	9✔
241	.Append(' ', TabSize);	9✔
242
243	bool wroteColumn = false;	9✔
244	foreach (var field in fields)	156✔
245	{
246	if (wroteColumn)	69✔
247	builder.Append(", ");	60✔
248
249	builder.Append(QuoteIdentifier(field));	69✔
250	wroteColumn = true;	69✔
251	}
252
253	builder	9✔
254	.AppendLine()	9✔
255	.AppendLine(")");	9✔
256	}	9✔
257
258	/// <summary>
259	/// Appends a SELECT-based USING clause to the merge statement using the temporary table.
260	/// </summary>
261	/// <param name="mergeDefinition">The merge definition.</param>
262	/// <param name="mergeColumns">The columns to include in the merge.</param>
263	/// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
264	private static void AppendUsingSelect(DataMergeDefinition mergeDefinition, List<DataMergeColumn> mergeColumns, StringBuilder builder)
265	{
266	builder	5✔
267	.AppendLine("USING")	5✔
268	.AppendLine("(")	5✔
269	.Append(' ', TabSize)	5✔
270	.AppendLine("SELECT");	5✔
271
272	bool hasColumn = false;	5✔
273	foreach (var mergeColumn in mergeColumns)	102✔
274	{
275	bool writeComma = hasColumn;	46✔
276
277	builder	46✔
278	.AppendLineIf(",", v => writeComma)	46✔
279	.Append(' ', TabSize * 2)	46✔
280	.Append(QuoteIdentifier(mergeColumn.SourceColumn));	46✔
281
282	hasColumn = true;	46✔
283	}
284
285	builder	5✔
286	.AppendLine()	5✔
287	.Append(' ', TabSize)	5✔
288	.Append("FROM ")	5✔
289	.Append(TableIdentifier(mergeDefinition.TemporaryTable))	5✔
290	.AppendLine()	5✔
291	.AppendLine(")")	5✔
292	.AppendLine("AS s");	5✔
293	}	5✔
294
295	/// <summary>
296	/// Appends the JOIN condition for the MERGE statement based on key columns.
297	/// </summary>
298	/// <param name="mergeColumns">The columns to use for the join condition.</param>
299	/// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
300	private static void AppendJoin(List<DataMergeColumn> mergeColumns, StringBuilder builder)
301	{
302	bool hasColumn;
303	builder	14✔
304	.AppendLine("ON")	14✔
305	.AppendLine("(");	14✔
306
307	hasColumn = false;	14✔
308	foreach (var mergeColumn in mergeColumns.Where(c => c.IsKey))	56✔
309	{
310	bool writeComma = hasColumn;	14✔
311	builder	14✔
312	.AppendLineIf(" AND ", v => writeComma)	14✔
313	.Append(' ', TabSize)	14✔
314	.Append("t.")	14✔
315	.Append(QuoteIdentifier(mergeColumn.TargetColumn))	14✔
316	.Append(" = s.")	14✔
317	.Append(QuoteIdentifier(mergeColumn.SourceColumn));	14✔
318
319	hasColumn = true;	14✔
320	}
321
322	builder	14✔
323	.AppendLine()	14✔
324	.Append(')')	14✔
325	.AppendLine();	14✔
326	}	14✔
327
328	/// <summary>
329	/// Appends the OUTPUT clause to the MERGE statement if output is enabled in the merge definition.
330	/// </summary>
331	/// <param name="mergeDefinition">The merge definition.</param>
332	/// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
333	private static void AppendOutput(DataMergeDefinition mergeDefinition, StringBuilder builder)
334	{
335	if (!mergeDefinition.IncludeOutput)	14✔
336	return;	11✔
337
338	var mergeColumns = mergeDefinition.Columns	3✔
339	.Where(c => !c.IsIgnored)	3✔
340	.ToList();	3✔
341
342	builder	3✔
343	.AppendLine("OUTPUT")	3✔
344	.Append(' ', TabSize)	3✔
345	.Append("$action as [Action]");	3✔
346
347	foreach (var mergeColumn in mergeColumns)	42✔
348	{
349	builder	18✔
350	.AppendLine(",")	18✔
351	.Append(' ', TabSize)	18✔
352	.Append("DELETED.")	18✔
353	.Append(QuoteIdentifier(mergeColumn.SourceColumn))	18✔
354	.Append(" as [")	18✔
355	.Append(OriginalPrefix)	18✔
356	.Append(ParseIdentifier(mergeColumn.SourceColumn))	18✔
357	.Append("],")	18✔
358	.AppendLine();	18✔
359
360	builder	18✔
361	.Append(' ', TabSize)	18✔
362	.Append("INSERTED.")	18✔
363	.Append(QuoteIdentifier(mergeColumn.SourceColumn))	18✔
364	.Append(" as [")	18✔
365	.Append(CurrentPrefix)	18✔
366	.Append(ParseIdentifier(mergeColumn.SourceColumn))	18✔
367	.Append(']');	18✔
368	}
369
370	}	3✔
371
372	/// <summary>
373	/// Appends the DELETE clause to the MERGE statement if delete is enabled in the merge definition.
374	/// </summary>
375	/// <param name="mergeDefinition">The merge definition.</param>
376	/// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
377	private static void AppendDelete(DataMergeDefinition mergeDefinition, StringBuilder builder)
378	{
379	if (!mergeDefinition.IncludeDelete)	14!
380	return;	14✔
381
382	builder	×
383	.AppendLine("WHEN NOT MATCHED BY SOURCE THEN ")	×
384	.Append(' ', TabSize)	×
385	.AppendLine("DELETE");	×
386	}	×
387
388	/// <summary>
389	/// Appends the UPDATE clause to the MERGE statement if update is enabled in the merge definition.
390	/// </summary>
391	/// <param name="mergeDefinition">The merge definition.</param>
392	/// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
393	private static void AppendUpdate(DataMergeDefinition mergeDefinition, StringBuilder builder)
394	{
395	if (!mergeDefinition.IncludeUpdate)	14!
396	return;	×
397
398	var mergeColumns = mergeDefinition.Columns	14✔
399	.Where(c => !c.IsIgnored && c.CanUpdate)	14✔
400	.ToList();	14✔
401
402	builder	14✔
403	.AppendLine("WHEN MATCHED THEN ")	14✔
404	.Append(' ', TabSize)	14✔
405	.AppendLine("UPDATE SET");	14✔
406
407	bool hasColumn = false;	14✔
408	foreach (var mergeColumn in mergeColumns)	230✔
409	{
410	bool writeComma = hasColumn;	101✔
411	builder	101✔
412	.AppendLineIf(",", v => writeComma)	101✔
413	.Append(' ', TabSize * 2)	101✔
414	.Append("t.")	101✔
415	.Append(QuoteIdentifier(mergeColumn.TargetColumn))	101✔
416	.Append(" = s.")	101✔
417	.Append(QuoteIdentifier(mergeColumn.SourceColumn));	101✔
418
419	hasColumn = true;	101✔
420	}
421	builder.AppendLine();	14✔
422	}	14✔
423
424	/// <summary>
425	/// Appends the INSERT clause to the MERGE statement if insert is enabled in the merge definition.
426	/// </summary>
427	/// <param name="mergeDefinition">The merge definition.</param>
428	/// <param name="builder">The <see cref="StringBuilder"/> to append SQL to.</param>
429	private static void AppendInsert(DataMergeDefinition mergeDefinition, StringBuilder builder)
430	{
431	if (!mergeDefinition.IncludeInsert)	14!
432	return;	×
433
434	var mergeColumns = mergeDefinition.Columns	14✔
435	.Where(c => !c.IsIgnored && c.CanInsert)	14✔
436	.ToList();	14✔
437
438	builder	14✔
439	.AppendLine("WHEN NOT MATCHED BY TARGET THEN ")	14✔
440	.Append(' ', TabSize)	14✔
441	.AppendLine("INSERT")	14✔
442	.Append(' ', TabSize)	14✔
443	.AppendLine("(");	14✔
444
445	bool hasColumn = false;	14✔
446	foreach (var mergeColumn in mergeColumns)	258✔
447	{
448	bool writeComma = hasColumn;	115✔
449	builder	115✔
450	.AppendLineIf(",", v => writeComma)	115✔
451	.Append(' ', TabSize * 2)	115✔
452	.Append(QuoteIdentifier(mergeColumn.TargetColumn));	115✔
453
454	hasColumn = true;	115✔
455	}
456	builder.AppendLine();	14✔
457
458	builder	14✔
459	.Append(' ', TabSize)	14✔
460	.AppendLine(")")	14✔
461	.Append(' ', TabSize)	14✔
462	.AppendLine("VALUES")	14✔
463	.Append(' ', TabSize)	14✔
464	.AppendLine("(");	14✔
465
466	hasColumn = false;	14✔
467	foreach (var mergeColumn in mergeColumns)	258✔
468	{
469	bool writeComma = hasColumn;	115✔
470	builder	115✔
471	.AppendLineIf(",", v => writeComma)	115✔
472	.Append(' ', TabSize * 2)	115✔
473	.Append("s.")	115✔
474	.Append(QuoteIdentifier(mergeColumn.SourceColumn));	115✔
475
476	hasColumn = true;	115✔
477	}
478	builder.AppendLine();	14✔
479
480	builder	14✔
481	.Append(' ', TabSize)	14✔
482	.AppendLine(")");	14✔
483	}	14✔
484
485	/// <summary>
486	/// Determines whether the specified type requires quoting in SQL statements.
487	/// </summary>
488	/// <param name="type">The type to check.</param>
489	/// <returns><c>true</c> if the type requires quoting; otherwise, <c>false</c>.</returns>
490	private static bool NeedQuote(Type type)
491	{
492	var underType = type.GetUnderlyingType();	1,677✔
493
494	if (underType == typeof(string))	1,677✔
495	return true;	1,635✔
496	if (underType == typeof(TimeSpan))	42✔
497	return true;	3✔
498	if (underType == typeof(DateTime))	39✔
499	return true;	3✔
500	if (underType == typeof(DateTimeOffset))	36✔
501	return true;	3✔
502	if (underType == typeof(Guid))	33✔
503	return true;	6✔
504	if (underType == typeof(JsonElement))	27!
505	return true;	×
506	#if NET6_0_OR_GREATER
507	if (underType == typeof(DateOnly))	27✔
508	return true;	3✔
509	if (underType == typeof(TimeOnly))	24✔
510	return true;	3✔
511	#endif
512
513	return false;	21✔
514	}
515
516	/// <summary>
517	/// Converts a value to its SQL literal representation for use in generated statements.
518	/// </summary>
519	/// <param name="value">The value to convert.</param>
520	/// <returns>The SQL literal as a string.</returns>
521	private static string GetValue(object? value)
522	{
523	if (value == null \|\| value == DBNull.Value)	2,499✔
524	return "NULL";	822✔
525
526	return value switch	1,677!
527	{	1,677✔
528	string stringValue => stringValue,	1,635✔
529	DateTime dateTimeValue => dateTimeValue.ToString("yyyy-MM-dd HH:mm:ss.fff"),	3✔
530	DateTimeOffset dateTimeOffset => dateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.ffffffzzz"),	3✔
531	byte[] byteArray => ToHex(byteArray),	×
532	bool boolValue => boolValue ? "1" : "0",	3✔
533	JsonElement jsonElement => GetJsonValue(jsonElement) ?? "NULL",	×
534	#if NET6_0_OR_GREATER	1,677✔
535	DateOnly dateValue => dateValue.ToString("yyyy-MM-dd"),	3✔
536	TimeOnly timeValue => timeValue.ToString("hh:mm:ss.ffffff"),	3✔
537	#endif	1,677✔
538	_ => Convert.ToString(value) ?? string.Empty	27!
539	};	1,677✔
540	}
541
542	/// <summary>
543	/// Converts a JSON element to its string representation for use in SQL statements.
544	/// </summary>
545	/// <param name="jsonElement">The JSON element to convert.</param>
546	/// <returns>The string representation of the JSON element, or <c>null</c> if undefined.</returns>
547	private static string? GetJsonValue(JsonElement jsonElement)
548	{
549	return jsonElement.ValueKind != JsonValueKind.Undefined	×
550	? jsonElement.GetRawText()	×
551	: null;	×
552	}
553
554	/// <summary>
555	/// Converts a byte array to its hexadecimal string representation for use in SQL statements.
556	/// </summary>
557	/// <param name="bytes">The byte array to convert.</param>
558	/// <returns>The hexadecimal string representation.</returns>
559	private static string ToHex(byte[] bytes)
560	{
561	#if NET5_0_OR_GREATER
562	return Convert.ToHexString(bytes);	×
563	#else
564	var s = StringBuilderCache.Acquire();
565	s.Append("0x");
566
567	foreach (var b in bytes)
568	s.Append(b.ToString("x2").ToUpperInvariant());
569
570	return StringBuilderCache.ToString(s);
571	#endif
572	}
573	}

loresoft / FluentCommand / 29168809467

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous