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>("DBMergeSource", connection);
//Act
DbMerge <MySimpleRow> merge = new DbMerge <MySimpleRow>(MergeMode.Full, "DBMergeDestination", connection);
DbDestination <MySimpleRow> delta = new DbDestination <MySimpleRow>("DBMergeDelta", connection);
source.LinkTo(merge);
merge.LinkTo(delta);
source.Execute();
merge.Wait();
delta.Wait();
//Assert
Assert.True(merge.UseTruncateMethod == true);
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 DeltaLoadWithDeletion()
{
//Arrange
MemorySource source = new MemorySource();
source.DataAsList.Add(CreateDynamicRow(2, "Test2"));
source.DataAsList.Add(CreateDynamicRow(3, "Test3"));
source.DataAsList.Add(CreateDynamicRow(4, delete: true));
source.DataAsList.Add(CreateDynamicRow(10, delete: true));
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeDynamicDeltaDestination");
d2c.InsertTestDataSet3();
//Act
DbMerge dest = new DbMerge(SqlConnection, "DBMergeDynamicDeltaDestination")
{
DeltaMode = MergeMode.Delta
};
dest.MergeProperties.IdPropertyNames.Add("Col1");
dest.MergeProperties.ComparePropertyNames.Add("Col2");
dest.MergeProperties.DeletionProperties.Add("Delete", true);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d2c.AssertTestData();
Assert.Collection <ExpandoObject>(dest.DeltaTable,
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Update && r.Col1 == 2); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Insert && r.Col1 == 3); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Delete && r.Col1 == 4); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Delete && r.Col1 == 10); }
);
}
public void NoIdColumn(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.InsertTestDataSet3();
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("DBMergeSource", connection);
//Act
DbMerge <MySimpleRow> dest = new DbMerge <MySimpleRow>(MergeMode.Full, "DBMergeDestination", connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.True(dest.UseTruncateMethod == true);
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 == ChangeAction.Update).Count() == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Delete && row.Key == 10).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Insert).Count() == 3);
}
public void Test(IConnectionManager sourceConnection, IConnectionManager destConnection)
{
//Arrange
string QB = destConnection.QB;
string QE = destConnection.QE;
CreateSourceAndDestinationTables(sourceConnection, destConnection, QB, QE);
//Act
var nameSource = new DbSource <Name>(sourceConnection, "Name");
var personMerge = new DbMerge <People>(destConnection, "People");
var transform = new RowTransformation <Name, People>(d =>
{
return(new People()
{
FirstName = d.FIRST_NAME,
LastName = d.LAST_NAME,
Id = d.ID
});
});
nameSource.LinkTo(transform);
transform.LinkTo(personMerge);
nameSource.Execute();
personMerge.Wait();
//Assert
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 1 AND {QB}FirstName{QE} = 'Bugs' AND {QB}LastName{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 2 AND {QB}FirstName{QE} IS NULL AND {QB}LastName{QE} = 'Pig'"));
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 3 AND {QB}FirstName{QE} = 'Franky' AND {QB}LastName{QE} IS NULL"));
}
public void SimpleMergeWithDynamic()
{
//Arrange
MemorySource source = new MemorySource();
source.DataAsList.Add(CreateDynamicRow(1, "Test1"));
source.DataAsList.Add(CreateDynamicRow(2, "Test2"));
source.DataAsList.Add(CreateDynamicRow(3, "Test3"));
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeDynamicDestination");
d2c.InsertTestDataSet3();
//Act
DbMerge dest = new DbMerge(SqlConnection, "DBMergeDynamicDestination");
dest.MergeProperties.IdPropertyNames.Add("Col1");
dest.MergeProperties.ComparePropertyNames.Add("Col2");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(SqlConnection, "DBMergeDynamicDestination", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 7 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
d2c.AssertTestData();
Assert.Collection <ExpandoObject>(dest.DeltaTable,
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Exists && r.Col1 == 1); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Update && r.Col1 == 2); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Insert && r.Col1 == 3); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Delete && r.Col1 == 4); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.ChangeAction == ChangeAction.Delete && r.Col1 == 10); }
);
}
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 DisablingDeletion(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.DeltaMode = DeltaMode.NoDeletions;
//dest.DisableDeletion = true;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(5, RowCountTask.Count(connection, "DBMergeDestination"));
Assert.True(dest.DeltaTable.Count == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "I" && row.Key == 3).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "U" && row.Key == 2).Count() == 1);
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 MergeIntoAzure()
{
var envvar = Environment.GetEnvironmentVariable("ETLBoxAzure");
if (envvar != "true") return;
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(AzureSqlConnection, "[dest].[AzureMergeDestination]");
d2c.InsertTestDataSet3();
DbSource<MySimpleRow> source = new DbSource<MySimpleRow>(SqlConnection, "DBMergeSource");
//Act
DbMerge<MySimpleRow> dest = new DbMerge<MySimpleRow>(AzureSqlConnection, "[dest].[AzureMergeDestination]");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(AzureSqlConnection, "[dest].[AzureMergeDestination]", $"{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 == ChangeAction.Update).Count() == 2);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Delete && row.Col1 == 10).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Insert).Count() == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Exists && row.Col1 == 1).Count() == 1);
}
public void OnlyUpdates(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeOnlyUpdatesSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeOnlyUpdatesDestination");
d2c.InsertTestDataSet3();
DbSource <MyMergeRow> source = new DbSource <MyMergeRow>(connection, "DBMergeOnlyUpdatesSource");
//Act
DbMerge <MyMergeRow> dest = new DbMerge <MyMergeRow>(connection, "DBMergeOnlyUpdatesDestination");
dest.MergeMode = MergeMode.OnlyUpdates;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(4, RowCountTask.Count(connection, "DBMergeOnlyUpdatesDestination", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 10 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.True(dest.DeltaTable.Count == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Update).Count() == 2);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Exists && row.Key == 1).Count() == 1);
}
public void MergeIntoAzure()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(AzureSqlConnection, "[dest].[AzureMergeDestination]");
d2c.InsertTestDataSet3();
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("DBMergeSource", SqlConnection);
//Act
DbMerge <MySimpleRow> dest = new DbMerge <MySimpleRow>(MergeMode.Full, "[dest].[AzureMergeDestination]", AzureSqlConnection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(AzureSqlConnection, "[dest].[AzureMergeDestination]", $"{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 == ChangeAction.Update).Count() == 2);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Delete && row.Col1 == 10).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Insert).Count() == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.None && row.Col1 == 1).Count() == 1);
}
static void Main(string[] args)
{
//SqlCon
SqlConnection = new SqlConnectionManager(ConnectionString);
RecreateDatabase("demo", ConnectionString);
Prepare();
DbSource <MyMergeRow> source = new DbSource <MyMergeRow>(SqlConnection, "source");
DbMerge <MyMergeRow> merge = new DbMerge <MyMergeRow>(SqlConnection, "destination");
source.LinkTo(merge);
source.Execute();
merge.Wait();
}
public void DeltaLoadWithDeletion(IConnectionManager connection)
{
//Arrange
MemorySource <MyMergeRow> source = new MemorySource <MyMergeRow>();
source.DataAsList.Add(new MyMergeRow()
{
Key = 2, Value = "Test2"
});
source.DataAsList.Add(new MyMergeRow()
{
Key = 3, Value = "Test3"
});
source.DataAsList.Add(new MyMergeRow()
{
Key = 4, DeleteThisRow = true
});
source.DataAsList.Add(new MyMergeRow()
{
Key = 10, DeleteThisRow = true
});
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDeltaDestination");
d2c.InsertTestDataSet3();
//Act
DbMerge <MyMergeRow> dest = new DbMerge <MyMergeRow>(connection, "DBMergeDeltaDestination")
{
DeltaMode = MergeMode.Delta
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.True(dest.UseTruncateMethod == false);
d2c.AssertTestData();
Assert.True(dest.DeltaTable.Count == 4);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Update && row.Key == 2).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Insert && row.Key == 3).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Delete && row.Key == 4).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == ChangeAction.Delete && row.Key == 10).Count() == 1);
}
public void MergeFromEmptySource()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeEmptySource");
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeEmptyDestination");
d2c.InsertTestData();
DbSource <MyMergeRow> source = new DbSource <MyMergeRow>(SqlConnection, "DBMergeEmptySource");
//Act
DbMerge <MyMergeRow> dest = new DbMerge <MyMergeRow>(SqlConnection, "DBMergeEmptyDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d2c.AssertTestData();
}
public void NoUpdateWithGuid(int rowsInDest, int rowsInSource)
{
//Arrange
CreateDestinationTable("MergeDestination");
List <MyMergeRow> knownGuids = CreateTestData(rowsInSource);
TransferTestDataIntoDestination(knownGuids);
MemorySource <MyMergeRow> source = AddNewTestData(rowsInDest, knownGuids);
DbMerge <MyMergeRow> mergeDest = new DbMerge <MyMergeRow>(SqlConnection, "MergeDestination");
source.LinkTo(mergeDest);
//Act
var executionTime = BigDataHelper.LogExecutionTime("Execute merge", () =>
{
source.Execute();
mergeDest.Wait();
});
//Assert
Assert.Equal(rowsInDest + rowsInSource, RowCountTask.Count(SqlConnection, "MergeDestination") ?? 0);
Assert.True(executionTime <= TimeSpan.FromMilliseconds((rowsInDest + rowsInSource) * 2));
}
public void MergeWithCompositeKey(IConnectionManager connection)
{
//Arrange
ObjectNameDescriptor TNS = new ObjectNameDescriptor("DBMergeSource", connection.QB, connection.QE);
ObjectNameDescriptor TND = new ObjectNameDescriptor("DBMergeDestination", connection.QB, connection.QE);
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'"));
}