public void TestNotMatchedBySourceMatched_ThenUpdate()
{
var data = new[]
{
new UserData
{
UserId = 1,
FirstName = "First",
LastName = "Last",
EMail = "*****@*****.**",
RegDate = new DateTime(2020, 01, 02)
}
};
var merge = SqQueryBuilder.MergeDataInto(Tables.User("T"), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s.Set(s.Target.FirstName, s.Source.FirstName).Set(s.Target.LastName, s.Source.LastName))
.WhenNotMatchedBySourceThenUpdate(t => t.UserId == 7)
.Set(s => s.Set(s.Target.Version, -1))
.Done();
var sql = merge?.ToSql();
var expected = "MERGE [dbo].[user] [T] " +
"USING (VALUES (1,'First','Last'))[A0]([UserId],[FirstName],[LastName]) " +
"ON [T].[UserId]=[A0].[UserId] WHEN NOT MATCHED BY SOURCE AND [T].[UserId]=7 THEN UPDATE SET [T].[Version]=-1;";
Assert.AreEqual(expected, sql);
}
public void TestMatched_ThenDelete()
{
var data = new[]
{
new UserData
{
UserId = 1,
FirstName = "First",
LastName = "Last",
EMail = "*****@*****.**",
RegDate = new DateTime(2020, 01, 02)
}
};
var merge = SqQueryBuilder.MergeDataInto(Tables.User("T"), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s.Set(s.Target.FirstName, s.Source.FirstName).Set(s.Target.LastName, s.Source.LastName))
.WhenMatchedThenDelete()
.Done();
var sql = merge?.ToSql();
var expected = "MERGE [dbo].[user] [T] " +
"USING (VALUES (1,'First','Last'))[A0]([UserId],[FirstName],[LastName]) " +
"ON [T].[UserId]=[A0].[UserId] WHEN MATCHED THEN DELETE;";
Assert.AreEqual(expected, sql);
}
public void TestNotMatched_ThenExclude()
{
var data = new[]
{
new UserData
{
UserId = 1,
FirstName = "First",
LastName = "Last",
EMail = "*****@*****.**",
RegDate = new DateTime(2020, 01, 02)
}
};
var merge = SqQueryBuilder.MergeDataInto(Tables.User("T"), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s.Set(s.Target.FirstName, s.Source.FirstName).Set(s.Target.LastName, s.Source.LastName))
.WhenNotMatchedByTargetThenInsert((t, s) => t.UserId.WithSource(s) != 8)
.Exclude(t => t.LastName)
.Done();
var sql = merge?.ToSql();
var expected = "MERGE [dbo].[user] [T] " +
"USING (VALUES (1,'First','Last'))[A0]([UserId],[FirstName],[LastName]) " +
"ON [T].[UserId]=[A0].[UserId] WHEN NOT MATCHED AND [A0].[UserId]!=8 THEN INSERT([UserId],[FirstName]) VALUES([A0].[UserId],[A0].[FirstName]);";
Assert.AreEqual(expected, sql);
}
public void KeysOnly_WhenMatchedThenUpdate_Error()
{
var data = new [] { new { Id1 = 1, Id2 = 2 }, new { Id1 = 3, Id2 = 4 } };
Assert.Throws <SqExpressException>(() =>
SqQueryBuilder.MergeDataInto(Tables.CustomerOrder(), data)
.MapDataKeys(s => s.Set(s.Target.CustomerId, s.Source.Id1).Set(s.Target.OrderId, s.Source.Id2))
.WhenMatchedThenUpdate()
.WhenNotMatchedByTargetThenInsert()
.WhenNotMatchedBySourceThenDelete()
.Done());
}
public void EmptyData_Null()
{
var data = new UserData[0];
Assert.Throws <SqExpressException>(() =>
{
SqQueryBuilder.MergeDataInto(Tables.User(), data)
.MapDataKeys(s => s.Set(s.Target.FirstName, s.Source.FirstName))
.MapData(s =>
s.Set(s.Target.FirstName, s.Source.FirstName).Set(s.Target.LastName, s.Source.LastName))
.WhenMatchedThenDelete()
.Done();
});
}
public void KeysOnly_NoWhenMatch()
{
var data = new [] { new { Id1 = 1, Id2 = 2 }, new { Id1 = 3, Id2 = 4 } };
var merge = SqQueryBuilder.MergeDataInto(Tables.CustomerOrder(), data)
.MapDataKeys(s => s.Set(s.Target.CustomerId, s.Source.Id1).Set(s.Target.OrderId, s.Source.Id2))
.WhenNotMatchedByTargetThenInsert()
.WhenNotMatchedBySourceThenDelete()
.Done();
var sql = merge.ToSql();
const string expected = "MERGE [dbo].[CustomerOrder] [A0] USING (VALUES (1,2),(3,4))[A1]([CustomerId],[OrderId]) " +
"ON [A0].[CustomerId]=[A1].[CustomerId] AND [A0].[OrderId]=[A1].[OrderId] " +
"WHEN NOT MATCHED THEN INSERT([CustomerId],[OrderId]) VALUES([A1].[CustomerId],[A1].[OrderId]) " +
"WHEN NOT MATCHED BY SOURCE THEN DELETE;";
Assert.AreEqual(expected, sql);
}
public void TestUpdateDefault()
{
var data = new[]
{
new UserData
{
UserId = 1,
FirstName = "First",
LastName = "Last",
EMail = "*****@*****.**",
RegDate = new DateTime(2020, 01, 02)
}
};
var merge = SqQueryBuilder.MergeDataInto(Tables.User("T"), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName))
.WhenMatchedThenUpdate()
.AlsoSet(s => s
.Set(s.Target.Version, -1)
.SetDefault(s.Target.RegDate))
.WhenNotMatchedByTargetThenInsert()
.ExcludeKeys()
.AlsoInsert(s => s.SetDefault(s.Target.RegDate))
.Done();
var sql = merge.ToSql();
var expected = "MERGE [dbo].[user] [T] USING (VALUES (1,'First','Last'))[A0]([UserId],[FirstName],[LastName]) " +
"ON [T].[UserId]=[A0].[UserId] " +
"WHEN MATCHED THEN UPDATE SET [T].[FirstName]=[A0].[FirstName],[T].[LastName]=[A0].[LastName],[T].[Version]=-1,[T].[RegDate]=DEFAULT " +
"WHEN NOT MATCHED THEN INSERT([FirstName],[LastName],[RegDate]) VALUES([A0].[FirstName],[A0].[LastName],DEFAULT);";
Assert.AreEqual(expected, sql);
}
public void FullTest()
{
const int usersCount = 3;
var data = new List <UserData>(usersCount);
for (int i = 0; i < usersCount; i++)
{
data.Add(new UserData
{
UserId = i % 2 == 0 ? 0 : i,
FirstName = "First" + i,
LastName = "Last" + i,
EMail = $"user{i}@company.com",
RegDate = new DateTime(2020, 01, 02)
});
}
DateTime utcNow = new DateTime(2020, 10, 03, 10, 17, 12, 131);
var recordIndex = CustomColumnFactory.Int32("Index");
var inserted = CustomColumnFactory.Int32("InsertedUserId");
var deleted = CustomColumnFactory.Int32("DeletedUserId");
var action = CustomColumnFactory.String("Action");
var mergeOutput = SqQueryBuilder
.MergeDataInto(Tables.User(), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName)
.Set(s.Target.Email, s.Source.EMail)
.Set(s.Target.RegDate, s.Source.RegDate))
.MapExtraData(s => s.Set(recordIndex, s.Index))
.AndOn((t, s) => t.UserId.WithSource(s) != 0)
.WhenMatchedThenUpdate()
.AlsoSet(s =>
s.Set(s.Target.Version, s.Target.Version + 1)
.Set(s.Target.Modified, utcNow))
.WhenNotMatchedByTargetThenInsert()
.ExcludeKeys()
.Exclude(t => new[] { t.Email.ColumnName, t.LastName.ColumnName })
.AlsoInsert(s => s
.Set(s.Target.LastName, "Fake")
.Set(s.Target.Created, utcNow)
.Set(s.Target.Modified, utcNow)
.Set(s.Target.Version, 1))
.WhenNotMatchedBySourceThenDelete()
.Output((t, s, m) => m
.Inserted(t.UserId.As(inserted))
.Inserted(t.UserId.As(deleted))
.Column(recordIndex.WithSource(s))
.Action(action))
.Done();
var actual = mergeOutput?.ToSql();
var expected = "MERGE [dbo].[user] [A0] USING (" +
"VALUES (0,'First0','Last0','*****@*****.**','2020-01-02',0)," +
"(1,'First1','Last1','*****@*****.**','2020-01-02',1)," +
"(0,'First2','Last2','*****@*****.**','2020-01-02',2)" +
")[A1]([UserId],[FirstName],[LastName],[Email],[RegDate],[Index]) " +
"ON [A0].[UserId]=[A1].[UserId] AND [A1].[UserId]!=0 " +
"WHEN MATCHED THEN UPDATE SET " +
"[A0].[FirstName]=[A1].[FirstName]," +
"[A0].[LastName]=[A1].[LastName]," +
"[A0].[Email]=[A1].[Email]," +
"[A0].[RegDate]=[A1].[RegDate]," +
"[A0].[Version]=[A0].[Version]+1," +
"[A0].[Modified]='2020-10-03T10:17:12.131' " +
"WHEN NOT MATCHED THEN INSERT" +
"([FirstName],[RegDate],[LastName],[Created],[Modified],[Version]) " +
"VALUES([A1].[FirstName],[A1].[RegDate],'Fake','2020-10-03T10:17:12.131','2020-10-03T10:17:12.131',1) " +
"WHEN NOT MATCHED BY SOURCE THEN DELETE " +
"OUTPUT INSERTED.[UserId] [InsertedUserId],INSERTED.[UserId] [DeletedUserId],[A1].[Index],$ACTION [Action];";
Assert.AreEqual(expected, actual);
}
public async Task Exec(IScenarioContext context)
{
var tt = new TestMergeTmpTable();
await context.Database.Statement(tt.Script.Create());
var testData = new List <TestMergeData>
{
new TestMergeData(1, 10),
new TestMergeData(2, 11)
};
//Insert
context.WriteLine("Inserting using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate()
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedByTargetThenInsert()
.Done()
.Exec(context.Database);
var dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,10,0;2,11,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Updating using MERGE..");
testData.Add(new TestMergeData(3, 12));
testData[0] = testData[0].WithValue(100);
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate((s, t) => tt.Value.WithSource(t) != s.Value.WithSource(s.Alias))
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedByTargetThenInsert()
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,100,1;2,11,0;3,12,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Updating (BY SOURCE) using MERGE..");
testData = new List <TestMergeData>
{
new TestMergeData(1, 17),
};
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate((s, t) => tt.Value.WithSource(t) != s.Value.WithSource(s.Alias))
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedBySourceThenUpdate(t => t.Value == 12).Set(s => s.Set(s.Target.Version, s.Target.Version + 10))
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,17,2;2,11,0;3,12,10")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Deleting (BY SOURCE) using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate()
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedBySourceThenDelete(t => t.Value == 12)
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,17,3;2,11,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Deleting ON MATCH using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenDelete()
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "2,11,0")
{
throw new Exception("Incorrect data");
}
await context.Database.Statement(tt.Script.Drop());
}
