示例#1
0
        /// <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());
        }
示例#2
0
        /// <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."),
            };