Example #1
0
        /// <summary>
        /// Generates a TSQL Merge Statement for the provided options.
        /// </summary>
        /// <param name="options">Upsert Options that specify how the merge should be built.</param>
        /// <returns></returns>
        public override string Merge(IUpsertOptions <TType> options)
        {
            // reference: https://stackoverflow.com/a/14806962/86860

            var builder = new StringBuilder();

            builder.Append($"MERGE INTO {options.TargetTableSchema}.{options.TargetTableName}");
            builder.AppendLine(" WITH (HOLDLOCK) AS target");

            builder.AppendLine($"USING {TableName(true)} AS source");
            builder.Append("ON ");
            foreach (var match in options.MatchColumns)
            {
                builder.Append($"target.{match} = source.{match}");
                if (match == options.MatchColumns.Last())
                {
                    // on the last one, end the line
                    builder.AppendLine("");
                }
                else
                {
                    // AND for the next parm
                    builder.Append(" AND ");
                }
            }
            builder.AppendLine("WHEN MATCHED THEN");
            // updates
            builder.Append("UPDATE SET ");
            foreach (var map in options.MapColumns)
            {
                builder.Append($"target.{map} = source.{map}");
                if (map == options.MapColumns.Last())
                {
                    // on the last one, end the line
                    builder.AppendLine("");
                }
                else
                {
                    // AND for the next parm
                    builder.Append(", ");
                }
            }
            builder.AppendLine("WHEN NOT MATCHED BY target THEN");
            //insert
            builder.AppendLine($"INSERT ({string.Join(", ", options.MapColumns)})");
            builder.AppendLine($"VALUES (source.{string.Join(", source.", options.MapColumns)})");

            //https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql Remarks
            builder.Append(";"); // final semicolon for MERGE statament
            return(builder.ToString());
        }
Example #2
0
        /// <summary>
        /// Upsert (insert if not matched, update if matched) a generic collection of objects.
        /// </summary>
        /// <typeparam name="TEntity">The type of entities to be updated.</typeparam>
        /// <param name="conn">The <see cref="IDbConnection"/> instance we're hanging off.</param>
        /// <param name="entities">An IEnumerable of objects to be upserted into the connection.</param>
        /// <param name="options">Bulk Upsert Options that describe how this upsert should work.</param>
        /// <returns></returns>
        public static bool Upsert <TEntity>(
            this IDbConnection conn,
            IEnumerable <TEntity> entities,
            IUpsertOptions <TEntity> options)
        {
            // setup
            var sqlGenerator  = new TSqlGenerator <TEntity>();
            var createScript  = sqlGenerator.CreateTable();
            var mergeScript   = sqlGenerator.Merge(options);
            var cleanUpScript = $"DROP TABLE {sqlGenerator.TableName(true)};";

            // execute
            using (var createTableCmd = conn.CreateCommand())
            {
                createTableCmd.CommandType = CommandType.Text;
                createTableCmd.CommandText = createScript;
                createTableCmd.ExecuteNonQuery();
            }

            // ==> bulk insert to temp table
            using (var copy = new SqlBulkCopy((SqlConnection)conn))
                using (var reader = FastMember.ObjectReader.Create(entities, sqlGenerator.GetProperties().ToArray()))
                {
                    // setup mapping
                    foreach (var prop in sqlGenerator.GetProperties())
                    {
                        // force one<=>one mapping
                        copy.ColumnMappings.Add(prop, prop);
                    }
                    // set destination table (insert into TEMP table)
                    copy.DestinationTableName = sqlGenerator.TableName(true);
                    // write data from reader to server
                    copy.WriteToServer(reader);
                    // run the bulk insert to temp table
                    copy.Close();
                }
            // <== end bulk insert to temp table

            // ==> merge upsert
            using (var trans = conn.BeginTransaction())
                using (var mergeTempAndRealCmd = conn.CreateCommand())
                {
                    // ==> merge temp table to target table
                    mergeTempAndRealCmd.Transaction = trans;
                    mergeTempAndRealCmd.CommandType = CommandType.Text;
                    mergeTempAndRealCmd.CommandText = mergeScript;
                    mergeTempAndRealCmd.ExecuteNonQuery();
                    // <== end merge temp table to target table
                    trans.Commit();
                }
            // <== end merge upsert

            // cleanup
            using (var dropTableCmd = conn.CreateCommand())
            {
                dropTableCmd.CommandText = cleanUpScript;
                dropTableCmd.ExecuteNonQuery();
            }
            // finalize
            return(true); // maybe update this to indicate kinds of failure instead of simple true/false.
        }
 /// <summary>
 /// Performs the generation of the merge statement based on the given options.
 /// </summary>
 /// <param name="options"></param>
 /// <returns></returns>
 public abstract string Merge(IUpsertOptions <TType> options);