/// <summary>
/// Use sql merge to get all of the generated values back for all rows AND be able to map them to the correct objects
/// </summary>
/// <param name="mergeSerializer">When this method returns, contains a <see cref="Contracts.IMergeSerializer"/> that can be used to serialize collections into what's expected by the @serialized parameter</param>
/// <param name="mergeDefinition">Should inserts/updates/deletes be included in the merge statement? Should there be additional filtering?</param>
/// <param name="tableMap">Optionally specify a table map to be used to build out the query</param>
/// <remarks>
/// Note: The OPENJSON function is available only under compatibility level 130 or higher.
/// If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function.
/// </remarks>
/// <seealso cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql"/>
/// <returns>
/// Creates sql which expects a @serialized parameter whose value is a json or xml array string; if xml, the main and child nodes are named '_'.
/// The properties of the collection items are named _0 through _z, _10 through _zz, _100 through _zzz, and so-on.
/// A collection item property '_' is expected, which contains the index of the item within the array.
/// </returns>
public string Merge <T>(out Contracts.IMergeSerializer <T> mergeSerializer, MergeDefinition <T> mergeDefinition, ITableMap <T>?tableMap = null, bool useJson = false)
{
//--Build a table map and figure out which columns should be serialized
tableMap ??= GetTableMap <T>();
//--Build a merge serializer and the core of the merge statement
mergeSerializer = useJson ? new JsonMergeSerializer <T>(tableMap) : new XmlMergeSerializer <T>(tableMap);
var outputColumns = BuildOutputColumns(mergeDefinition, tableMap);
var merge = new System.Text.StringBuilder();
merge.AppendLine("declare @outputResult table(");
merge.AppendLine(string.Join($",{Environment.NewLine}", outputColumns.Select(column => $" {column.columnIdentifier} {column.sqlType}")));
merge.AppendLine(");");
merge.AppendLines(BuildCoreMergeLines(mergeSerializer, mergeDefinition, tableMap));
//--Build the components of the merge
merge.AppendLines(BuildInsertLines(mergeDefinition, tableMap));
merge.AppendLines(BuildUpdateLines(mergeDefinition, tableMap));
merge.AppendLines(BuildDeleteLines(mergeDefinition));
//--Build the merge's output statement so we can determine which records were inserted/updated/deleted
merge.AppendLine($"output {string.Join(", ", outputColumns.Select(oc => oc.value))} into @outputResult({string.Join(", ", outputColumns.Select(column => column.columnIdentifier))});");
merge.Append("select * from @outputResult;");
return(merge.ToString());
}
/// <summary>
/// Build the core lines for the merge statement
/// </summary>
public IEnumerable <string> BuildCoreMergeLines <T>(Contracts.IMergeSerializer <T> mergeSerializer, MergeDefinition <T> mergeDefinition, ITableMap <T> tableMap)
{
if (mergeSerializer is XmlMergeSerializer <T> )
{
//--Create an internal representation of the XML document
yield return("declare @preparedDoc int;");
yield return("exec sp_xml_preparedocument @preparedDoc output, @serialized;");
yield return("");
}
var sourceColumns = (mergeDefinition.IncludeInsert, mergeDefinition.IncludeUpdate, mergeDefinition.IncludeDelete) switch
{
(true, false, _) => tableMap.KeyColumns.Union(tableMap.InsertColumns).ToList(),
(true, true, _) => tableMap.KeyColumns.Union(tableMap.UpsertColumns).ToList(),
(false, true, _) => tableMap.KeyColumns.Union(tableMap.UpdateColumns).ToList(),
(false, false, true) => tableMap.KeyColumns,
_ => throw new ArgumentException("At least one of insert, update, or delete must be included in the merge."),
};