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 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);
}
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 TestEveryType()
{
// arrange
var instance = new TSqlGenerator <EachTypeWeHave>();
var expected = $@"CREATE TABLE EachTypeWeHave (
[PK] [int] NOT NULL,
[FK] [int] NULL,
[theGuid] [uniqueidentifier] NOT NULL,
[nullGuid] [uniqueidentifier] NULL,
[Name] [nvarchar](max) NOT NULL,
[Dt] [datetime] NOT NULL,
[NullableDt] [datetime] NULL,
[d] [real] NOT NULL,
[dNull] [real] NULL,
[longNumber] [bigint] NOT NULL,
[i64Number] [bigint] NOT NULL,
[er] [float] NOT NULL,
[dec] [decimal] NOT NULL,
[theBIT] [bit] NOT NULL,
[theBOOLEAN] [bit] NOT NULL,
[chewer] [tinyint] NOT NULL,
[bigChewer] [tinyint] NOT NULL,
[binary] [binary] NOT NULL,
[offset] [datetimeoffset] NOT NULL
)";
// act
var actual = instance.CreateTable(false);
// assert
Assert.Equal(expected, actual);
}
public void TempTableShouldMatch()
{
// arrange
var instance = new TSqlGenerator <StandardModel>();
var expected = $@"#StandardModels";
// act
var actual = instance.TableName(true);
// assert
Assert.Equal(expected, actual);
}
public void SetUp()
{
var sqlServerCommandFactory = new SqlServerCommandFactory(null);
_generator = new SqlCeTSqlGenerator(sqlServerCommandFactory);
_subQuery = new AliasedSqlSubQuery();
_subQuery.ExplicitlySpecifiedColumnsToSelect = new[] { "ColumnName1", "ColumnName2" };
_subQuery.TableName = "TableName";
_subQuery.PrimaryKeyColumnName = "IdColumnName";
}
public void SetUp()
{
var sqlServerCommandFactory = new SqlServerCommandFactory(null);
_generator = new SqlCeTSqlGenerator(sqlServerCommandFactory);
_subQuery = new AliasedSqlSubQuery();
_subQuery.ExplicitlySpecifiedColumnsToSelect = new[] { "ColumnName1", "ColumnName2" };
_subQuery.TableName = "TableName";
_subQuery.PrimaryKeyColumnName = "IdColumnName";
}
public void TestNullAndNotNullInts()
{
// arrange
var instance = new TSqlGenerator <TestNullAndNotNullInt>();
var expected = $@"CREATE TABLE TestNullAndNotNullInts (
[PK] [int] NOT NULL,
[FK] [int] NULL
)";
// act
var actual = instance.CreateTable(false);
// assert
Assert.Equal(expected, actual);
}
public void TestTempTableGenerationExplicit()
{
// arrange
var instance = new TSqlGenerator <StandardModel>();
var expected = $@"CREATE TABLE #StandardModels (
[PK] [int] NOT NULL,
[FK] [int] NULL,
[Name] [nvarchar](max) NOT NULL
)";
// act
var actual = instance.CreateTable(true);
// assert
Assert.Equal(expected, actual);
}
public void TestTempTableGeneration()
{
// arrange
var instance = new TSqlGenerator <StandardModel>();
var expected = $@"CREATE TABLE #StandardModels ("
+ Environment.NewLine
+ "[PK] [int] NOT NULL,"
+ Environment.NewLine
+ "[FK] [int] NULL,"
+ Environment.NewLine
+ "[Name] [nvarchar](max) NOT NULL"
+ Environment.NewLine
+ ")";
// act
var actual = instance.CreateTable();
// 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.
}
