public void WithDeltaDestination(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.InsertTestDataSet3();
TwoColumnsDeltaTableFixture delta2Columns = new TwoColumnsDeltaTableFixture(connection, "DBMergeDelta");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(connection, "DBMergeSource");
//Act
DBMerge <MySimpleRow> merge = new DBMerge <MySimpleRow>(connection, "DBMergeDestination");
DBDestination <MySimpleRow> delta = new DBDestination <MySimpleRow>(connection, "DBMergeDelta");
source.LinkTo(merge);
merge.LinkTo(delta);
source.Execute();
merge.Wait();
delta.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(connection, "DBMergeDestination", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 7 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.Equal(7, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 10 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'D' AND {d2c.QB}Col1{d2c.QE} = 10"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'U' AND {d2c.QB}Col1{d2c.QE} IN (1,2,4)"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'I' AND {d2c.QB}Col1{d2c.QE} IN (3,5,6)"));
}
public void SimpleMerge(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.InsertTestDataSet3();
DBSource <MyMergeRow> source = new DBSource <MyMergeRow>(connection, "DBMergeSource");
//Act
DBMerge <MyMergeRow> dest = new DBMerge <MyMergeRow>(connection, "DBMergeDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(connection, "DBMergeDestination", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 7 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.True(dest.DeltaTable.Count == 7);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "U").Count() == 2);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Key == 10).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "I").Count() == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "E" && row.Key == 1).Count() == 1);
}
public void EnforcingTruncate(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.InsertTestDataSet3();
DBSource <MyMergeRow> source = new DBSource <MyMergeRow>(connection, "DBMergeSource");
//Act
DBMerge <MyMergeRow> dest = new DBMerge <MyMergeRow>(connection, "DBMergeDestination");
dest.UseTruncateMethod = true;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDestination"));
Assert.True(dest.DeltaTable.Count == 5);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "E" && row.Key == 1).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "U" && row.Key == 2).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "I" && row.Key == 3).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Key == 4).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Key == 10).Count() == 1);
}
public void NoMergeIdColumn(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "DBMergeSource");
source2Columns.InsertTestData();
source2Columns.InsertTestDataSet2();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DBMergeDestination");
dest2Columns.InsertTestDataSet3();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(connection, "DBMergeSource");
//Act
DBMerge <MySimpleRow> dest = new DBMerge <MySimpleRow>(connection, "DBMergeDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(connection, "DBMergeDestination", "Col1 BETWEEN 1 AND 7 AND Col2 LIKE 'Test%'"));
Assert.True(dest.DeltaTable.Count == 7);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "U").Count() == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Key == 10).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "I").Count() == 3);
}
public void DBMergeNoMergeIdColumn()
{
CreateSourceTable();
CreateDestinationTable();
DBSource <MySimpleRowNoMergeIdColumn> source = new DBSource <MySimpleRowNoMergeIdColumn>("test.Source");
DBMerge <MySimpleRowNoMergeIdColumn> dest = new DBMerge <MySimpleRowNoMergeIdColumn>("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
AssertDestinationTable();
}
public void DBMergeWithDeltaDestination()
{
CreateSourceTable();
CreateDestinationTable();
SqlTask.ExecuteNonQuery("Create delta table", @"CREATE TABLE test.Delta
(ColKey int not null, ColValue nvarchar(30) null,
ChangeDate datetime null, ChangeAction char(1) not null)");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>("test.Source");
DBMerge <MySimpleRow> merge = new DBMerge <MySimpleRow>("test.Destination");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>("test.Delta");
source.LinkTo(merge);
merge.LinkTo(dest);
source.Execute();
merge.Wait();
dest.Wait();
//dest.Wait();
}
public void MergeWithCompositeKey(IConnectionManager connection)
{
//Arrange
TableNameDescriptor TNS = new TableNameDescriptor("DBMergeSource", connection);
TableNameDescriptor TND = new TableNameDescriptor("DBMergeDestination", connection);
ReCreateTable(connection, TNS);
ReCreateTable(connection, TND);
InsertSourceData(connection, TNS);
InsertDestinationData(connection, TND);
//Act
DBSource <MyMergeRow> source = new DBSource <MyMergeRow>(connection, "DBMergeSource");
DBMerge <MyMergeRow> dest = new DBMerge <MyMergeRow>(connection, "DBMergeDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDestination"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'E' and {TND.QB}ColValue2{TND.QE} = 'Test3'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'U' and {TND.QB}ColValue2{TND.QE} = 'Test2'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'I' and {TND.QB}ColValue2{TND.QE} = 'Test1'"));
}
