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