public void MultipleMatchColumnsShouldMatch()
{
// arrange
var instance = new TSqlGenerator <StandardModel>();
var expected = $@"MERGE INTO dbo.StandardModels WITH (HOLDLOCK) AS target"
+ Environment.NewLine
+ "USING #StandardModels AS source"
+ Environment.NewLine
+ "ON target.PK = source.PK AND target.FK = source.FK"
+ Environment.NewLine
+ "WHEN MATCHED THEN"
+ Environment.NewLine
+ "UPDATE SET target.PK = source.PK, target.FK = source.FK, target.Name = source.Name"
+ Environment.NewLine
+ "WHEN NOT MATCHED BY target THEN"
+ Environment.NewLine
+ "INSERT (PK, FK, Name)"
+ Environment.NewLine
+ "VALUES (source.PK, source.FK, source.Name)"
+ Environment.NewLine
+ ";"; // final new line
// act
var actual = instance.Merge(new UpsertOptions <StandardModel>()
.SetTargetTable("StandardModels")
.AddMatchColumn(o => o.PK)
.AddMatchColumn(o => o.FK)
.AddMapColumn(p => p.PK)
.AddMapColumn(p => p.FK)
.AddMapColumn(p => p.Name)
);
// assert
Assert.Equal(expected, actual);
}
public void MergeWithAllMapped()
{
// arrange
var instance = new TSqlGenerator <TestModel1>();
var expected = $@"MERGE INTO dbo.TestModel1 WITH (HOLDLOCK) AS target"
+ Environment.NewLine
+ "USING #TestModel1 AS source"
+ Environment.NewLine
+ "ON target.TmId = source.TmId"
+ Environment.NewLine
+ "WHEN MATCHED THEN"
+ Environment.NewLine
+ "UPDATE SET target.TmId = source.TmId, target.TfkId = source.TfkId, target.ModifiedDate = source.ModifiedDate, target.PSI = source.PSI, target.ModifiedBy = source.ModifiedBy, target.CreatedBy = source.CreatedBy, target.Current = source.Current, target.CreatedDate = source.CreatedDate"
+ Environment.NewLine
+ "WHEN NOT MATCHED BY target THEN"
+ Environment.NewLine
+ "INSERT (TmId, TfkId, ModifiedDate, PSI, ModifiedBy, CreatedBy, Current, CreatedDate)"
+ Environment.NewLine
+ "VALUES (source.TmId, source.TfkId, source.ModifiedDate, source.PSI, source.ModifiedBy, source.CreatedBy, source.Current, source.CreatedDate)"
+ Environment.NewLine
+ ";"; // final new line
// act
var actual = instance.Merge(new UpsertOptions <TestModel1>()
.SetTargetTable("TestModel1")
.AddMatchColumn(o => o.TmId)
.MapAllColumns()
);
// assert
Assert.Equal(expected, actual);
}
public void GeneratedMergeShouldShowMappedColumns()
{
// arrange
var instance = new TSqlGenerator <StandardModel>();
var expected = $@"MERGE INTO dbo.StandardModels WITH (HOLDLOCK) AS target
USING #StandardModels AS source
ON target.PK = source.PK
WHEN MATCHED THEN
UPDATE SET target.PK = source.PK, target.FK = source.FK, target.Name = source.Name
WHEN NOT MATCHED BY target THEN
INSERT (PK, FK, Name)
VALUES (source.PK, source.FK, source.Name)
;"; // final new line
// act
var actual = instance.Merge(new UpsertOptions <StandardModel>()
.SetTargetTable("StandardModels")
.AddMatchColumn(o => o.PK)
.AddMapColumn(p => p.PK)
.AddMapColumn(p => p.FK)
.AddMapColumn(p => p.Name)
);
// assert
Assert.Equal(expected, actual);
}
/// <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.
}
